Poor Performance with 7.31.uc6

Hello all, We have a database that was upgraded from 7.23.uc5 to 7.31.uc6 a couple of weeks ago and we seem to be experiencing some performance issues. This is an OLTP system that processes insurance claims. Claims that would have taken a few seconds to process are now taking several minutes. I am concerned we have had an index go bad, but I won't be able to check that until later when I can bring the system down. I know I need examine configuration options as well. The poor performance has not been a constant problem. It is possible that the performance decreases with the amount of time the server has been up, as we had to cycle it last week and everything seemed fine until the past couple of days. After the upgrade I performed the "Update Statistics" as noted in the documentation, but I have seen a post from Clown suggesting "UPDATE STATISTICS DROP DISTRIBUTIONS". Would this make a significant difference? Using recent posts as a guide I am including output from onstat -c, onstat -p and onstat -P | tail -5, onstat -u | tail-2, onstat-F and onstat -R. None of the onconfig values have been changed since the upgrade, and these values were setup by the previous DBA, so I don't know why specific values were chosen. I know the last bufwaits ratio I calculated was much higher than Art's recommended 7%. I appreciate any input! Thanks, Ty -- Ty O'Kelly DBA tokelly@maxor.invalid.com 806-324-5521 onstat -c: # Root Dbspace Configuration ROOTNAME rootdbs # Root dbspace nameROOTPATH /dev/informix.new/rootdbs # Path for device containing root dbspace ROOTOFFSET 0 # Offset of root dbspace into device (Kbytes) ROOTSIZE 500000 # Size of root dbspace (Kbytes) # Disk Mirroring Configuration Parameters MIRROR 0 # 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 50000 # Physical log file size (Kbytes) # Logical Log Configuration LOGFILES 4 # Number of logical log files LOGSIZE 30000 # Logical log size (Kbytes) # Diagnostics MSGPATH /usr/informix/etc/online.primary # System message log file path CONSOLE /dev/console # System console message path ALARMPROGRAM /usr/informix/etc/no_log.sh # Alarm program path # System Archive Tape Device #TAPEDEV /dev/rmt/0 # Tape device path TAPEDEV maxdev:/dev/rmt/0 # Tape device path TAPEBLK 62 # Tape block size (Kbytes) TAPESIZE 12000000 # Maximum amount of data to put on tape (Kbytes) # Log Archive Tape Device #LTAPEDEV /dev/rmt/0 # Log tape device path LTAPEDEV maxdev:/dev/rmt/0 # Log tape device path LTAPEBLK 62 # Log tape block size (Kbytes) LTAPESIZE 2000000 # Max amount of data to put on log tape (Kbytes) # Optical STAGEBLOB ,1 # INFORMIX-OnLine/Optical staging area # System Configuration SERVERNUM 118 # Unique id corresponding to a OnLineinstance DBSERVERNAME primary # Name of default database server DBSERVERALIASES net_primary # List of alternate dbservernames 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 formulti-processor NUMCPUVPS 3 # Number of user (cpu) vps SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vpsto one NOAGE 0 # Process aging AFF_SPROC 1 # Affinity start processor AFF_NPROCS 3 # Affinity number of processors # Shared Memory Parameters LOCKS 100000 # Maximum number of locks BUFFERS 80000 # Maximum number of shared buffers NUMAIOVPS 1 # Number of IO vps PHYSBUFF 128 # Physical log buffer size (Kbytes) LOGBUFF 32 # Logical log buffer size (Kbytes)LOGSMAX 40 # Maximum number of logical log files CLEANERS 14 # Number of buffer cleaner processes SHMBASE 0xa000000 # Shared memory base address SHMVIRTSIZE 70000 # initial virtual shared memory segmentsize # SHMVIRTSIZE 8000 # initial virtual shared memory segment size SHMADD 25000 # Size of new shared memory segments (Kbytes) SHMTOTAL 0 # Total shared memory (Kbytes). 0=>unlimited CKPTINTVL 300 # Check point interval (in sec) LRUS 8 # Number of LRU queues LRU_MAX_DIRTY 2 # LRU percent dirty begin cleaning limit LRU_MIN_DIRTY 1 # LRU percent dirty end cleaning limit LTXHWM 40 # Long transaction high water markpercentage LTXEHWM 50 # 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 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 10 # DR max time between DR buffer flushes (in sec) DRTIMEOUT 600 # DR network timeout (in sec) DRLOSTFOUND /tmp/dr.lostfound # DR lost+found file path # CDR Variables CDR_LOGBUFFERS 2048 # size of log reading buffer pool (Kbytes) CDR_EVALTHREADS 1,2 # evaluator threads (per-cpu-vp,additional) CDR_DSLOCKWAIT 5 # DS lockwait timeout (seconds) CDR_QUEUEMEM 4096 # Maximum amount of memory for any CDR queue (Kbytes) # Backup/Restore variables BAR_ACT_LOG /tmp/bar_act.log BAR_MAX_BACKUP 2 BAR_RETRY 1 BAR_NB_XPORT_COUNT 10 BAR_XFER_BUF_SIZE 31 # Read Ahead Variables RA_PAGES 64 # Number of pages to attempt to readahead RA_THRESHOLD 60 # Number o

