Re: Slow database creation and loading

Hello John, if you set lrumax and mindirty to 99 you have to generate your own checkpoints!!!! if you do not want to generate checkpoints you may want to set lrumaxdirty to 10 and set mindirty to say 5 or smaller. 400000 for BUFFERS means 4 * 400000 = 1.6 GB of buffer cache.... assume you have that memory. > The HPL process (and I found out we are using the Express mode not the > Deluxe mode like I first thought) is taking about 4 hrs to load > the 10GB of data, then another 3 hrs to create rowids (we needed to 4 hours for 10 GB is afwul. what are your disks doing???? create rowids i assume you set pdpriority and have that configured properly... 3 hours are LONGGGG post the onconfig also check your disks if they are 100 % busy.. also how long are checkpoints taking??? some time ago i used HPL in express mode and reloaded a 100 GB table on a sun which took only 1 hour. again dependant on disks memory cpu etc. Superboer. On 21 jun, 15:42, johneevo <johne...@gmail.com> wrote: > On Jun 19, 3:05 am, Superboer <superbo...@t-online.de> wrote: > > Hi Superboer, > > Thanks for helping me with this. > > Last night I changed the BUFFERS to 400000 and increased LRUS and > CLEANERS to 99 for each and this did not speed things up. > > So tonight I am going to try also make some of the changes that you > suggested, lowering NUMAIOVPS from 36 to 4 and > increasing the PHYSBUFF and LOGBUFF from 64 to 512 for each > > I'm not ready yet to try the "generatechkpt" stored proc (although I > might be soon...) I figure I'll just try a few things at > a time and see what if any affect they have. > > I should probably give a little more info on the overall time things > are taking to run, since me first post probably gave the impression > that > the HPL process is taking 10+ hrs. > > The HPL process (and I found out we are using the Express mode not the > Deluxe mode like I first thought) is taking about 4 hrs to load > the 10GB of data, then another 3 hrs to create rowids (we needed to > remove them before running HPL since these tables are fragmented), > 1 hr to enable contraints etc. and then 2hrs for table stats. > > Do you still think we can speed this up, or is this the best we can > get using the version that we have? > > John > > > > 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 Kbytes000 > > > 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 ovuserth

On Jun 21, 1:04 pm, Superboer <superbo...@t-online.de> wrote: Hi Superboer, > > if you set lrumax and mindirty to 99 you have to generate your own > checkpoints!!!! > if you do not want to generate checkpoints you may want to set > lrumaxdirty to 10 and set mindirty to say > 5 or smaller. I'll try setting lrumaxdirty and mindirty to 10 and 5 respectively for tonights load and see who things go. > 400000 for BUFFERS means 4 * 400000 = 1.6 GB of buffer cache.... > assume you have that memory. Yes, I realize that. We have 4 GB of memory but we do have 2 db instances on this box (one for development, the other for testing/ deployment), only the testing/deployment db gets refreshed nightly. I am wondering if that is to much memory to grab, but so far nobody has complained about the performance going down after I made this change. > 4 hours for 10 GB is afwul. what are your disks doing???? > create rowids i assume you set pdpriority and have that configured > properly... This how we are setting pdqpriority from the shell file: export MAX_PDQPRIORITY=100 PSORT_NPROCS=6 PDQPRIORITY=100 FET_BUF_SIZE=32767 Then after the import we turn PDQ off. > 3 hours are LONGGGG > post the onconfig also check your disks if they are 100 % busy.. > also how long are checkpoints taking??? I apologize but I don't know how to check how busy the disks are nor how long the checkpoints are taking. Here is our config. NOTE: I changed the NUMAIOVPS, PHYSBUFF and LOGBUFF values this morning and the server wont be bounced until tonight before the import starts. #************************************************************************** # # INFORMIX SOFTWARE, INC. # # Title: onconfig.insurnace # Description: INFORMIX-OnLine Configuration Parameters # #************************************************************************** # Root Dbspace Configuration ROOTNAME rootdbs # Root dbspace nameROOTPATH /usr/informix/dblinks/infrootlv1 # Path for device containing root dbspace ROOTOFFSET 4 # Offset of root dbspace into device (Kbytes) ROOTSIZE 2097147 # Size of root dbspace (Kbytes) # Disk Mirroring Configuration Parameters MIRROR 1 # Mirroring flag (Yes = 1, No = 0) MIRRORPATH # Path for device containing mirroredroot MIRROROFFSET 0 # Offset into mirrored device (Kbytes) # Physical Log Configuration PHYSDBS rootdbs # Location (dbspace) of physical log PHYSFILE 40000 # Physical log file size (Kbytes) # Logical Log Configuration LOGFILES 14 # Number of logical log files LOGSIZE 10000 # Logical log size (Kbytes) # Diagnostics MSGPATH /usr/informix/online.insurance # System message log file path CONSOLE /usr/informix/console.insurance # System console message path ALARMPROGRAM /usr/informix/log_full.sh # Alarm program path # System Archive Tape Device TAPEDEV /dev/null # Tape device path TAPEBLK 1024 # Tape block size (Kbytes) TAPESIZE 12000000 # Maximum amount of data to put on tape (Kbytes) # Log Archive Tape Device LTAPEDEV /dev/null # Log tape device path LTAPEBLK 1024 # Log tape block size (Kbytes) LTAPESIZE 4096000 # Max amount of data to put on log tape (Kbytes) # Optical STAGEBLOB # INFORMIX-OnLine/Optical staging area # System Configuration SERVERNUM 0 # Unique id corresponding to a OnLineinstance DBSERVERNAME insurance_online # Name of default database server DBSERVERALIASES insurance_remote # List of alternate dbservernames NETTYPE soctcp,3,600,CPU # Added for Inf. TS 2/24/97 #NETTYPE onsoctcp,3,600,NET # Changed for Inf. CSA 5/3/06 DEADLOCK_TIMEOUT 60 # Max time to wait of lock indistributed env. RESIDENT 0 # Forced residency flag (Yes = 1, No = 0) MULTIPROCESSOR 1 # 0 for single-processor, 1 for multi-processor NUMCPUVPS 3 # Number of user (cpu) vps SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vpsto one NOAGE 1 # Process aging AFF_SPROC 0 # Affinity start processor AFF_NPROCS 0 # Affinity number of processors # Shared Memory Parameters LOCKS 750000 # Maximum number of locks# BUFFERS 75000 # Maximum number of shared buffers BUFFERS 400000 # Maximum number of shared buffers JE 6/19/07#NUMAIOVPS 36 # Number of IO vps changed CSA 05/3/06 NUMAIOVPS 4 # Number of IO vps changed JE 06/21/07#PHYSBUFF 64 # Physical log buffer size (Kbytes) #LOGBUFF 64 # Logical log buffer size (Kbytes) PHYSBUFF 512 # Physical log buffer size (Kbytes) JE 6/21/07 LOGBUFF 512 # Logical log buffer size (Kbytes) JE 6/21/07LOGSMAX 40 # Maximum number of logical log files #CLEANERS 32 # Number of buffer cleaner processes CLEANERS 99 # Number of buffer cleaner processes JE 6/20/07 SHMBASE 0x30000000 # Shared memory base address#SHMVIRTSIZE 131072 # initial virtual shared memory segment size SHMVIRTSIZE 524288 # initial virtual shared memorysegment size SHMADD 32768 # Size of new shared memory segments (Kbytes) SHMTOTAL 0 # Total shared memory (Kbytes). 0=>unlimited CKPTINTVL 1200 # Check point interval (in sec)# LRUS 4 # Number of LRU queues # Test config for LRUS. From NG posting seems buffers to LRUS should be a # ratio of 2560 BUFFERS to 1 LRUS. LRUS 99 # Number of LRU queues JE 6/20/07#LRUS 19 # Number of LRU queues LRU_MAX_DIRTY 10 # LRU percent dirty begin cleaninglimit LRU_MIN_DIRTY 5 # LRU percent dirty end cleaning limit LTXHWM 50 # Long transaction high water markpercentage LTXEHWM 60 # Long transaction high water mark (exclusive) TXTIMEOUT 0x12c # Transaction timeout (in sec) STACKSIZE 32 # Stack size (Kbytes) # System Page Size # BUFFSIZE - OnLine no longer supports this configuration parameter. # To determine the page size used by OnLine on your platform # see the last line of output from the command, 'onstat - b'. # Recovery Variables # OFF_RECVRY_THREADS: # Number of parallel worker threads during fast recovery or an offline restore. # ON_RECVRY_THREADS: # Number of parallel worker threads during an online restore. OFF_RECVRY_THREADS

johneevo schreef: > This how we are setting pdqpriority from the shell file: > export MAX_PDQPRIORITY=100 PSORT_NPROCS=6 PDQPRIORITY=100 > FET_BUF_SIZE=32767 The above looks good so does the onconfig stuff. (i do assume if you create a backup you use onbar -b -w or external backup??) > I apologize but I don't know how to check how busy the disks are nor on ex sun iostat -dx <sampletime> < nr samples> or sar -d <sampletime> < nr samples> on aix afaicr there is a utitlity called topas which also displays iostats.... aix should also have sar and if i am not mistaken iostat??? > how long the checkpoints are taking. how long checkpoints are taking, have a look in your log file (onstat - m) > 1 hour?!?!?! Did that include indexes and constraints? No only data load, indexes where created later using pdq etc. I would create a script and set pdq etc after HPL. HPL attempts to set pdq, but you do not have full controll. Superboer. > > When we started with HPL the loading of the data was only taking > seconds but re-enabling the indexes and constraints is where things > slowed down. > > John

On Jun 22, 3:06 am, Superboer <superbo...@t-online.de> wrote: Hi Superboer, No real speedup last night either, maybe saved 5 minutes. > (i do assume if you create a backup you use onbar -b -w or external > backup??) I'm not a dba or a sys admin, but I think we are using ontape not onbar. Since this is our development/test server I don't think we are backing up the logs. On our test server the backups go to /dev/ null. > on ex sun > iostat -dx <sampletime> < nr samples> or > sar -d <sampletime> < nr samples> > > on aix afaicr there is a utitlity called topas which also displays > iostats.... > aix should also have sar and if i am not mistaken iostat??? > > how long the checkpoints are taking. > > how long checkpoints are taking, have a look in your log file (onstat - > m) Sorry I should have mentioned that we are running aix on an RS-6000. I will run sar during the next import on Saturday night. I'll check the checkpoints then also. > > 1 hour?!?!?! Did that include indexes and constraints? > > No only data load, indexes where created later using pdq etc. > I would create a script and set pdq etc after HPL. > HPL attempts to set pdq, but you do not have full controll. Ah, I wonder if that's why we are not getting better performance. Our tables are being created with the indexes, constraints, etc and then HPL takes care of disabling and re-enabling them. But if HPL is setting its own PDQ, then it might not be setting using optimal settings of index creation. If I could find an easy way to muck with the SQL file created by dbexport each night to move index, constraint and trigger creations to another SQL file then maybe we could speed things up.