Re: Slow database creation and loading

On Jun 11, 3:07 am, Superboer <superbo...@t-online.de> wrote: Hi Superboer, Thanks for the reply. > You should be able to speed this up. > > > I have been told that we have 4 GB of memory on this box. > > BUFFERS 75000 # Maximum number of shared buffers> > 4 GB available and only 75000*4k=300MB of buffer cache.. i would make > this bigger > and therefor increase > > > PHYSFILE 40000 # Physical log file size (Kbytes) > > NUMAIOVPS 36 # Number of IO vps changed CSA 05/3/06 Any suggestions on what the increase the the BUFFERS to. And I guess there is some ratio that the BUFFERS to PHYSFILE should be set to. If this is correct would you mind tell me what that ratio is? > Are you using kernel io??? > onstat -g ioa will tell.Yes it appears that we are using kernel io if I am reading the onstat - g ioa otput correctly. The kio lines have the majority the reads and writes. > if so decrease NUMAIOVPS if using cooked files then consider raw > please Any suggestions on that the decrease the NUMAIOVPS to? Or is this just a trail and error type of tuning? > > > PHYSBUFF 64 # Physical log buffer size (Kbytes) > bigger. > > LOGBUFF 64 # Logical log buffer size (Kbytes)> > bigger. I'm sorry, but once again, any suggestions on what to increase these buffers to? > During the load it may be interesting to see what the db is doing, so > an onstat -p > may help... Here is the onstat -p output while the load is running. This was taken while the HPL portion was running. Informix Dynamic Server Version 7.31.UD7 -- On-Line (CKPT REQ) -- Up 01:47:2 6 -- 873952 Kbytes Blocked:CKPT Profile dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached 1389715 7807595 27016290 94.86 1038143 2602162 2919803 64.44 isamtot open start read write rewrite delete commit rollbk 53729641 130660 148793 12402936 35365788 3679 13512 5474 139 gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs 0 0 0 0 0 0 0 ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes 0 0 0 3789.28 409.72 356 725 bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans 709578 0 15464370 0 0 580 1856 11850 ixda-RA idx-RA da-RA RA-pgsused lchwaits 79 1 255074 255136 57787 > also you state dbimport/???? if load/unload, make sure that indexes > are created after > data is loaded. I'll check on this, but I believe these are rather small tables. The contain a "TEXT" column and thus would not work with HPL (or at least we couldn't get it to work). > Also you could consider generating your own checkpoints > setting lrumax and min dirty to 99, > check buffer cache if 75 % dirty then do a onmode -c > (i have cut load times have in half using this......) I would love to cut my load times in half, but the load needs to run unattended so it looks like this option is out unless I'm missing the boat completely with your comment here. John

If you are using HPL, that grabs it's own memory. Data does not go through the normal buffer pool (unless you are using deluxe mode). You might not want to increase buffers. j. -----Original Message----- From: informix-list-bounces@iiug.org [mailto:informix-list-bounces@iiug.org]On Behalf Of johneevo Sent: Monday, June 18, 2007 10:19 PM To: informix-list@iiug.org Subject: Re: Slow database creation and loading On Jun 11, 3:07 am, Superboer <superbo...@t-online.de> wrote: Hi Superboer, Thanks for the reply. > You should be able to speed this up. > > > I have been told that we have 4 GB of memory on this box. > > BUFFERS 75000 # Maximum number of shared buffers> > 4 GB available and only 75000*4k=300MB of buffer cache.. i would make > this bigger > and therefor increase > > > PHYSFILE 40000 # Physical log file size (Kbytes) > > NUMAIOVPS 36 # Number of IO vps changed CSA 05/3/06 Any suggestions on what the increase the the BUFFERS to. And I guess there is some ratio that the BUFFERS to PHYSFILE should be set to. If this is correct would you mind tell me what that ratio is? > Are you using kernel io??? > onstat -g ioa will tell.Yes it appears that we are using kernel io if I am reading the onstat - g ioa otput correctly. The kio lines have the majority the reads and writes. > if so decrease NUMAIOVPS if using cooked files then consider raw > please Any suggestions on that the decrease the NUMAIOVPS to? Or is this just a trail and error type of tuning? > > > PHYSBUFF 64 # Physical log buffer size (Kbytes) > bigger. > > LOGBUFF 64 # Logical log buffer size (Kbytes)> > bigger. I'm sorry, but once again, any suggestions on what to increase these buffers to? > During the load it may be interesting to see what the db is doing, so > an onstat -p > may help... Here is the onstat -p output while the load is running. This was taken while the HPL portion was running. Informix Dynamic Server Version 7.31.UD7 -- On-Line (CKPT REQ) -- Up 01:47:2 6 -- 873952 Kbytes Blocked:CKPT Profile dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached 1389715 7807595 27016290 94.86 1038143 2602162 2919803 64.44 isamtot open start read write rewrite delete commit rollbk 53729641 130660 148793 12402936 35365788 3679 13512 5474 139 gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs 0 0 0 0 0 0 0 ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes 0 0 0 3789.28 409.72 356 725 bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans 709578 0 15464370 0 0 580 1856 11850 ixda-RA idx-RA da-RA RA-pgsused lchwaits 79 1 255074 255136 57787 > also you state dbimport/???? if load/unload, make sure that indexes > are created after > data is loaded. I'll check on this, but I believe these are rather small tables. The contain a "TEXT" column and thus would not work with HPL (or at least we couldn't get it to work). > Also you could consider generating your own checkpoints > setting lrumax and min dirty to 99, > check buffer cache if 75 % dirty then do a onmode -c > (i have cut load times have in half using this......) I would love to cut my load times in half, but the load needs to run unattended so it looks like this option is out unless I'm missing the boat completely with your comment here. John _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list

