VPCLASS cpu on sun/oracle M4000 machine

Hello, I have an IDS 11.50.FC6 instance on an M4000 oracle server with 4 CPUs with 4 cores * 2 threads each. The OS (Solaris 10) 'sees' a total of 32 CPUs. The thing is, I'm trying to tune the cpu VPCLASS onconfig parameter so that this instance is able to use more CPU power, but I can't figure out the correct configuration. My question: If I set the VPCLASS config parameter like this: VPCLASS cpu,num=4,aff=(0-7,8-15,16-23,24-31),noage why starts the engine reporting this upon reboot? 12:57:11 Affinitied VP 1 to phys proc 0 12:57:11 Affinitied VP 3 to phys proc 1 12:57:11 Affinitied VP 4 to phys proc 2 12:57:11 Affinitied VP 5 to phys proc 3 If I understand this right, this clearly means that all informix VP are pinned to the same physical processor, the 1st one, which has a total of 8 virtual processors (0-7). I check this my running: $ prsinfo -vp:The physical processor has 8 virtual processors (0-7) SPARC64-VII (portid 1024 impl 0x7 ver 0xa1 clock 2530 MHz) The physical processor has 8 virtual processors (8-15) SPARC64-VII (portid 1032 impl 0x7 ver 0xa1 clock 2530 MHz) The physical processor has 8 virtual processors (16-23) SPARC64-VII (portid 1040 impl 0x7 ver 0xa1 clock 2530 MHz) The physical processor has 8 virtual processors (24-31) SPARC64-VII (portid 1048 impl 0x7 ver 0xa1 clock 2530 MHz) But, according to the config parameter above, shouldn't the 4 informix cpu VP be pinning to vp=0, vp=8, vp=16 and vp=24? Is this a bug (or a feature)? Note: I've also tried the shorter one: VPCLASS cpu,num=4,aff=(0-32/n) where I tried n=8, n=4, etc, but I get always the same result: cpu VP always get pinned to OS virtual processors 0,1,2,3,.... always in that order. Just if somebody is interested, the background of all that is that for certain unload/load tasks the CPU power I'm getting is way to low. In fact, when I issue a prstat -a, never ever the total power reported for user 'informix' is above 1,4%, it doesn't matter how many cpu VPs I add, which is ridiculously low. Can anybody give me a hint on this? Thanks.

Hi Gerardo! I have the same situation on the production environment of the company that I work, but the machine is small compared the server you have. In the our environment, Informix is running on RHEL 5 x86_64 bits. In my case: 2 Physical Processors 4 cores per physical CPU 2 threads per core 16 GB Memory (Limited by Informix Growth Edition license) --------------------- Total: 16 threads. I've set the following values to the VPCLASS parameter: VPCLASS cpu,num=16,noage The performance that I've got is awesome, the environment has 6200 established connections, and the server workload is up to 25% max at peak times. The average DB response time is 350 miliseconds, using a EMC CX4-120 Storage System. Regards, Alberto. -----Mensagem original----- De: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Em nome de GERARDO PADIERNA Enviada em: terça-feira, 23 de agosto de 2011 08:51 Para: ids@iiug.org Assunto: VPCLASS cpu on sun/oracle M4000 machine [24663] Hello, I have an IDS 11.50.FC6 instance on an M4000 oracle server with 4 CPUs with 4 cores * 2 threads each. The OS (Solaris 10) 'sees' a total of 32 CPUs. The thing is, I'm trying to tune the cpu VPCLASS onconfig parameter so that this instance is able to use more CPU power, but I can't figure out the correct configuration. My question: If I set the VPCLASS config parameter like this: VPCLASS cpu,num=4,aff=(0-7,8-15,16-23,24-31),noage why starts the engine reporting this upon reboot? 12:57:11 Affinitied VP 1 to phys proc 0 12:57:11 Affinitied VP 3 to phys proc 1 12:57:11 Affinitied VP 4 to phys proc 2 12:57:11 Affinitied VP 5 to phys proc 3 If I understand this right, this clearly means that all informix VP are pinned to the same physical processor, the 1st one, which has a total of 8 virtual processors (0-7). I check this my running: $ prsinfo -vp:The physical processor has 8 virtual processors (0-7) SPARC64-VII (portid 1024 impl 0x7 ver 0xa1 clock 2530 MHz) The physical processor has 8 virtual processors (8-15) SPARC64-VII (portid 1032 impl 0x7 ver 0xa1 clock 2530 MHz) The physical processor has 8 virtual processors (16-23) SPARC64-VII (portid 1040 impl 0x7 ver 0xa1 clock 2530 MHz) The physical processor has 8 virtual processors (24-31) SPARC64-VII (portid 1048 impl 0x7 ver 0xa1 clock 2530 MHz) But, according to the config parameter above, shouldn't the 4 informix cpu VP be pinning to vp=0, vp=8, vp=16 and vp=24? Is this a bug (or a feature)? Note: I've also tried the shorter one: VPCLASS cpu,num=4,aff=(0-32/n) where I tried n=8, n=4, etc, but I get always the same result: cpu VP always get pinned to OS virtual processors 0,1,2,3,.... always in that order. Just if somebody is interested, the background of all that is that for certain unload/load tasks the CPU power I'm getting is way to low. In fact, when I issue a prstat -a, never ever the total power reported for user 'informix' is above 1,4%, it doesn't matter how many cpu VPs I add, which is ridiculously low. Can anybody give me a hint on this? Thanks. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Hi Alberto, thanks for your response. I've already tried a similar VPCLASS setting, but it isn't any better. In fact, I have the same application+database, different client but similar data volume, on a complete different architecture: Intel Xeon X5660 (6 cores, 12 threads), running on RHEL 6.1, 14GB RAM with IDS 11.70.FC2. with VPCLASS set as: VPCLASS cpu,num=6,noage (although, according to onstat -g glo statistics, only 4 of them are really used). In this box, a comparable unload task is about 6 times faster than on the M4000. I'd like to figure out how to improve unload times on the M4000 machine. Greetings!

