Load Performance problems

I have a IDS 7.30 UC8-1 server running on HP-UX 11.0 system. The database is a data warehouse and I am trying to configure it so that it is optimized for a DSS environment. We process daily data loads at night that sometimes run 6 to 8 hours. During the load checkpoints are occuring at almost every five minutes (probably because the physical buffer is being filled). I want to configure the server so that NO checkpoints occur any time during the load. (I will force a checkpoint before the load and after the load in my script) I have tried changing many parameters (LRUS, PHYSBUFF, CLEANERS) to try and make this happen but nothing has worked. I have tried increasing PHYSBUFF up to 1024 to no avail... How can I configure my server so that during my load checkpoints do not occur?? Here is my onconfig file details: #*********************************************************************** *** # # INFORMIX SOFTWARE, INC. # # Title: onconfig.std # Description: INFORMIX-OnLine Configuration Parameters # #*********************************************************************** *** # Root Dbspace Configuration ROOTNAME inf_root # Root dbspace name ROOTPATH /dev/vgdata01/rinf_root # Path for device containing root dbspace ROOTOFFSET 0 # Offset of root dbspace into device (Kbytes) ROOTSIZE 512000 # 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) B # Physical Log Configuration PHYSDBS inf_root # Location (dbspace) of physical log PHYSFILE 50000 # Physical log file size (Kbytes) # Logical Log Configuration LOGFILES 12 # Number of logical log files LOGSIZE 2000 # Logical log size (Kbytes) # Diagnostics MSGPATH /usr/informix/online.log # System message log file path CONSOLE /dev/console # System console message pathALARMPROGRAM /users/informix/log_full.sh # Alarm program path # System Archive Tape Device TAPEDEV /dev/rmt/3m # Tape device path TAPEBLK 128 # Tape block size (Kbytes) TAPESIZE 15000000 # Maximum amount of data to put ontape (Kbytes ) # Log Archive Tape Device LTAPEDEV /dev/null # Log tape device path LTAPEBLK 16 # Log tape block size (Kbytes) LTAPESIZE 10240 # 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 buffet_ipc # Name of default database serverDBSERVERALIASES buffet_tcp,buffet_tcp2 # List of alternate dbservernames NETTYPE ipcshm,4,300,CPU # Configure poll thread(s) for nettype NETTYPE soctcp,4,300,NET # Configure poll thread(s) for nettype 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 4 # 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 15000 # Maximum number of locks BUFFERS 225000 # Maximum number of shared buffers NUMAIOVPS 25 # Number of IO vps PHYSBUFF 64 # Physical log buffer size (Kbytes) LOGBUFF 64 # Logical log buffer size (Kbytes)LOGSMAX 25 # Maximum number of logical log files CLEANERS 128 # Number of buffer cleaner processes SHMBASE 0x0 # Shared memory base address SHMVIRTSIZE 250000 # initial virtual shared memorysegment size SHMADD 40000 # Size of new shared memory segments (Kbytes) SHMTOTAL 0 # Total shared memory (Kbytes). 0=>unlimited CKPTINTVL 3000 # Check point interval (in sec) LRUS 128 # Number of LRU queues LRU_MAX_DIRTY 80 # LRU percent dirty begin cleaning limit LRU_MIN_DIRTY 70 # 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) QSTATS 1 # 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 10 # Default number of offline workerthreads ON_RECVRY_THREADS 1 # Default number of online workerthreads # Data Replication Variables # DRAUTO: 0 manual, 1 retain type, 2 reverse type DRAUTO 0 # DR automatic switchover DRINTERVAL 30 # DR max time between DR buffer flushes (in sec) DRTIMEOUT 30 # DR network timeout (in sec) DRLOSTFOUND /users/informix/etc/dr.lost.found # DR lost+found filepath # Backup/Restore variables BAR_ACT_LOG /tmp/bar_act.log BAR_MAX_BACKUP 0 BAR_RETRY 1 BAR_NB_XPORT_COUNT 10 BAR_XFER_BUF_SIZE 31 # Read Ahead Variables RA_PAGES 32 # Number of pages to attempt to readahead RA_THRESHOLD 4 # Number of pages left before next group # DBSPACETEMP: # OnLine equivalent of DBTEMP for SE. This is the list of dbspaces # that the OnLine SQL Engine will use to create temp tables etc. # If specified it must be a colon separated list of dbspaces that exist # when the OnLine system is brought online. If not specified, or if # all dbspaces specified are invalid, various ad hoc queries will create # temporary files in /tmp instead. DBSPACETEMP temp1:temp2:temp3:temp4 # Default temp dbspaces # DUMP*: # The following parameters control the type of diagnostics information which # is preserved when an unanticipated error condition (assertion failure) occurs # during OnLine operations