On Jun 18, 10:50 pm, "Jack Parker" <jack.park...@verizon.net> wrote: > If you are using HPL, that grabs it's own memory. Data does not go through > the normal buffer pool (unless you are using deluxe mode). You might not > want to increase buffers. I need to double check but I believe we are using deluxe mode, something to do with fragmented tables prevented express mode from working. I we are using deluxe mode is it ok to increase buffers?

> I would love to cut my load times in half, but the load needs to run > unattended so it looks like this option is out unless I'm missing it can run unattended... please tes all first on a test box... as informix: set lrumin and maxdirty to 99 in $ONCONFIG bounce the engine. dbaccess sysmaster <<! -- WARNING CHECK THE CODE may contain a bug.. create procedure generatechkpt()define dirty decimal (4,3); while (1=1) select ( sum(lru_nmod) / sum ( lru_nfree + lru_nmod )) into dirty from syslrus ; if (dirty < 0.75 ) then system "sleep 1"; else system "onmode -c"; end if end while ; end procedure; execute procedure generatechkpt();! run your load when done do onmode -c set lrumin and maxdirty back to what it was and bounce your engine. regarding onconfig: grab 1 GB for bufferecache at least; BUFFERS 250000 # Maximum number of shared buffers > > > PHYSFILE 40000 # Physical log file size (Kbytes)i do not want a checkpoint when this becomes 75 % full so use onparams to set the size to 1 GB or 1.5 GB. You should be safe since you are on 7.31.UD7. if all is raw and KAIO set NUMAIOVPS to 4 max or to 2. PHYSBUFF 512 # Physical log buffer size (Kbytes) LOGBUFF 512 # Logical log buffer size (Kbytes) > contain a "TEXT" column and thus would not work with HPL > (or at least we couldn't get it to work). it does work in deluxe as someone else stated, and yes you can increase your buffer cache it will help!!!!! also the above hack spl will help. i also see > Informix Dynamic Server Version 7.31.UD7 -- On-Line (CKPT REQ) -- -->> checkpoint request.. how long are your checkpoints??? can your disks cope??? i sure hope no raid 5; ask Art why. Superboer. On 19 jun, 04:19, johneevo <johne...@gmail.com> wrote: > On Jun 11, 3:07 am, Superboer <superbo...@t-online.de> wrote: > Hi Superboer, > > Thanks for the reply. > > > You should be able to speed this up. > > > > I have been told that we have 4 GB of memory on this box. > > > BUFFERS 75000 # Maximum number of shared buffers> > > 4 GB available and only 75000*4k=300MB of buffer cache.. i would make > > this bigger > > and therefor increase > > > > PHYSFILE 40000 # Physical log file size (Kbytes) > > > NUMAIOVPS 36 # Number of IO vps changed CSA 05/3/06> > Any suggestions on what the increase the the BUFFERS to. And I guess > there is some ratio that the BUFFERS to PHYSFILE > should be set to. If this is correct would you mind tell me what that > ratio is? > > > Are you using kernel io??? > > onstat -g ioa will tell.> > Yes it appears that we are using kernel io if I am reading the onstat - > g ioa otput correctly. The kio lines have the majority > the reads and writes. > > > if so decrease NUMAIOVPS if using cooked files then consider raw > > please > > Any suggestions on that the decrease the NUMAIOVPS to? Or is this > just a trail and error type of tuning? > > > > > > PHYSBUFF 64 # Physical log buffer size (Kbytes) > > bigger. > > > LOGBUFF 64 # Logical log buffer size (Kbytes)> > > bigger. > > I'm sorry, but once again, any suggestions on what to increase these > buffers to? > > > During the load it may be interesting to see what the db is doing, so > > an onstat -p > > may help... > > Here is the onstat -p output while the load is running. This was > taken while the HPL portion was running. > > Informix Dynamic Server Version 7.31.UD7 -- On-Line (CKPT REQ) -- > Up 01:47:2 > 6 -- 873952 Kbytes > Blocked:CKPT > > Profile > dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached > 1389715 7807595 27016290 94.86 1038143 2602162 2919803 64.44 > > isamtot open start read write rewrite delete > commit rollbk > 53729641 130660 148793 12402936 35365788 3679 13512 > 5474 139 > > gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs > 0 0 0 0 0 0 0 > > ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes > 0 0 0 3789.28 409.72 356 725 > > bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress > seqscans > 709578 0 15464370 0 0 580 1856 11850 > > ixda-RA idx-RA da-RA RA-pgsused lchwaits > 79 1 255074 255136 57787 > > > also you state dbimport/???? if load/unload, make sure that indexes > > are created after > > data is loaded. > > I'll check on this, but I believe these are rather small tables. The > contain a "TEXT" column and thus would not work with HPL > (or at least we couldn't get it to work). > > > Also you could consider generating your own checkpoints > > setting lrumax and min dirty to 99, > > check buffer cache if 75 % dirty then do a onmode -c > > (i have cut load times have in half using this......) > > I would love to cut my load times in half, but the load needs to run > unattended so it looks like this option is out unless I'm missing > the boat completely with your comment here. > > John

