Insert performance is poor

Hi, I have an ultra2 (with 2 300 mhz cpu's and 512M of ram) running solaris 2.6 and informix 7.31 on a RAID5 hardware raid using raw files. The database inserts via an application about 4 million records a day. Lately I'm only inserting about 3,000 records in a 5 minute period where I used to be able to insert about 8,000-12,000 records in the same 5 minute period. I'm not sure what happened. What configurations could I do to improve performance? The following is my onconfig file, please let me know what setting you would put. Thanks in advance... ROOTNAME rootdbs # Root dbspace name ROOTPATH /u/informix/links/ROOT_00_0681 ROOTOFFSET 2 # Offset of root dbspace into device ROOTSIZE 681000 # Size of root dbspace (Kbytes)MIRROR 1 # Mirroring flag (Yes = 1, No = 0) MIRRORPATH # Path for device containing mirroredroot MIRROROFFSET 0 # Offset into mirrored device (Kbytes)PHYSDBS rootdbs # Location (dbspace) of physical log PHYSFILE 5000 # Physical log file size (Kbytes) LOGFILES 6 # Number of logical log files LOGSIZE 4000 # Logical log size (Kbytes)MSGPATH /usr/informix/online.log # System message log file path CONSOLE /usr/informix/console.log # System console message path ALARMPROGRAM /u/informix/etc/log_full.sh # Alarm program path TAPEDEV /dev/null # Tape device path TAPEBLK 16 # Tape block size (Kbytes) TAPESIZE 10240 # Maximum amount of data to put on tape LTAPEDEV /dev/null # Log tape device path LTAPEBLK 16 # Log tape block size (Kbytes) LTAPESIZE 10240 # Max amount of data to put on log tapeSTAGEBLOB # INFORMIX-OnLine/Optical staging area SERVERNUM 10 # Unique id corresponding to a OnLine DBSERVERNAME n36_shm # Name of default database server DBSERVERALIASES n36_tcp # List of alternate dbservernames NETTYPE ipcshm,1,50,NET # Override sqlhosts nettype parameters NETTYPE tlitcp,1,50,CPU # Override sqlhosts nettype parameters DEADLOCK_TIMEOUT 60 # Max time to wait of lock in RESIDENT 1 # Forced residency flag (Yes = 1, No = MULTIPROCESSOR 1 # 0 for single-processor, 1 for multi- NUMCPUVPS 2 # Number of user (cpu) vps SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps NOAGE 1 # Process aging AFF_SPROC 0 # Affinity start processor AFF_NPROCS 0 # Affinity number of processors LOCKS 48000 # Maximum number of locks BUFFERS 64000 # Maximum number of shared buffers NUMAIOVPS 2 # Number of IO vps PHYSBUFF 512 # Physical log buffer size (Kbytes) LOGBUFF 64 # Logical log buffer size (Kbytes)LOGSMAX 10 # Maximum number of logical log files CLEANERS 20 # Number of buffer cleaner processes SHMBASE 0xa000000 # Shared memory base address SHMVIRTSIZE 64000 # initial virtual shared memory segment SHMADD 8192 # Size of new shared memory segments SHMTOTAL 0 # Total shared memory (Kbytes). CKPTINTVL 600 # Check point interval (in sec) LRUS 20 # Number of LRU queues LRU_MAX_DIRTY 60 # LRU percent dirty begin cleaning limit LRU_MIN_DIRTY 50 # LRU percent dirty end cleaning limit LTXHWM 50 # Long transaction high water mark LTXEHWM 60 # Long transaction high water mark TXTIMEOUT 0x12c # Transaction timeout (in sec) STACKSIZE 32 # Stack size (Kbytes) OFF_RECVRY_THREADS 10 # Default number of offline worker ON_RECVRY_THREADS 2 # Default number of online worker DRAUTO 0 # DR automatic switchover DRINTERVAL 30 # DR max time between DR buffer flushes DRTIMEOUT 30 # DR network timeout (in sec)DRLOSTFOUND /usr/informix/etc/dr.lostfound # DR lost+found file path BAR_ACT_LOG /tmp/bar_act.log BAR_MAX_BACKUP 0 BAR_RETRY 1 BAR_NB_XPORT_COUNT 10 BAR_XFER_BUF_SIZE 31 RA_PAGES 20 # Number of pages to attempt to read RA_THRESHOLD 4 # Number of pages left before next group DBSPACETEMP rootdbs # Default temp dbspaces DUMPDIR /tmp # Preserve diagnostics in this directory DUMPSHMEM 0 # Dump a copy of shared memory DUMPGCORE 0 # Dump a core image using 'gcore' DUMPCORE 0 # Dump a core image (Warning:this DUMPCNT 1 # Number of shared memory or gcore FILLFACTOR 90 # Fill factor for building indexes USEOSTIME 0 # 0: use internal time(fast), 1: get MAX_PDQPRIORITY 100 # Maximum allowed pdqpriority DS_MAX_QUERIES 40000 # Maximum number of decision support DS_TOTAL_MEMORY # Decision support memory (Kbytes) DS_MAX_SCANS 1000000 # Maximum number of decision support DATASKIP ALL # List of dbspaces to skip OPTCOMPIND 0 # To hint the optimizer ONDBSPACEDOWN 0 # Dbspace down option: 0 = CONTINUE, 1LBU_PRESERVE 0 # Preserve last log for log backup OPCACHEMAX 128 # Maximum optical cache size (Kbytes) HETERO_COMMIT 0CDR_LOGBUFFERS 2048 # size of log reading buffer pool CDR_EVALTHREADS 1,1 # evaluator threads (per-cpu- CDR_DSLOCKWAIT 5 # DS lockwait timeout (seconds) CDR_QUEUEMEM 4096 # Maximum amount of memory for any CDRSYSALARMPROGRAM /u/informix/etc/evidence.sh # System Alarm program path TBLSPACE_STATS 1CDR_LOGDELTA 30 # % of log space allowed in queue memory CDR_NUMCONNECT 16 # Expected connections per server CDR_NIFRETRY 300 # Connection retry (seconds) CDR_NIFCOMPRESS 0 # Link level compression (-1 never, 0ISM_DATA_POOL ISMData # If the data pool name is changed, be ISM_LOG_POOL ISMLogs OPT_GOAL -1 DIRECTIVES 1 RESTARTABLE_RESTORE off Sent via Deja.com http://www.deja.com/ Before you buy.

mr_potato_head@my-deja.com schrieb: > DBSERVERNAME n36_shm # Name of default database server > DBSERVERALIASES n36_tcp # List of alternate dbservernames > NETTYPE ipcshm,1,50,NET # Override sqlhosts nettype parameters > NETTYPE tlitcp,1,50,CPU # Override sqlhosts nettype parameters You should switch nettype-shm to CPU and nettype-tli to NET. To what is INFORMIXSERVER set for your insert-job ? Hope it is set n36_shm.

Have you added any indexes? Have you recently deleted many rows from the table? The reason I mention this is that inserts to a full table which extends the number of pages used, inserting to pages never touched before, is more efficient than inserts which re-use pages deleted from. Inserts to previously allocated pages require that the pages be read in off of disk, even if they are empty. Inserts to newly allocated pages which extend the number of pages used do not require the read. mr_potato_head@my-deja.com wrote: > Hi, > I have an ultra2 (with 2 300 mhz cpu's and 512M of ram) running > solaris 2.6 and informix 7.31 on a RAID5 hardware raid using raw > files. The database inserts via an application about 4 million records > a day. Lately I'm only inserting about 3,000 records in a 5 minute > period where I used to be able to insert about 8,000-12,000 records in > the same 5 minute period. I'm not sure what happened. What > configurations could I do to improve performance? The following is my > onconfig file, please let me know what setting you would put. Thanks > in advance... > > ROOTNAME rootdbs # Root dbspace name > ROOTPATH /u/informix/links/ROOT_00_0681 > ROOTOFFSET 2 # Offset of root dbspace into device > ROOTSIZE 681000 # Size of root dbspace (Kbytes)> MIRROR 1 # Mirroring flag (Yes = 1, No = 0) > MIRRORPATH # Path for device containing mirrored> root > MIRROROFFSET 0 # Offset into mirrored device (Kbytes)> PHYSDBS rootdbs # Location (dbspace) of physical log > PHYSFILE 5000 # Physical log file size (Kbytes) > LOGFILES 6 # Number of logical log files > LOGSIZE 4000 # Logical log size (Kbytes)> MSGPATH /usr/informix/online.log # System message log file path > CONSOLE /usr/informix/console.log # System console message path > ALARMPROGRAM /u/informix/etc/log_full.sh # Alarm program path > TAPEDEV /dev/null # Tape device path > TAPEBLK 16 # Tape block size (Kbytes) > TAPESIZE 10240 # Maximum amount of data to put on tape > 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> STAGEBLOB # INFORMIX-OnLine/Optical staging area > SERVERNUM 10 # Unique id corresponding to a OnLine > DBSERVERNAME n36_shm # Name of default database server > DBSERVERALIASES n36_tcp # List of alternate dbservernames > NETTYPE ipcshm,1,50,NET # Override sqlhosts nettype parameters > NETTYPE tlitcp,1,50,CPU # Override sqlhosts nettype parameters > DEADLOCK_TIMEOUT 60 # Max time to wait of lock in > RESIDENT 1 # Forced residency flag (Yes = 1, No = > MULTIPROCESSOR 1 # 0 for single-processor, 1 for multi- > NUMCPUVPS 2 # Number of user (cpu) vps > SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps > NOAGE 1 # Process aging > AFF_SPROC 0 # Affinity start processor > AFF_NPROCS 0 # Affinity number of processors > LOCKS 48000 # Maximum number of locks > BUFFERS 64000 # Maximum number of shared buffers > NUMAIOVPS 2 # Number of IO vps > PHYSBUFF 512 # Physical log buffer size (Kbytes) > LOGBUFF 64 # Logical log buffer size (Kbytes)> LOGSMAX 10 # Maximum number of logical log files > CLEANERS 20 # Number of buffer cleaner processes > SHMBASE 0xa000000 # Shared memory base address > SHMVIRTSIZE 64000 # initial virtual shared memory segment > SHMADD 8192 # Size of new shared memory segments > SHMTOTAL 0 # Total shared memory (Kbytes). > CKPTINTVL 600 # Check point interval (in sec) > LRUS 20 # Number of LRU queues > LRU_MAX_DIRTY 60 # LRU percent dirty begin cleaning limit > LRU_MIN_DIRTY 50 # LRU percent dirty end cleaning limit > LTXHWM 50 # Long transaction high water mark > LTXEHWM 60 # Long transaction high water mark > TXTIMEOUT 0x12c # Transaction timeout (in sec) > STACKSIZE 32 # Stack size (Kbytes) > OFF_RECVRY_THREADS 10 # Default number of offline worker > ON_RECVRY_THREADS 2 # Default number of online worker > DRAUTO 0 # DR automatic switchover > DRINTERVAL 30 # DR max time between DR buffer flushes > DRTIMEOUT 30 # DR network timeout (in sec)> DRLOSTFOUND /usr/informix/etc/dr.lostfound # DR lost+found file path > BAR_ACT_LOG /tmp/bar_act.log > BAR_MAX_BACKUP 0 > BAR_RETRY 1 > BAR_NB_XPORT_COUNT 10 > BAR_XFER_BUF_SIZE 31 > RA_PAGES 20 # Number of pages to attempt to read > RA_THRESHOLD 4 # Number of pages left before next group > DBSPACETEMP rootdbs # Default temp dbspaces > DUMPDIR /tmp # Preserve diagnostics in this directory > DUMPSHMEM 0 # Dump a copy of shared memory > DUMPGCORE 0 # Dump a core image using 'gcore' > DUMPCORE 0 # Dump a core image (Warning:this > DUMPCNT 1 # Number of shared memory or gcore > FILLFACTOR 90 # Fill factor for building indexes > USEOSTIME 0 # 0: use internal time(fast), 1: get > MAX_PDQPRIORITY 100 # Maximum allowed pdqpriority > DS_MAX_QUERIES 40000 # Maximum number of decision support > DS_TOTAL_MEMORY # Decision support memory (Kbytes) > DS_MAX_SCANS 1000000 # Maximum number of decision support > DATASKIP ALL # List of dbspaces to skip > OPTCOMPIND 0 # To hint the optimizer > ONDBSPACEDOWN 0 # Dbspace down option: 0 = CONTINUE, 1> LBU_PRESERVE 0 # Preserve last log for log backup > OPCACHEMAX 128 # Maximum optical cache size (Kbytes) > HETERO_COMMIT 0> CDR_LOGBUFFERS 2048 # size of log reading buffer pool > CDR_EVALTHREADS 1,1 # evaluator threads (per-cpu- > CDR_DSLOCKWAIT 5 # DS lockwait timeout (seconds) > CDR_QUEUEMEM 4096 # Maximum amount of memory for any CDR> SYSALARMPROGRAM /u/informix/etc/evidence.sh # System Alarm program path > TBLSPACE_STATS 1> CDR_LOGDELTA 30 # % of log space allowed in queue memory > CDR_NUMCONNECT 16 # Expected connections per server > CDR_NIFRETRY 300 # Connection retry (seconds) > CDR_NIFCOMPRESS 0 # Link level compression (-1 never, 0> ISM_DATA_POOL ISMData # If the data pool name is changed, be > ISM_LOG_POOL ISMLogs > OPT_GOAL -1> DIRECTIV

See comments below -- Tony Flaherty Snr. A/P, Informix DBA, HpUx Admin, Gimmi a broom! MFS Ltd. mr_potato_head@my-deja.com wrote in message <8rmed1$k2c$1@nnrp1.deja.com>... >Hi, > I have an ultra2 (with 2 300 mhz cpu's and 512M of ram) running >solaris 2.6 and informix 7.31 on a RAID5 hardware raid using raw RAID5, Oh-hom duck Mr. Kagels comments quick :o)) RAID5 is bad for databases, see any one of the posts by Art Kagel on this. >files. The database inserts via an application about 4 million records >a day. Lately I'm only inserting about 3,000 records in a 5 minute >period where I used to be able to insert about 8,000-12,000 records in >the same 5 minute period. I'm not sure what happened. What >configurations could I do to improve performance? The following is my >onconfig file, please let me know what setting you would put. Thanks >in advance... > > >ROOTNAME rootdbs # Root dbspace name >ROOTPATH /u/informix/links/ROOT_00_0681 >ROOTOFFSET 2 # Offset of root dbspace into device >ROOTSIZE 681000 # Size of root dbspace (Kbytes)>MIRROR 1 # Mirroring flag (Yes = 1, No = 0) >MIRRORPATH # Path for device containing mirrored>root >MIRROROFFSET 0 # Offset into mirrored device (Kbytes)>PHYSDBS rootdbs # Location (dbspace) of physical log Move rootdbs to its own dbspace. >PHYSFILE 5000 # Physical log file size (Kbytes) >LOGFILES 6 # Number of logical log files >LOGSIZE 4000 # Logical log size (Kbytes) more logs I would have thought. >MSGPATH /usr/informix/online.log # System message log file path >CONSOLE /usr/informix/console.log # System console message path >ALARMPROGRAM /u/informix/etc/log_full.sh # Alarm program path >TAPEDEV /dev/null # Tape device path >TAPEBLK 16 # Tape block size (Kbytes) Backing up to NULL !!!!!!!!!!!! >TAPESIZE 10240 # Maximum amount of data to put on tape >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>STAGEBLOB # INFORMIX-OnLine/Optical staging area >SERVERNUM 10 # Unique id corresponding to a OnLine >DBSERVERNAME n36_shm # Name of default database server >DBSERVERALIASES n36_tcp # List of alternate dbservernames >NETTYPE ipcshm,1,50,NET # Override sqlhosts nettype parameters >NETTYPE tlitcp,1,50,CPU # Override sqlhosts nettype parameters >DEADLOCK_TIMEOUT 60 # Max time to wait of lock in >RESIDENT 1 # Forced residency flag (Yes = 1, No = >MULTIPROCESSOR 1 # 0 for single-processor, 1 for multi- >NUMCPUVPS 2 # Number of user (cpu) vps >SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps >NOAGE 1 # Process aging >AFF_SPROC 0 # Affinity start processor >AFF_NPROCS 0 # Affinity number of processors >LOCKS 48000 # Maximum number of locks >BUFFERS 64000 # Maximum number of shared buffers maybe increase this, whats your onstat -p look like? >NUMAIOVPS 2 # Number of IO vps do you have KAIO enabled? otherwise you need NUMAIOVPS >= number of chunks. >PHYSBUFF 512 # Physical log buffer size (Kbytes) >LOGBUFF 64 # Logical log buffer size (Kbytes)>LOGSMAX 10 # Maximum number of logical log files >CLEANERS 20 # Number of buffer cleaner processes >SHMBASE 0xa000000 # Shared memory base address >SHMVIRTSIZE 64000 # initial virtual shared memory segment >SHMADD 8192 # Size of new shared memory segments >SHMTOTAL 0 # Total shared memory (Kbytes). >CKPTINTVL 600 # Check point interval (in sec) >LRUS 20 # Number of LRU queues >LRU_MAX_DIRTY 60 # LRU percent dirty begin cleaning limit >LRU_MIN_DIRTY 50 # LRU percent dirty end cleaning limit To help prevent long checkpoints (if you need to ,OLTP?) try 4,2 or maybe even 2,1 >LTXHWM 50 # Long transaction high water mark >LTXEHWM 60 # Long transaction high water mark >TXTIMEOUT 0x12c # Transaction timeout (in sec) >STACKSIZE 32 # Stack size (Kbytes) >OFF_RECVRY_THREADS 10 # Default number of offline worker >ON_RECVRY_THREADS 2 # Default number of online worker >DRAUTO 0 # DR automatic switchover >DRINTERVAL 30 # DR max time between DR buffer flushes >DRTIMEOUT 30 # DR network timeout (in sec)>DRLOSTFOUND /usr/informix/etc/dr.lostfound # DR lost+found file path >BAR_ACT_LOG /tmp/bar_act.log >BAR_MAX_BACKUP 0 >BAR_RETRY 1 >BAR_NB_XPORT_COUNT 10 >BAR_XFER_BUF_SIZE 31 >RA_PAGES 20 # Number of pages to attempt to read >RA_THRESHOLD 4 # Number of pages left before next group Try PAGES 32, THRESHOLD 24 >DBSPACETEMP rootdbs # Default temp dbspaces This is Baaad!! set up multiple temp dbspaces on different disks, don't use the rootdbs. >DUMPDIR /tmp # Preserve diagnostics in this directory >DUMPSHMEM 0 # Dump a copy of shared memory >DUMPGCORE 0 # Dump a core image using 'gcore' >DUMPCORE 0 # Dump a core image (Warning:this >DUMPCNT 1 # Number of shared memory or gcore >FILLFACTOR 90 # Fill factor for building indexes >USEOSTIME 0 # 0: use internal time(fast), 1: get >MAX_PDQPRIORITY 100 # Maximum allowed pdqpriority >DS_MAX_QUERIES 40000 # Maximum number of decision support >DS_TOTAL_MEMORY # Decision support memory (Kbytes) >DS_MAX_SCANS 1000000 # Maximum number of decision support >DATASKIP ALL # List of dbspaces to skip >OPTCOMPIND 0 # To hint the optimizer >ONDBSPACEDOWN 0 # Dbspace down option: 0 = CONTINUE, 1>LBU_PRESERVE 0 # Preserve last log for log backup >OPCACHEMAX 128 # Maximum optical cache size (Kbytes) >HETERO_COMMIT 0>CDR_LOGBUFFERS 2048 # size of log reading buffer pool >CDR_EVALTHREADS 1,1 # evaluator threads (per-cpu- >CDR_DSLOCKWAIT 5 # DS lockwait timeout (seconds) >CDR_QUEUEMEM 4096 # Maximum amount of memory for any CDR>SYSALARMPROGRAM /u/informix/etc/evidence.sh # System Alarm program path >TBLSPACE_STATS 1 This has a huge (allegedly 20-30%) processing overhead, it should be turned of (0) unless you are trouble shooting a specific problem. >CDR_LOGDELTA 30 # % of l

Tony Flaherty wrote: > >TBLSPACE_STATS 1> > This has a huge (allegedly 20-30%) processing overhead, it should be turned > of (0) unless you are trouble shooting a specific problem. > This is'nt quite right. TBLSPACE_STATS hardly affects performance (but allows you to retrieve extremely valuable statistics). You could be referring to QSTATS (Queue statistics "onstat -g qst), that appears to have a 20% CPU overhead. There's another similar onconfig parameter, WSTATS (Wait Statistics "onstat -g wst") that appears to have little or no impact on performance. I guess this could be a matter of the profile of the Instance (number of users, activity, etc), but I've tested TBLSPACE_STATS on v7.3 and v9.2 on widely differing instances with similar results - no impact, nada, zilch, kuch nahi. Rudy

mr_potato_head@my-deja.com wrote: > Hi, > I have an ultra2 (with 2 300 mhz cpu's and 512M of ram) running > solaris 2.6 and informix 7.31 on a RAID5 hardware raid using raw > files. The database inserts via an application about 4 million records > a day. Lately I'm only inserting about 3,000 records in a 5 minute > period where I used to be able to insert about 8,000-12,000 records in > the same 5 minute period. I'm not sure what happened. What > configurations could I do to improve performance? The following is my > onconfig file, please let me know what setting you would put. Thanks > in advance... > You could try disabling/enabling the indexes on the table. Indexes can become inefficient with time, usually occurring on tables experiencing a high volume of DELETEs, INSERTs and UPDATEs. Periodic rebuilding of such Indexes is recommended. Rudy