Gerardo, It probably will depend on machine architecture, it has changed since Enterprise series, you can see in prtdiag on my environment, Informix would take one cpuvp on each physical cpu. This is the example on my solaris machine e6900. It means Sun has changed the architecture since that. ========================= CPUs =============================================== CPU Run E$ CPU CPU FRU Name ID MHz MB Impl. Mask ---------- ------- ---- ---- ------- ---- /N0/SB0/P0 0,512 1350 16.0 US-IV 3.1 /N0/SB0/P1 1,513 1350 16.0 US-IV 3.1 /N0/SB0/P2 2,514 1350 16.0 US-IV 3.1 /N0/SB0/P3 3,515 1350 16.0 US-IV 3.1 /N0/SB2/P0 8,520 1350 16.0 US-IV 3.1 /N0/SB2/P1 9,521 1350 16.0 US-IV 3.1 /N0/SB2/P2 10,522 1350 16.0 US-IV 3.1 /N0/SB2/P3 11,523 1350 16.0 US-IV 3.1 I Don't understand your concern, despite that each core works separately, depends on the machine architecture, it will reduce message exchange on shared memory and reduce the %sys utilization, improving the performance. Why do you have a so big machine to use so little? Celso Cabral Coimbra Administrador de Banco de Dados ClearTech Ltda "Trust at the heart of Communications" Tel. (11) 3576-4509 -----Mensagem original----- De: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Em nome de GERARDO PADIERNA Enviada em: terça-feira, 23 de agosto de 2011 08:51 Para: ids@iiug.org Assunto: VPCLASS cpu on sun/oracle M4000 machine [24663] Hello, I have an IDS 11.50.FC6 instance on an M4000 oracle server with 4 CPUs with 4 cores * 2 threads each. The OS (Solaris 10) 'sees' a total of 32 CPUs. The thing is, I'm trying to tune the cpu VPCLASS onconfig parameter so that this instance is able to use more CPU power, but I can't figure out the correct configuration. My question: If I set the VPCLASS config parameter like this: VPCLASS cpu,num=4,aff=(0-7,8-15,16-23,24-31),noage why starts the engine reporting this upon reboot? 12:57:11 Affinitied VP 1 to phys proc 0 12:57:11 Affinitied VP 3 to phys proc 1 12:57:11 Affinitied VP 4 to phys proc 2 12:57:11 Affinitied VP 5 to phys proc 3 If I understand this right, this clearly means that all informix VP are pinned to the same physical processor, the 1st one, which has a total of 8 virtual processors (0-7). I check this my running: $ prsinfo -vp:The physical processor has 8 virtual processors (0-7) SPARC64-VII (portid 1024 impl 0x7 ver 0xa1 clock 2530 MHz) The physical processor has 8 virtual processors (8-15) SPARC64-VII (portid 1032 impl 0x7 ver 0xa1 clock 2530 MHz) The physical processor has 8 virtual processors (16-23) SPARC64-VII (portid 1040 impl 0x7 ver 0xa1 clock 2530 MHz) The physical processor has 8 virtual processors (24-31) SPARC64-VII (portid 1048 impl 0x7 ver 0xa1 clock 2530 MHz) But, according to the config parameter above, shouldn't the 4 informix cpu VP be pinning to vp=0, vp=8, vp=16 and vp=24? Is this a bug (or a feature)? Note: I've also tried the shorter one: VPCLASS cpu,num=4,aff=(0-32/n) where I tried n=8, n=4, etc, but I get always the same result: cpu VP always get pinned to OS virtual processors 0,1,2,3,.... always in that order. Just if somebody is interested, the background of all that is that for certain unload/load tasks the CPU power I'm getting is way to low. In fact, when I issue a prstat -a, never ever the total power reported for user 'informix' is above 1,4%, it doesn't matter how many cpu VPs I add, which is ridiculously low. Can anybody give me a hint on this? Thanks. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Use: VPCLASS cpu,num=16,aff=(1,3,5,7,9,11,13,15,17,19,21,23,25,27,29,31),noage or even VPCLASS cpu,num=32,aff=(1,3,5,7,9,11,13,15,17,19,21,23,25,27,29,31),noage Do NOT count the hardware threads as processors, so only use one thread per core in the aff setting, but you can run up to four cpu vps on each core on processors this fast. As far as using more CPU cycles, your server may be IO bound. Do you need more buffers? What does your BTR look like? Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Aug 23, 2011 at 7:50 AM, GERARDO PADIERNA <g.padierna@gmail.com>wrote: > Hello, > I have an IDS 11.50.FC6 instance on an M4000 oracle server with 4 CPUs with > 4 > cores * 2 threads each. The OS (Solaris 10) 'sees' a total of 32 CPUs. > The thing is, I'm trying to tune the cpu VPCLASS onconfig parameter so that > this instance is able to use more CPU power, but I can't figure out the > correct configuration. > My question: If I set the VPCLASS config parameter like this: > VPCLASS cpu,num=4,aff=(0-7,8-15,16-23,24-31),noage > why starts the engine reporting this upon reboot? > 12:57:11 Affinitied VP 1 to phys proc 0 > 12:57:11 Affinitied VP 3 to phys proc 1 > 12:57:11 Affinitied VP 4 to phys proc 2 > 12:57:11 Affinitied VP 5 to phys proc 3 > If I understand this right, this clearly means that all informix VP are > pinned > to the same physical processor, the 1st one, which has a total of 8 virtual > processors (0-7). I check this my running: > $ prsinfo -vp:> The physical processor has 8 virtual processors (0-7) > SPARC64-VII (portid 1024 impl 0x7 ver 0xa1 clock 2530 MHz) > The physical processor has 8 virtual processors (8-15) > SPARC64-VII (portid 1032 impl 0x7 ver 0xa1 clock 2530 MHz) > The physical processor has 8 virtual processors (16-23) > SPARC64-VII (portid 1040 impl 0x7 ver 0xa1 clock 2530 MHz) > The physical processor has 8 virtual processors (24-31) > SPARC64-VII (portid 1048 impl 0x7 ver 0xa1 clock 2530 MHz) > > But, according to the config parameter above, shouldn't the 4 informix cpu > VP > be pinning to vp=0, vp=8, vp=16 and vp=24? Is this a bug (or a feature)? > > Note: I've also tried the shorter one: > VPCLASS cpu,num=4,aff=(0-32/n) > where I tried n=8, n=4, etc, but I get always the same result: cpu VP > always > get pinned to OS virtual processors 0,1,2,3,.... always in that order. > > Just if somebody is interested, the background of all that is that for > certain > unload/load tasks the CPU power I'm getting is way to low. In fact, when I > issue a prstat -a, never ever the total power reported for user 'informix' > is > above 1,4%, it doesn't matter how many cpu VPs I add, which is ridiculously > low. Can anybody give me a hint on this? Thanks. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --bcaec51b9a65f8037504ab2baf45