benlag@aent.com wrote: > I have a IDS 7.30 UC8-1 server running on HP-UX 11.0 system. > > The database is a data warehouse and I am trying to configure it so > that it is optimized for a DSS environment. We process daily data loads > at night that sometimes run 6 to 8 hours. During the load checkpoints > are occuring at almost every five minutes (probably because the > physical buffer is being filled). I want to configure the server so > that NO checkpoints occur any time during the load. (I will force a > checkpoint before the load and after the load in my script) I have > tried changing many parameters (LRUS, PHYSBUFF, CLEANERS) to try and > make this happen but nothing has worked. I have tried increasing > PHYSBUFF up to 1024 to no avail...> > How can I configure my server so that during my load checkpoints do not > occur?? Checkpoints occur under the following conditions 1. Physical Log (not buffer) fills to 75% 2. CKPTINTVL seconds elapse after the previous checkpoint (and some activity has occurred) 3. The database server detects that the next logical-log file to become current contains the most-recent checkpoint record. 4. A few others that are probably not applicable to your case in point You've set your CKPTINVTL to 3000. This means that your checkpoints are being triggered by either 1 or 3. Going by the size of your Physical log v/s the size of all your Logical logs, I'd be inclined to start viewing the latter with the beady eye (if your database has logging). You can easily confirm this by examining your Message log file. At the time of the daily load, check the number of logs that have been filled between checkpoints. Rudy

The parameters that control this are CKPTINTVL and PHYSFILE. You will not be able to eliminate checkpoints altogether unless you are willing to make PHYSFILE 125% of the number of pages that will be updated during the load which is unreasonble. You could however, make CKPTINTVL 3600 (1hr) and make PHYSFILE large enough to hold an hours worth of preimages (about 1/8 the volume of the load). Also change LRU_MAX_DIRTY & LRU_MIN_DIRTY to 5 and 0 respectively to minimize the impact of each checkpoint. Just a hint but if you upgrade to 7.31 which began to implement light weight checkpoints, and even more so in 9.2x which completed the process, the physical log is unlikely to trigger early checkpoints AND checkpoints do NOT flush all dirty buffers as they do in 7.30 and earlier so the impact of each checkpoint is even smaller (and the values of LRU_MAX/MIN_DIRTY even more important!) Art S. Kagel benlag@aent.com wrote: > > I have a IDS 7.30 UC8-1 server running on HP-UX 11.0 system. > > The database is a data warehouse and I am trying to configure it so > that it is optimized for a DSS environment. We process daily data loads > at night that sometimes run 6 to 8 hours. During the load checkpoints > are occuring at almost every five minutes (probably because the > physical buffer is being filled). I want to configure the server so > that NO checkpoints occur any time during the load. (I will force a > checkpoint before the load and after the load in my script) I have > tried changing many parameters (LRUS, PHYSBUFF, CLEANERS) to try and > make this happen but nothing has worked. I have tried increasing > PHYSBUFF up to 1024 to no avail...> > How can I configure my server so that during my load checkpoints do not > occur?? > > Here is my onconfig file details: > #*********************************************************************** > *** > # > # INFORMIX SOFTWARE, INC. > # > # Title: onconfig.std > # Description: INFORMIX-OnLine Configuration Parameters > # > #*********************************************************************** > *** > > # Root Dbspace Configuration > > ROOTNAME inf_root # Root dbspace name > ROOTPATH /dev/vgdata01/rinf_root> # Path for device containing root > dbspace > ROOTOFFSET 0 # Offset of root dbspace into device > (Kbytes) > ROOTSIZE 512000 # Size of root dbspace (Kbytes)> > # Disk Mirroring Configuration Parameters > MIRROR 1 # Mirroring flag (Yes = 1, No = 0) > MIRRORPATH # Path for device containing mirrored> root > MIRROROFFSET 0 # Offset into mirrored device (Kbytes)> > B > # Physical Log Configuration > > PHYSDBS inf_root # Location (dbspace) of physical log > PHYSFILE 50000 # Physical log file size (Kbytes)> > # Logical Log Configuration > > LOGFILES 12 # Number of logical log files > LOGSIZE 2000 # Logical log size (Kbytes)> > # Diagnostics > > MSGPATH /usr/informix/online.log # System message log file path > CONSOLE /dev/console # System console message path> ALARMPROGRAM /users/informix/log_full.sh # Alarm program path > > # System Archive Tape Device > TAPEDEV /dev/rmt/3m # Tape device path > TAPEBLK 128 # Tape block size (Kbytes) > TAPESIZE 15000000 # Maximum amount of data to put on> tape (Kbytes > ) > > # Log Archive Tape Device > > LTAPEDEV /dev/null # Log tape device path > LTAPEBLK 16 # Log tape block size (Kbytes) > LTAPESIZE 10240 # 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 OnLine> instance > DBSERVERNAME buffet_ipc # Name of default database server> DBSERVERALIASES buffet_tcp,buffet_tcp2 # List of alternate > dbservernames > NETTYPE ipcshm,4,300,CPU # Configure poll thread(s) for nettype > NETTYPE soctcp,4,300,NET # Configure poll thread(s) for nettype > DEADLOCK_TIMEOUT 60 # Max time to wait of lock in> distributed env. > RESIDENT 0 # Forced residency flag (Yes = 1, No = > 0) > > MULTIPROCESSOR 1 # 0 for single-processor, 1 for multi-> processor > NUMCPUVPS 4 # Number of user (cpu) vps > SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps> to one > > NOAGE 1 # Process aging > AFF_SPROC 0 # Affinity start processor > AFF_NPROCS 0 # Affinity number of processors> > # Shared Memory Parameters > > LOCKS 15000 # Maximum number of locks > BUFFERS 225000 # Maximum number of shared buffers > NUMAIOVPS 25 # Number of IO vps > PHYSBUFF 64 # Physical log buffer size (Kbytes) > LOGBUFF 64 # Logical log buffer size (Kbytes)> LOGSMAX 25 # Maximum number of logical log files > CLEANERS 128 # Number of buffer cleaner processes > SHMBASE 0x0 # Shared memory base address > SHMVIRTSIZE 250000 # initial virtual shared memory> segment size > SHMADD 40000 # Size of new shared memory segments > (Kbytes) > SHMTOTAL 0 # Total shared memory (Kbytes). > 0=>unlimited > CKPTINTVL 3000 # Check point interval (in sec) > LRUS 128 # Number of LRU queues > LRU_MAX_DIRTY 80 # LRU percent dirty begin cleaning limit > LRU_MIN_DIRTY 70 # LRU percent dirty end cleaning limit > LTXHWM 50 # Long transaction high water mark> percentage > LTXEHWM 60 # Long transaction high water mark > (exclusive) > TXTIMEOUT 0x12c # Transaction timeout (in sec) > STACKSIZE 32 # Stack size (Kbytes) > QSTATS 1> > # 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 10 # Default number of offline worker> threads > ON_RECVRY_THREADS 1 # Default number of online worker> threads > > # Data Replication Variables > # DRAUTO: 0 manual, 1 retain type, 2 reverse type > DRAUTO 0 # DR automatic switchover > DRINTERVAL 30 # DR max time between DR