Yes, if in deluxe mode, you are not really getting the full benefit of HPL, just some parallel input files. j. -----Original Message----- From: informix-list-bounces@iiug.org [mailto:informix-list-bounces@iiug.org]On Behalf Of johneevo Sent: Monday, June 18, 2007 11:05 PM To: informix-list@iiug.org Subject: Re: Slow database creation and loading On Jun 18, 10:50 pm, "Jack Parker" <jack.park...@verizon.net> wrote: > If you are using HPL, that grabs it's own memory. Data does not go through > the normal buffer pool (unless you are using deluxe mode). You might not > want to increase buffers. I need to double check but I believe we are using deluxe mode, something to do with fragmented tables prevented express mode from working. I we are using deluxe mode is it ok to increase buffers? _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list

On Jun 18, 10:19 pm, johneevo <johne...@gmail.com> wrote: > On Jun 11, 3:07 am, Superboer <superbo...@t-online.de> wrote: > Hi Superboer, > > Thanks for the reply. > > > You should be able to speed this up. > > > > I have been told that we have 4 GB of memory on this box. > > > BUFFERS 75000 # Maximum number of shared buffers> > > 4 GB available and only 75000*4k=300MB of buffer cache.. i would make > > this bigger > > and therefor increase > > > > PHYSFILE 40000 # Physical log file size (Kbytes) > > > NUMAIOVPS 36 # Number of IO vps changed CSA 05/3/06> > Any suggestions on what the increase the the BUFFERS to. And I guess > there is some ratio that the BUFFERS to PHYSFILE > should be set to. If this is correct would you mind tell me what that > ratio is? > > > Are you using kernel io??? > > onstat -g ioa will tell.> > Yes it appears that we are using kernel io if I am reading the onstat - > g ioa otput correctly. The kio lines have the majority > the reads and writes. > > > if so decrease NUMAIOVPS if using cooked files then consider raw > > please > > Any suggestions on that the decrease the NUMAIOVPS to? Or is this > just a trail and error type of tuning? > > > > > > PHYSBUFF 64 # Physical log buffer size (Kbytes) > > bigger. > > > LOGBUFF 64 # Logical log buffer size (Kbytes)> > > bigger. > > I'm sorry, but once again, any suggestions on what to increase these > buffers to? > > > During the load it may be interesting to see what the db is doing, so > > an onstat -p > > may help... > > Here is the onstat -p output while the load is running. This was > taken while the HPL portion was running. > > Informix Dynamic Server Version 7.31.UD7 -- On-Line (CKPT REQ) -- > Up 01:47:2 > 6 -- 873952 Kbytes > Blocked:CKPT > > Profile > dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached > 1389715 7807595 27016290 94.86 1038143 2602162 2919803 64.44 > > isamtot open start read write rewrite delete > commit rollbk > 53729641 130660 148793 12402936 35365788 3679 13512 > 5474 139 > > gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs > 0 0 0 0 0 0 0 > > ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes > 0 0 0 3789.28 409.72 356 725 > > bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress > seqscans > 709578 0 15464370 0 0 580 1856 11850 > > ixda-RA idx-RA da-RA RA-pgsused lchwaits > 79 1 255074 255136 57787 > > > also you state dbimport/???? if load/unload, make sure that indexes > > are created after > > data is loaded. > > I'll check on this, but I believe these are rather small tables. The > contain a "TEXT" column and thus would not work with HPL > (or at least we couldn't get it to work). > > > Also you could consider generating your own checkpoints > > setting lrumax and min dirty to 99, > > check buffer cache if 75 % dirty then do a onmode -c > > (i have cut load times have in half using this......) > > I would love to cut my load times in half, but the load needs to run > unattended so it looks like this option is out unless I'm missing > the boat completely with your comment here. John, Looking at your onstat -p output, I've calculated your critical metrics and: BR = 6.61 BTR = 81.73 RAU = 99.990 A Buffwaits Ratio (BR) over 7 means a slow server over 10 is server performance death, at 6.61 yours is certainly not optimal. I would increase LRUS and CLEANERS (always keep CLEANER >= LRUS) to lower your BR. A Buffer Turnover Rate of 81.73 means that you are turning over the entire buffer cache almost 82 times an hour or about every 44 seconds. Ideally BTR should be single digits to get down that low you'll have to increase the number of buffers by several times, I'd guess that ~400000-500000 would get you below 9. The Readahead Utilization (RAU) is fine and should be as close as possible to 100.00%, 99.990 is about as good as it gets in practice. Art S. Kagel