GERARDO PADIERNA Wrote: -------------------------------------------------------------------------------- Hello, ...snip... My question: If I set the VPCLASS config parameter like this: VPCLASS cpu,num=4,aff=(0-7,8-15,16-23,24-31),noage why starts the engine reporting this upon reboot? 12:57:11 Affinitied VP 1 to phys proc 0 12:57:11 Affinitied VP 3 to phys proc 1 12:57:11 Affinitied VP 4 to phys proc 2 12:57:11 Affinitied VP 5 to phys proc 3 ...snip... -------------------------------------------------------------------------------- Response: You've configured the engine to start 4 cpuvp's and you've listed 32 cpus in the affinity list. It is connecting to the first 4 from the affinity list. aff=(0-7,8-15,16-23,24-31) tells it to affinity to processors 0 through 7 then processors 8 through 15 etc. If you want to start 4 cpuvp's affinitied to processors 0, 8, 16, and 24 then use this line... VPCLASS cpu,num=4,aff=(0,8,16,24),noage Although, you probably want to avoid affinity to proc 0. HTH, Dave Griffen

Hi Dave, thanks, you're right, I was messing up with the VPCLASS syntax; I guess I was so stupid a to think, I don't know why, that in the (a-b,c-d,e-f,...) somehow the ',' would have precedence over the x-z interval specification. This is one of those pretty obvious things that for some reason you aren't able to see... I even convinced a colleague of mine of my weird syntax interpretation ;). Nevertheless, the better informix VP - CPU-core distribution doesn't speed up my unload, nor does it bring it a higher CPU utilization (as viewed with prstat -a): still not higher than 1.4% global 'informix'-user CPU usage. Greetings