benlag@aent.com wrote: > I have a IDS 7.30 UC8-1 server running on HP-UX 11.0 system. > > The database is a data warehouse and I am trying to configure it so > that it is optimized for a DSS environment. We process daily data loads > at night that sometimes run 6 to 8 hours. During the load checkpoints > are occuring at almost every five minutes (probably because the > physical buffer is being filled). I want to configure the server so > that NO checkpoints occur any time during the load. (I will force a > checkpoint before the load and after the load in my script) I have > tried changing many parameters (LRUS, PHYSBUFF, CLEANERS) to try and > make this happen but nothing has worked. I have tried increasing > PHYSBUFF up to 1024 to no avail...> > How can I configure my server so that during my load checkpoints do not > occur?? >[...big snip...] What causes a checkpoint? There's the checkpoint interval. There's the physical log getting too full. There's manually forcing a checkpoint with 'onmode -c'. So, if you don't want any checkpoints to occur for 8 hours of data loading, your physical log has to be so big that it can hold 8 hours worth of clean pages with 25% spare capacity. Your checkpoint interval has to be at least 8*3600. And you have to prevent everyone from using 'onmode -c' (which is trivial). Note that PHYSBUFF is not material. Oh, you'll also need to make sure your logical logs are big enough to cope with all this activity -- a checkpoint is triggered when the next logical log page to be used contains the previous checkpoint record. I think the log sizes you are likely to need for 8 hours of data loading make your desired requirement impractical. Somehow, I don't think that checkpoints are really the problem. You are presumably wanting to avoid flushing all the data to disk all the time During a load operation, you want to try and ensure that your data won't be being scribbled around the database at random (eg no round robin fragmentation?). Loading the data in sorted order might help, therefore. Also, don't forget that all the data you load does have to be written to disk sooner or later; the key is to ensure that it is done smoothly as the load progresses, and that you don't have to reread data pages previously written to disk. EOW -- End of Waffle! -- Yours, Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h> Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN "I don't suffer from insanity; I enjoy every minute of it!"