You are running into a known bug in 7.2x that is KILLING the 7.31 server. The 7.2x servers were not diligent about properly marking index node and leaf pages in their headers with the correct pagetype flags. In 7.2x this had no deliterious (SP?) effect, but in 7.3x buffer cache pages are prioritized based on those flags with RESIDENT datapages HIGH, index nodes MED-HIGH, data pages and index leaves MED-LOW and overhead and system catalog pages LOW priority. A read of a new page into the cache will favor replacing a lower priority page. The problem is that 7.2x marked all or most of your index pages MED-HIGH priority so they are starving out data pages over time (see the onstat -P output tail to see that >92% of your buffer cache is BTREE (index) pages! Bouncing the server will solve the problem for a day or two, as you already noted. A short term quick fix is to define the environment variable NOLRUPRIO in the environment that starts the engine and bounce it to disable the buffer priority code. The permanent solution is to recreate all of your indexes using 7.31 (best to do that with PDQPRIORITY and the PSORT_ parameters set). Another gotcha going from 7.2x to 7.3x is the new Corellated Sub-Query Flattening feature of 7.31. Since simple joins are normally faster than corellated sub-queries, and since ANY corellated sub-query can be rewritten as a simple join, the engine willl do so for you. However if you do not have the indexes to support the new join the sub-query version will have run faster. You will need to look at all of your corellated subqueries and make sure that there are indexes available to support the rewritten code. Run each with SET EXPLAIN to see the resulting join code and the query path to determine what indexes are missing. (This can also be turned off with an environment variable.) Art S. Kagel Ty O'Kelly wrote: > Hello all, > > We have a database that was upgraded from 7.23.uc5 to 7.31.uc6 a couple > of weeks ago and we seem to be experiencing some performance issues. > This is an OLTP system that processes insurance claims. Claims that > would have taken a few seconds to process are now taking several > minutes. I am concerned we have had an index go bad, but I won't be > able to check that until later when I can bring the system down. I know > I need examine configuration options as well. The poor performance has > not been a constant problem. It is possible that the performance > decreases with the amount of time the server has been up, as we had to > cycle it last week and everything seemed fine until the past couple of > days. After the upgrade I performed the "Update Statistics" as noted in > the documentation, but I have seen a post from Clown suggesting "UPDATE > STATISTICS DROP DISTRIBUTIONS". Would this make a significant > difference? Using recent posts as a guide I am including output from > onstat -c, onstat -p and onstat -P | tail -5, onstat -u | tail-2, onstat> -F and onstat -R. None of the onconfig values have been changed since > the upgrade, and these values were setup by the previous DBA, so I don't > know why specific values were chosen. I know the last bufwaits ratio I > calculated was much higher than Art's recommended 7%. I appreciate any > input! > > Thanks, > Ty > -- > Ty O'Kelly > DBA > tokelly@maxor.invalid.com > 806-324-5521 > > onstat -c:> > # Root Dbspace Configuration > > ROOTNAME rootdbs # Root dbspace name> ROOTPATH /dev/informix.new/rootdbs > # Path for device containing root > dbspace > ROOTOFFSET 0 # Offset of root dbspace into device > (Kbytes) > ROOTSIZE 500000 # Size of root dbspace (Kbytes)> > # Disk Mirroring Configuration Parameters > > MIRROR 0 # Mirroring flag (Yes = 1, No = 0) > MIRRORPATH # Path for device containing mirrored> root > MIRROROFFSET 0 # Offset into mirrored device (Kbytes)> > # Physical Log Configuration > > PHYSDBS rootdbs # Location (dbspace) of physical log > PHYSFILE 50000 # Physical log file size (Kbytes)> > # Logical Log Configuration > > LOGFILES 4 # Number of logical log files > LOGSIZE 30000 # Logical log size (Kbytes)> > # Diagnostics > > MSGPATH /usr/informix/etc/online.primary > # System message log file path > CONSOLE /dev/console # System console message path > ALARMPROGRAM /usr/informix/etc/no_log.sh # Alarm program path> > # System Archive Tape Device > > #TAPEDEV /dev/rmt/0 # Tape device path > TAPEDEV maxdev:/dev/rmt/0 # Tape device path > TAPEBLK 62 # Tape block size (Kbytes) > TAPESIZE 12000000 # Maximum amount of data to put on tape > (Kbytes)> > # Log Archive Tape Device > > #LTAPEDEV /dev/rmt/0 # Log tape device path > LTAPEDEV maxdev:/dev/rmt/0 # Log tape device path > LTAPEBLK 62 # Log tape block size (Kbytes) > LTAPESIZE 2000000 # Max amount of data to put on log tape > (Kbytes)> > # Optical > > STAGEBLOB ,1 # INFORMIX-OnLine/Optical staging area > > # System Configuration > > SERVERNUM 118 # Unique id corresponding to a OnLine> instance > DBSERVERNAME primary # Name of default database server > DBSERVERALIASES net_primary # List of alternate dbservernames > 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 3 # Number of user (cpu) vps > SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps> to one > > NOAGE 0 # Process aging > AFF_SPROC 1 # Affinity start processor > AFF_NPROCS 3 # Affinity number of processors> > # Shared Memory Parameters > > LOCKS 100000 # Maximum number of locks > BUFFERS 80000 # Maximum number of shared buffers > NUMAIOVPS 1 # Number of IO vps > PHYSBUFF 128 # Physical log buffer size (Kbytes) > LOGBUFF 32 # Logical log buffer size (Kbytes)> LOGSMAX 40 # Maximum number of logical log files > CLEANERS 14 # Number of buffer cleaner processes > SHMBASE 0xa000000 # Shared memory base address > SHMVIRTSIZE 70000 # initial virtual shared memory segment> size > # SHMVIRTSIZE 8000 # initial virtual shared memory > segment size > SHMADD 25000 # Size of new shared memory segments > (Kbytes) > SHMTOTAL 0 # Total shared memory (Kbytes). > 0=>unlimited

Art, Thank you for your advice. A couple of follow-up questions: "Art S. Kagel" wrote: > A short term quick fix is to define the environment variable NOLRUPRIO in the > environment that starts the engine and bounce it to disable the buffer > priority code. The permanent solution is to recreate all of your indexes > using 7.31 (best to do that with PDQPRIORITY and the PSORT_ parameters set). We are planning on taking this database to 9.20.uc4 in the near future. Will this problem be compounded by another upgrade? Should we rebuild the indexes prior to the upgrade or do them afterward? Can the indexes be rebuilt in 7.31 with the NOLRUPRIO variable set? Thanks, Ty -- Ty O'Kelly DBA tokelly@maxor.invalid.com 806-324-5521

Ty O'Kelly wrote: > Art, > > Thank you for your advice. A couple of follow-up questions: > > "Art S. Kagel" wrote: > > > A short term quick fix is to define the environment variable NOLRUPRIO in the > > environment that starts the engine and bounce it to disable the buffer > > priority code. The permanent solution is to recreate all of your indexes > > using 7.31 (best to do that with PDQPRIORITY and the PSORT_ parameters set). > > We are planning on taking this database to 9.20.uc4 in the near future. Will > this problem be compounded by another upgrade? Should we rebuild the indexes > prior to the upgrade or do them afterward? Can the indexes be rebuilt in 7.31 > with the NOLRUPRIO variable set? Actually it gets worse in 9.20 which is based on the 7.30 code. IDS 7.31 added priority aging such that high priority buffers have their priority reduced over time but in 7.30 there was no aging so the effect of the 7.2x carryover bug is greater in 7.30 and 9.20 than in 7.31. Fix the indexes now and BTW go with 9.21 instead. Yes the indexes will be built correctly in 7.3x no matter what (except that the 7.30 version of oncheck has the same bug as 7.2x so you must be careful in 7.30 to NEVER let oncheck build any indexes only do so manually, 7.31 fixed that one so 7.31 oncheck is safe though slow as always). Art S. Kagel > > > Thanks, > Ty > -- > Ty O'Kelly > DBA > tokelly@maxor.invalid.com > 806-324-5521

In article <397DE9E1.E3D8921C@bloomberg.net>, blp@bloomberg.net says... >rewritten code. Run each with SET EXPLAIN to see the resulting join code >and >the query path to determine what indexes are missing. (This can also be >turned off >with an environment variable.) SET EXPLAIN could be useful in any case to find other weird optimizer behavior, even on queries without corellated subqueries. --William Harris william@carsinfo.com