Hi Art, both of them: VPCLASS cpu,num=16,aff=(1,3,5,7,9,11,13,15,17,19,21,23,25,27,29,31),noage VPCLASS cpu,num=32,aff=(1,3,5,7,9,11,13,15,17,19,21,23,25,27,29,31),noage work (as I told Dave, I understand what I was doing wrong with the VPCLASS syntax). But despite of the fact that now upon boot-up informix reports correctly: 09:04:37 Affinitied VP 1 to phys proc 1 09:04:37 Affinitied VP 3 to phys proc 3 09:04:37 Affinitied VP 4 to phys proc 5 09:04:37 Affinitied VP 5 to phys proc 7 09:04:37 Affinitied VP 6 to phys proc 9 09:04:37 Affinitied VP 7 to phys proc 11 09:04:37 Affinitied VP 8 to phys proc 13 09:04:38 Affinitied VP 9 to phys proc 15 09:04:38 Affinitied VP 10 to phys proc 17 09:04:38 Affinitied VP 11 to phys proc 19 09:04:38 Affinitied VP 12 to phys proc 21 09:04:38 Affinitied VP 13 to phys proc 23 09:04:38 Affinitied VP 14 to phys proc 25 09:04:38 Affinitied VP 15 to phys proc 27 09:04:38 Affinitied VP 16 to phys proc 29 09:04:38 Affinitied VP 17 to phys proc 31 09:04:38 Affinitied VP 18 to phys proc 1 09:04:39 Affinitied VP 19 to phys proc 3 09:04:39 Affinitied VP 20 to phys proc 5 09:04:39 Affinitied VP 21 to phys proc 7 09:04:39 Affinitied VP 22 to phys proc 9 09:04:39 Affinitied VP 23 to phys proc 11 09:04:39 Affinitied VP 24 to phys proc 13 09:04:39 Affinitied VP 25 to phys proc 15 09:04:39 Affinitied VP 26 to phys proc 17 09:04:39 Affinitied VP 27 to phys proc 19 09:04:39 Affinitied VP 28 to phys proc 21 09:04:40 Affinitied VP 29 to phys proc 23 09:04:40 Affinitied VP 30 to phys proc 25 09:04:40 Affinitied VP 31 to phys proc 27 09:04:40 Affinitied VP 32 to phys proc 29 09:04:40 Affinitied VP 33 to phys proc 31 still, during the unload process, CPU usage, as shown by prstat -a isn't any higher as 1,3 - 1.4%: $ prstat -a (...) 221 root 20M 7360K sleep 59 0 0:02:53 0,0% devfsadm/8 NPROC USERNAME SWAP RSS MEMORY TIME CPU 81 informix 4648M 4191M 13% 30:54:07 1,4% 82 root 607M 427M 1,3% 49:51:03 0,1% 3 webspher 1994M 1842M 5,6% 23:32:02 0,0% 1 mancbrid 92M 97M 0,3% 1:38:36 0,0% 1 smmsp 2080K 5864K 0,0% 0:01:24 0,0% 1 tomapp 124M 94M 0,3% 0:48:18 0,0% 1 noaccess 126M 140M 0,4% 1:29:34 0,0% 5 daemon 6768K 8864K 0,0% 0:17:25 0,0% and, after a while, the onstat -g glo | cpu output shows this: vp pid class usercpu syscpu total Thread Eff 1 8291 cpu 81.46 71.62 153.08 549.35 27% 3 8334 cpu 84.16 79.51 163.67 624.29 26% 4 8335 cpu 108.68 135.33 244.01 1129.99 21% 5 8336 cpu 79.91 106.29 186.20 887.67 20% 6 8337 cpu 34.93 47.13 82.06 320.40 25% 7 8338 cpu 29.55 39.13 68.68 250.40 27% 8 8339 cpu 21.36 27.03 48.39 182.10 26% 9 8340 cpu 13.41 17.38 30.79 115.30 26% 10 8341 cpu 5.44 5.30 10.74 38.62 27% 11 8342 cpu 2.54 2.35 4.89 19.08 25% 12 8343 cpu 1.07 0.89 1.96 7.50 26% 13 8344 cpu 0.43 0.42 0.85 3.20 26% 14 8345 cpu 0.24 0.30 0.54 1.54 35% 15 8346 cpu 0.08 0.09 0.17 0.31 55% 16 8347 cpu 0.03 0.07 0.10 0.10 100% 17 8348 cpu 0.03 0.09 0.12 0.12 100% 18 8349 cpu 0.03 0.05 0.08 0.08 100% 19 8350 cpu 0.03 0.04 0.07 0.00 0% 20 8351 cpu 0.02 0.03 0.05 0.00 0% 21 8352 cpu 0.02 0.03 0.05 0.00 0% 22 8353 cpu 0.03 0.04 0.07 0.00 0% 23 8354 cpu 0.03 0.03 0.06 0.00 0% 24 8355 cpu 0.03 0.03 0.06 0.00 0% 25 8356 cpu 0.03 0.03 0.06 0.00 0% 26 8357 cpu 0.03 0.04 0.07 0.00 0% 27 8358 cpu 0.03 0.04 0.07 0.00 0% 28 8359 cpu 0.03 0.04 0.07 0.00 0% 29 8360 cpu 0.03 0.03 0.06 0.00 0% 30 8361 cpu 0.03 0.04 0.07 0.00 0% 31 8362 cpu 0.02 0.03 0.05 0.00 0% 32 8363 cpu 0.02 0.03 0.05 0.00 0% 33 8364 cpu 0.02 0.03 0.05 0.00 0% You can see the usercpu and syscpu columns are almost for most of the cpu VP, so I think it's obviously not using more than 8 to 10 cpu VP. Why doesn't it use more CPU power? The BTR, calculated according to what you explain here: http://www.dbmonster.com/Uwe/Forum.aspx/informix/3869/Informix-Performance-Issue s is: Pagreads: 263011805 Bufwrits: 27528339 Bufwaits: 842113 BUFFERS: 1024000 Time since reset: 113.0 ixda-RA: 3813274 idx-RA: 8352 da-RA: 7911577 RA-pgsused: 11529856 _________________________ BTR = ((27528339 + 263011805)/1024000)/113.0 = 2.51/hour (I have a doubt here: is the first term for BTR Bufwrits or Bufwaits? I used Bufwrites). Update: I've just tested a different approach here: until now I was doing just one 'unload to <file> select * ...'. I've launched several simultaneous unloads and there the CPU usage goes up. I did 11 unloads and CPU went up to around 15%, which is roughly: 1.3% CPU usage per unload * 11 unloads = 14.3% In this way I managed to unload 61GB data in 25min, whereas in a single unload it takes about 3h,30min. I will have to have a closer look at this simultaneous unloads to make it work safely, without data loss, but this is a different story. So it seems clear to me know that for whatever reason this machine, with the configuration I have, doesn't use more than about 1/2 a thread per unload process (if it used up 1 whole cpu thread per unload one should expect about 1/32*100 = 3.125% CPU usage, but there's only half of it...). Cheers!

You can consider using external tables for the unload. You will just have to define multiple output files for the external table and Informix will automatically spread the data between the files. SET PDQPRIORITY 100; CREATE EXTERNAL TABLE t1_ext SAMEAS t1 USING ( DATAFILES ('DISK:/tmp/file1.unl','DISK:/tmp/file2.unl','DISK:/tmp/file3.unl'), FORMAT 'DELIMITED', DELIMITER '|', RECORDEND '', Deluxe, NUMROWS 50, MAXERRORS 50, REJECTFILE '' ); INSERT INTO t1_ext SELECT * FROM t1; John F. Miller III STSM, Embedability Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 08/24/2011 01:36:30 AM: > From: "GERARDO PADIERNA" <g.padierna@gmail.com> > To: ids@iiug.org > Date: 08/24/2011 01:37 AM > Subject: Re: VPCLASS cpu on sun/oracle M4000 machine [24686] > Sent by: ids-bounces@iiug.org > > Hi Art, > both of them: > VPCLASS cpu,num=16,aff=(1,3,5,7,9,11,13,15,17,19,21,23,25,27,29,31),noage > VPCLASS cpu,num=32,aff=(1,3,5,7,9,11,13,15,17,19,21,23,25,27,29,31),noage > work (as I told Dave, I understand what I was doing wrong with the VPCLASS> syntax). But despite of the fact that now upon boot-up informix reports > correctly: > 09:04:37 Affinitied VP 1 to phys proc 1 > 09:04:37 Affinitied VP 3 to phys proc 3 > 09:04:37 Affinitied VP 4 to phys proc 5 > 09:04:37 Affinitied VP 5 to phys proc 7 > 09:04:37 Affinitied VP 6 to phys proc 9 > 09:04:37 Affinitied VP 7 to phys proc 11 > 09:04:37 Affinitied VP 8 to phys proc 13 > 09:04:38 Affinitied VP 9 to phys proc 15 > 09:04:38 Affinitied VP 10 to phys proc 17 > 09:04:38 Affinitied VP 11 to phys proc 19 > 09:04:38 Affinitied VP 12 to phys proc 21 > 09:04:38 Affinitied VP 13 to phys proc 23 > 09:04:38 Affinitied VP 14 to phys proc 25 > 09:04:38 Affinitied VP 15 to phys proc 27 > 09:04:38 Affinitied VP 16 to phys proc 29 > 09:04:38 Affinitied VP 17 to phys proc 31 > 09:04:38 Affinitied VP 18 to phys proc 1 > 09:04:39 Affinitied VP 19 to phys proc 3 > 09:04:39 Affinitied VP 20 to phys proc 5 > 09:04:39 Affinitied VP 21 to phys proc 7 > 09:04:39 Affinitied VP 22 to phys proc 9 > 09:04:39 Affinitied VP 23 to phys proc 11 > 09:04:39 Affinitied VP 24 to phys proc 13 > 09:04:39 Affinitied VP 25 to phys proc 15 > 09:04:39 Affinitied VP 26 to phys proc 17 > 09:04:39 Affinitied VP 27 to phys proc 19 > 09:04:39 Affinitied VP 28 to phys proc 21 > 09:04:40 Affinitied VP 29 to phys proc 23 > 09:04:40 Affinitied VP 30 to phys proc 25 > 09:04:40 Affinitied VP 31 to phys proc 27 > 09:04:40 Affinitied VP 32 to phys proc 29 > 09:04:40 Affinitied VP 33 to phys proc 31 > > still, during the unload process, CPU usage, as shown by prstat -a isn't any > higher as 1,3 - 1.4%: > $ prstat -a > (...)> > 221 root 20M 7360K sleep 59 0 0:02:53 0,0% devfsadm/8 > NPROC USERNAME SWAP RSS MEMORY TIME CPU > > 81 informix 4648M 4191M 13% 30:54:07 1,4% > > 82 root 607M 427M 1,3% 49:51:03 0,1% > > 3 webspher 1994M 1842M 5,6% 23:32:02 0,0% > > 1 mancbrid 92M 97M 0,3% 1:38:36 0,0% > > 1 smmsp 2080K 5864K 0,0% 0:01:24 0,0% > > 1 tomapp 124M 94M 0,3% 0:48:18 0,0% > > 1 noaccess 126M 140M 0,4% 1:29:34 0,0% > > 5 daemon 6768K 8864K 0,0% 0:17:25 0,0% > > and, after a while, the onstat -g glo | cpu output shows this: > vp pid class usercpu syscpu total Thread Eff > 1 8291 cpu 81.46 71.62 153.08 549.35 27% > 3 8334 cpu 84.16 79.51 163.67 624.29 26% > 4 8335 cpu 108.68 135.33 244.01 1129.99 21% > 5 8336 cpu 79.91 106.29 186.20 887.67 20% > 6 8337 cpu 34.93 47.13 82.06 320.40 25% > 7 8338 cpu 29.55 39.13 68.68 250.40 27% > 8 8339 cpu 21.36 27.03 48.39 182.10 26% > 9 8340 cpu 13.41 17.38 30.79 115.30 26% > 10 8341 cpu 5.44 5.30 10.74 38.62 27% > 11 8342 cpu 2.54 2.35 4.89 19.08 25% > 12 8343 cpu 1.07 0.89 1.96 7.50 26% > 13 8344 cpu 0.43 0.42 0.85 3.20 26% > 14 8345 cpu 0.24 0.30 0.54 1.54 35% > 15 8346 cpu 0.08 0.09 0.17 0.31 55% > 16 8347 cpu 0.03 0.07 0.10 0.10 100% > 17 8348 cpu 0.03 0.09 0.12 0.12 100% > 18 8349 cpu 0.03 0.05 0.08 0.08 100% > 19 8350 cpu 0.03 0.04 0.07 0.00 0% > 20 8351 cpu 0.02 0.03 0.05 0.00 0% > 21 8352 cpu 0.02 0.03 0.05 0.00 0% > 22 8353 cpu 0.03 0.04 0.07 0.00 0% > 23 8354 cpu 0.03 0.03 0.06 0.00 0% > 24 8355 cpu 0.03 0.03 0.06 0.00 0% > 25 8356 cpu 0.03 0.03 0.06 0.00 0% > 26 8357 cpu 0.03 0.04 0.07 0.00 0% > 27 8358 cpu 0.03 0.04 0.07 0.00 0% > 28 8359 cpu 0.03 0.04 0.07 0.00 0% > 29 8360 cpu 0.03 0.03 0.06 0.00 0% > 30 8361 cpu 0.03 0.04 0.07 0.00 0% > 31 8362 cpu 0.02 0.03 0.05 0.00 0% > 32 8363 cpu 0.02 0.03 0.05 0.00 0% > 33 8364 cpu 0.02 0.03 0.05 0.00 0% > > You can see the usercpu and syscpu columns are almost for most of the cpu VP, > so I think it's obviously not using more than 8 to 10 cpu VP. Why doesn't it > use more CPU power? > > The BTR, calculated according to what you explain here: > > http://www.dbmonster.com/Uwe/Forum.aspx/informix/3869/Informix- > Performance-Issues > is: > Pagreads: 263011805 > Bufwrits: 27528339 > Bufwaits: 842113 > BUFFERS: 1024000 > Time since reset: 113.0 > ixda-RA: 3813274 > idx-RA: 8352 > da-RA: 7911577 > RA-pgsused: 11529856 > _________________________ > BTR = ((27528339 + 263011805)/1024000)/113.0 = 2.51/hour > > (I have a doubt here: is the first term for BTR Bufwrits or Bufwaits? I used > Bufwrites). > > Update: > I've just tested a different approach here: until now I was doing just one > 'unload to <file> select * ...'. I've launched several simultaneous unloads > and there the CPU usage goes up. I did 11 unloads and CPU went up to around > 15%, which is roughly: > 1.3% CPU usage per unload * 11 unloads = 14.3% > In this way I managed to unload 61GB data in 25min, whereas in a > single unload > it takes about 3h,30min. I will have to have a closer look at this > simultaneous unloads to make it work safely, without data loss, but this is a > different story. > > So it seems clear to me know that for whatever reason this machine, with the > configuration I have, doesn't use more than about 1/2 a thread per unload > process (if it used up 1 whole cpu thread per unload one should expect about > 1/32*100 = 3.125% CPU usage, but there's only half of it...). > > Cheers! > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

On a single unload you are IO bound, only a single thread is needed to do the data acquisition unless the table is fragmented and you are doing the unload under positive PDQPRIORITY. Parallel unloading is the answer to utilizing more CPU that is why my dbexport replacement utility, myexport, has a parallel export option which can unload <N> tables at a time in parallel. Your BTR calculation looks right, so at least for the unloads you have plenty of buffers. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Aug 24, 2011 at 4:36 AM, GERARDO PADIERNA <g.padierna@gmail.com>wrote: > Hi Art, > both of them: > VPCLASS cpu,num=16,aff=(1,3,5,7,9,11,13,15,17,19,21,23,25,27,29,31),noage > VPCLASS cpu,num=32,aff=(1,3,5,7,9,11,13,15,17,19,21,23,25,27,29,31),noage > work (as I told Dave, I understand what I was doing wrong with the VPCLASS > syntax). But despite of the fact that now upon boot-up informix reports > correctly: > 09:04:37 Affinitied VP 1 to phys proc 1 > 09:04:37 Affinitied VP 3 to phys proc 3 > 09:04:37 Affinitied VP 4 to phys proc 5 > 09:04:37 Affinitied VP 5 to phys proc 7 > 09:04:37 Affinitied VP 6 to phys proc 9 > 09:04:37 Affinitied VP 7 to phys proc 11 > 09:04:37 Affinitied VP 8 to phys proc 13 > 09:04:38 Affinitied VP 9 to phys proc 15 > 09:04:38 Affinitied VP 10 to phys proc 17 > 09:04:38 Affinitied VP 11 to phys proc 19 > 09:04:38 Affinitied VP 12 to phys proc 21 > 09:04:38 Affinitied VP 13 to phys proc 23 > 09:04:38 Affinitied VP 14 to phys proc 25 > 09:04:38 Affinitied VP 15 to phys proc 27 > 09:04:38 Affinitied VP 16 to phys proc 29 > 09:04:38 Affinitied VP 17 to phys proc 31 > 09:04:38 Affinitied VP 18 to phys proc 1 > 09:04:39 Affinitied VP 19 to phys proc 3 > 09:04:39 Affinitied VP 20 to phys proc 5 > 09:04:39 Affinitied VP 21 to phys proc 7 > 09:04:39 Affinitied VP 22 to phys proc 9 > 09:04:39 Affinitied VP 23 to phys proc 11 > 09:04:39 Affinitied VP 24 to phys proc 13 > 09:04:39 Affinitied VP 25 to phys proc 15 > 09:04:39 Affinitied VP 26 to phys proc 17 > 09:04:39 Affinitied VP 27 to phys proc 19 > 09:04:39 Affinitied VP 28 to phys proc 21 > 09:04:40 Affinitied VP 29 to phys proc 23 > 09:04:40 Affinitied VP 30 to phys proc 25 > 09:04:40 Affinitied VP 31 to phys proc 27 > 09:04:40 Affinitied VP 32 to phys proc 29 > 09:04:40 Affinitied VP 33 to phys proc 31 > > still, during the unload process, CPU usage, as shown by prstat -a isn't > any > higher as 1,3 - 1.4%: > $ prstat -a > (...)> > 221 root 20M 7360K sleep 59 0 0:02:53 0,0% devfsadm/8 > NPROC USERNAME SWAP RSS MEMORY TIME CPU > > 81 informix 4648M 4191M 13% 30:54:07 1,4% > > 82 root 607M 427M 1,3% 49:51:03 0,1% > > 3 webspher 1994M 1842M 5,6% 23:32:02 0,0% > > 1 mancbrid 92M 97M 0,3% 1:38:36 0,0% > > 1 smmsp 2080K 5864K 0,0% 0:01:24 0,0% > > 1 tomapp 124M 94M 0,3% 0:48:18 0,0% > > 1 noaccess 126M 140M 0,4% 1:29:34 0,0% > > 5 daemon 6768K 8864K 0,0% 0:17:25 0,0% > > and, after a while, the onstat -g glo | cpu output shows this: > vp pid class usercpu syscpu total Thread Eff > 1 8291 cpu 81.46 71.62 153.08 549.35 27% > 3 8334 cpu 84.16 79.51 163.67 624.29 26% > 4 8335 cpu 108.68 135.33 244.01 1129.99 21% > 5 8336 cpu 79.91 106.29 186.20 887.67 20% > 6 8337 cpu 34.93 47.13 82.06 320.40 25% > 7 8338 cpu 29.55 39.13 68.68 250.40 27% > 8 8339 cpu 21.36 27.03 48.39 182.10 26% > 9 8340 cpu 13.41 17.38 30.79 115.30 26% > 10 8341 cpu 5.44 5.30 10.74 38.62 27% > 11 8342 cpu 2.54 2.35 4.89 19.08 25% > 12 8343 cpu 1.07 0.89 1.96 7.50 26% > 13 8344 cpu 0.43 0.42 0.85 3.20 26% > 14 8345 cpu 0.24 0.30 0.54 1.54 35% > 15 8346 cpu 0.08 0.09 0.17 0.31 55% > 16 8347 cpu 0.03 0.07 0.10 0.10 100% > 17 8348 cpu 0.03 0.09 0.12 0.12 100% > 18 8349 cpu 0.03 0.05 0.08 0.08 100% > 19 8350 cpu 0.03 0.04 0.07 0.00 0% > 20 8351 cpu 0.02 0.03 0.05 0.00 0% > 21 8352 cpu 0.02 0.03 0.05 0.00 0% > 22 8353 cpu 0.03 0.04 0.07 0.00 0% > 23 8354 cpu 0.03 0.03 0.06 0.00 0% > 24 8355 cpu 0.03 0.03 0.06 0.00 0% > 25 8356 cpu 0.03 0.03 0.06 0.00 0% > 26 8357 cpu 0.03 0.04 0.07 0.00 0% > 27 8358 cpu 0.03 0.04 0.07 0.00 0% > 28 8359 cpu 0.03 0.04 0.07 0.00 0% > 29 8360 cpu 0.03 0.03 0.06 0.00 0% > 30 8361 cpu 0.03 0.04 0.07 0.00 0% > 31 8362 cpu 0.02 0.03 0.05 0.00 0% > 32 8363 cpu 0.02 0.03 0.05 0.00 0% > 33 8364 cpu 0.02 0.03 0.05 0.00 0% > > You can see the usercpu and syscpu columns are almost for most of the cpu > VP, > so I think it's obviously not using more than 8 to 10 cpu VP. Why doesn't > it > use more CPU power? > > The BTR, calculated according to what you explain here: > > > http://www.dbmonster.com/Uwe/Forum.aspx/informix/3869/Informix-Performance-Issue s > is: > Pagreads: 263011805 > Bufwrits: 27528339 > Bufwaits: 842113 > BUFFERS: 1024000 > Time since reset: 113.0 > ixda-RA: 3813274 > idx-RA: 8352 > da-RA: 7911577 > RA-pgsused: 11529856 > _________________________ > BTR = ((27528339 + 263011805)/1024000)/113.0 = 2.51/hour > > (I have a doubt here: is the first term for BTR Bufwrits or Bufwaits? I > used > Bufwrites). > > Update: > I've just tested a different approach here: until now I was doing just one > 'unload to <file> select * ...'. I've launched several simultaneous unloads > and there the CPU usage goes up. I did 11 unloads and CPU went up to around > 15%, which is roughly: > 1.3% CPU usage per unload * 11 unloads = 14.3% > In this way I managed to unload 61GB data in 25min, whereas in a single > unload > it takes about 3h,30min. I will have to have a closer look at this > simultaneous unloads to make it work safely, without data loss, but this is > a > different story. > > So it seems clear to me know that for whatever reason this machine, with > the > configuration I have, doesn't use more than about 1/2 a thread per unload > process (if it used up 1 whole cpu thread per unload one should expect > about > 1/32*100 = 3.125% CPU usage, but there's only half of it...). > > Cheers! > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --20cf307cffc4ed5de504ab3ea9f2