Performance issue after 732 to 94fc6 upgrade

Hello, We finally upgraded from 7.32 to 9.40FC6. Some of our batch processes and most user OLTP are performing as well or better than the 732 version. However 3 of our large batch processes are now taking 30-40% longer to complete. For example a process that used to take 7 hrs is now taking 11.5 hrs. I am seeking some advice on what may be occurring. I have included our onconfig and an sqexplain for the process in question. One factor that seems strange in the sqexplain is the cost of the insert statement. I am unsure if that is just how sqexplain reports the data or if that is the issue. We are on hp-ux 11.11 with a 4 way 8 gig N class server. IDS 64 bit engine and 32 bit tools/network. The program in question is 4gl. Machine notes were followed and kernel params slightly modified. The machine does not appear to be taxed, not paging, cpu ok, etc. No excessive check points and onstat -p, g ioq, g iof, g iov are OK. We have run Art's dostats for the database in question and manually update stats for the system tables recommended by IBM. We did a dbexport and dbimport for the upgrade. We have rebuilt most of the indexes in question (but not all). PDQ is not enabled, no fragmentation scheme, did not specify detached indexes. (During the install we did have one major faux pas, we installed the 64 bit tools, 64 bit engine, 32 bit network. We realised afterwards that we grabbed the wrong tools cd (64 bit), and needed the 32 bit tools. Our application vendor and IDS reseller stated we could just re-install the 32 bit tools over the top w/o having to reinstall the engine and network.) I want to rule out IDS and server issues before address the business rule set up or the vendor's code. So any advice or critique is fine. Thanks in advance, Doug ROOTNAME rootdbs # Root dbspace nameROOTPATH /dbms/links/rootdbs9 # Path for device containing root dbspace ROOTOFFSET 0 # Offset of root dbspace into device (Kbytes) ROOTSIZE 30000 # Size of root dbspace (Kbytes) # Physical Log Configuration PHYSDBS plogdbs94 # Location (dbspace) of physical log PHYSFILE 127000 # Physical log file size (Kbytes) # Logical Log Configuration LOGFILES 101 # Number of logical log files LOGSIZE 2000 # Logical log size (Kbytes)TABLSPACE_STATS 0 # Maintain tblspace statistics # System Configuration SERVERNUM 1 # Unique id corresponding to a OnLine instance DBSERVERNAME online9 # Name of default database server DBSERVERALIASES test9 # List of alternate dbservernames NETTYPE ipcshm,1,100,CPU NETTYPE soctcp,2,100,NET DEADLOCK_TIMEOUT 120 # Max time to wait of lock in distributed env. RESIDENT 1 # Forced residency flag (Yes = 1, No = 0) MULTIPROCESSOR 1 # 0 for single-processor, 1 for multi-processor VPCLASS CPU,num=3,aff=1-3,noage VPCLASS AIO,num=2,aff=1-3 SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps to one # Shared Memory Parameters LOCKS 200000 # Maximum number of locks BUFFERS 900000 # Maximum number of shared buffers PHYSBUFF 64 # Physical log buffer size (Kbytes) LOGBUFF 64 # Logical log buffer size (Kbytes) CLEANERS 8 # Number of buffer cleaner processes SHMBASE 0x0L # Shared memory base address SHMVIRTSIZE 327680 SHMADD 32768 # Size of new shared memory segments (Kbytes) SHMTOTAL 0 # Total shared memory (Kbytes). 0=>unlimited CKPTINTVL 300 # Check point interval (in sec) LRUS 128 # Number of LRU queues LRU_MAX_DIRTY 10.000000 # LRU percent dirty begin cleaning limit LRU_MIN_DIRTY 5.000000 # LRU percent dirty end cleaning limit TXTIMEOUT 0x12c # Transaction timeout (in sec) STACKSIZE 64 # Stack size (Kbytes) # DYNAMIC_LOGS: DYNAMIC_LOGS 0 LTXHWM 40 LTXEHWM 50 # OFF_RECVRY_THREADS: OFF_RECVRY_THREADS 10 # Default number of offline worker threads ON_RECVRY_THREADS 1 # Default number of online worker threads # Backup/Restore variables BAR_ACT_LOG /dbms/informix9/log/bar_act.log BAR_DEBUG_LOG /dbms/informix9/log/informix/bar_dbug.log # ON-Bar Debug Log - not in /tmp please BAR_MAX_BACKUP 0 BAR_RETRY 1 BAR_NB_XPORT_COUNT 10 BAR_XFER_BUF_SIZE 31 RESTARTABLE_RESTORE on BAR_PROGRESS_FREQ 0 # Read Ahead Variables RA_PAGES 32 # Number of pages to attempt to read ahead RA_THRESHOLD 30 # Number of pages left before next group DBSPACETEMP tempdbs6:tempdbs7:tempdbs8:tempdbs9:tempdbs10 FILLFACTOR 90 # Fill factor for building indexes USEOSTIME 0 # 0: use internal time(fast), 1: get time from O # Parallel Database Queries (pdq) MAX_PDQPRIORITY 90 # Maximum allowed pdqpriority DS_MAX_QUERIES # Maximum number of decision support queries DS_TOTAL_MEMORY # Decision support memory (Kbytes) DS_MAX_SCANS 1048576 # Maximum number of decision support scans DATASKIP off # OPTCOMPIND OPTCOMPIND 1 # To hint the optimizer DIRECTIVES 1 # Optimizer DIRECTIVES ON (1/Default) or OFF (0) ONDBSPACEDOWN 2 # Dbspace down option: 0 = CONTINUE, 1 = ABORT, # HETERO_COMMIT (Gateway participation in distributed transactions) HETERO_COMMIT 0 SBSPACENAME # Default smartblob space name - this is where b SYSSBSPACENAME # Default smartblob space for use by the Informi BLOCKTIMEOUT 3600 # Default timeout for system blockSYSALARMPROGRAM /dbms/informix9/etc/evidence.sh # System Alarm program path # Optimization goal: -1 = ALL_ROWS(Default), 0 = FIRST_ROWS OPT_GOAL -1 ALLOW_NEWLINE 0 # embedded newlines(Yes = 1, No = 0 or anything START OF PROCESS QUERY: ------ delete from retwahst where empid = ? Estimated Cost: 7 Estimated # of Rows Returned: 36 1) bsidba.retwahst: INDEX PATH (1) Index Keys: empid (Serial, fragments: ALL) Lower Index Filter: bsidba.retwahst.empid = '101289 ' QUERY: ------ select count ( * ) from hr_retirewa , hr_pe_mstr where id = ? and hr_pe_id = ? and ( currbeg <= ? or extractbeg is null or extractbeg = " " ) and ( currend>= ? or extractend is null or extractend = " " ) and ( retirestat = "A" or retirestat = "O" or retirestat = "G" or retirestat = "F" ) Estimated Cost: 4 Estimated # of Rows Returned: 1 1) bsi.hr_pe_mstr: INDEX PATH (1) Index Keys: hr_pe_id (Key-Only) (Serial, fragments: ALL) Lower Index Filter: bsi.hr_pe_mstr.hr_pe_id = '101289 ' 2) bsidba.hr_retirewa: INDEX PATH Filters: ((((bsidba.hr_retirewa.currend >= 05/01/2006 OR bsidba.hr_retirewa.extractend IS NULL ) OR bsidba.hr_retirewa.extractend = ) AND (((bsidba.hr_retirewa.retirestat = 'A' OR bsidba.hr_retirewa.retirestat = 'O' ) OR bsidba.hr_retirewa.retirestat = 'G' ) OR bsidba.hr_retirewa.retirestat = 'F' ) ) AND ((bsidba.hr_retirewa.currbeg <= 05/31/2006 OR bsidba.hr_retirewa.extractbeg IS NULL ) OR bsidba.hr_retirewa.extractbeg = ) ) (1) Index Keys: id currbeg currend (Serial, fragments: ALL) Lower Index Filter: bsidba.hr_retirewa.id = '101289 ' NESTED LOOP JOIN QUERY: ------ select retirestat , currbeg , currend , start_per , reportbeg , extractend , currend , currsys , currplan , currrate , reportbeg , reportend , hr_pe_mstr .* from hr_retirewa , hr_pe_mstr where id = ? and hr_pe_id = ? and ( currbeg <= ? or extractbeg is null or extractbe

I review the explain and I find some sequential scan and a Dynamic Hash join. If the tables with the seq. scan are large and have index in the search field, you need run update statistics again. If all your query are based in nested loop joins, I recomed you change the onconfig parameter 'OPTCOMPIND' to 0. I had a performance problem when we migreted from 7.3 to 9.30, and it resolved changing this parameter. -----Mensaje original----- De: Doug Fossmeyer [mailto:DougF@SpokaneSchools.org] Enviado el: Viernes, 26 de Mayo de 2006 12:20 p.m. Para: ids@iiug.org Asunto: Performance issue after 732 to 94fc6 upgrade [6819] Hello, We finally upgraded from 7.32 to 9.40FC6. Some of our batch processes and most user OLTP are performing as well or better than the 732 version. However 3 of our large batch processes are now taking 30-40% longer to complete. For example a process that used to take 7 hrs is now taking 11.5 hrs. I am seeking some advice on what may be occurring. I have included our onconfig and an sqexplain for the process in question. One factor that seems strange in the sqexplain is the cost of the insert statement. I am unsure if that is just how sqexplain reports the data or if that is the issue. We are on hp-ux 11.11 with a 4 way 8 gig N class server. IDS 64 bit engine and 32 bit tools/network. The program in question is 4gl. Machine notes were followed and kernel params slightly modified. The machine does not appear to be taxed, not paging, cpu ok, etc. No excessive check points and onstat -p, g ioq, g iof, g iov are OK. We have run Art's dostats for the database in question and manually update stats for the system tables recommended by IBM. We did a dbexport and dbimportfor the upgrade. We have rebuilt most of the indexes in question (but not all). PDQ is not enabled, no fragmentation scheme, did not specify detached indexes. (During the install we did have one major faux pas, we installed the 64 bit tools, 64 bit engine, 32 bit network. We realised afterwards that we grabbed the wrong tools cd (64 bit), and needed the 32 bit tools. Our application vendor and IDS reseller stated we could just re-install the 32 bit tools over the top w/o having to reinstall the engine and network.) I want to rule out IDS and server issues before address the business rule set up or the vendor's code. So any advice or critique is fine. Thanks in advance, Doug ROOTNAME rootdbs # Root dbspace nameROOTPATH /dbms/links/rootdbs9 # Path for device containing root dbspace ROOTOFFSET 0 # Offset of root dbspace into device (Kbytes) ROOTSIZE 30000 # Size of root dbspace (Kbytes) # Physical Log Configuration PHYSDBS plogdbs94 # Location (dbspace) of physical log PHYSFILE 127000 # Physical log file size (Kbytes) # Logical Log Configuration LOGFILES 101 # Number of logical log files LOGSIZE 2000 # Logical log size (Kbytes)TABLSPACE_STATS 0 # Maintain tblspace statistics # System Configuration SERVERNUM 1 # Unique id corresponding to a OnLine instance DBSERVERNAME online9 # Name of default database server DBSERVERALIASES test9 # List of alternate dbservernames NETTYPE ipcshm,1,100,CPU NETTYPE soctcp,2,100,NET DEADLOCK_TIMEOUT 120 # Max time to wait of lock in distributed env. RESIDENT 1 # Forced residency flag (Yes = 1, No = 0) MULTIPROCESSOR 1 # 0 for single-processor, 1 for multi-processor VPCLASS CPU,num=3,aff=1-3,noage VPCLASS AIO,num=2,aff=1-3 SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps to one # Shared Memory Parameters LOCKS 200000 # Maximum number of locks BUFFERS 900000 # Maximum number of shared buffers PHYSBUFF 64 # Physical log buffer size (Kbytes) LOGBUFF 64 # Logical log buffer size (Kbytes) CLEANERS 8 # Number of buffer cleaner processes SHMBASE 0x0L # Shared memory base address SHMVIRTSIZE 327680 SHMADD 32768 # Size of new shared memory segments (Kbytes) SHMTOTAL 0 # Total shared memory (Kbytes). 0=>unlimited CKPTINTVL 300 # Check point interval (in sec) LRUS 128 # Number of LRU queues LRU_MAX_DIRTY 10.000000 # LRU percent dirty begin cleaning limit LRU_MIN_DIRTY 5.000000 # LRU percent dirty end cleaning limit TXTIMEOUT 0x12c # Transaction timeout (in sec) STACKSIZE 64 # Stack size (Kbytes) # DYNAMIC_LOGS: DYNAMIC_LOGS 0 LTXHWM 40 LTXEHWM 50 # OFF_RECVRY_THREADS: OFF_RECVRY_THREADS 10 # Default number of offline worker threads ON_RECVRY_THREADS 1 # Default number of online worker threads # Backup/Restore variables BAR_ACT_LOG /dbms/informix9/log/bar_act.log BAR_DEBUG_LOG /dbms/informix9/log/informix/bar_dbug.log # ON-Bar Debug Log - not in /tmp please BAR_MAX_BACKUP 0 BAR_RETRY 1 BAR_NB_XPORT_COUNT 10 BAR_XFER_BUF_SIZE 31 RESTARTABLE_RESTORE on BAR_PROGRESS_FREQ 0 # Read Ahead Variables RA_PAGES 32 # Number of pages to attempt to read ahead RA_THRESHOLD 30 # Number of pages left before next group DBSPACETEMP tempdbs6:tempdbs7:tempdbs8:tempdbs9:tempdbs10 FILLFACTOR 90 # Fill factor for building indexes USEOSTIME 0 # 0: use internal time(fast), 1: get time from O # Parallel Database Queries (pdq) MAX_PDQPRIORITY 90 # Maximum allowed pdqpriority DS_MAX_QUERIES # Maximum number of decision support queries DS_TOTAL_MEMORY # Decision support memory (Kbytes) DS_MAX_SCANS 1048576 # Maximum number of decision support scans DATASKIP off # OPTCOMPIND OPTCOMPIND 1 # To hint the optimizer DIRECTIVES 1 # Optimizer DIRECTIVES ON (1/Default) or OFF (0) ONDBSPACEDOWN 2 # Dbspace down option: 0 = CONTINUE, 1 = ABORT, # HETERO_COMMIT (Gateway participation in distributed transactions) HETERO_COMMIT 0 SBSPACENAME # Default smartblob space name - this is where b SYSSBSPACENAME # Default smartblob space for use by the Informi BLOCKTIMEOUT 3600 # Default timeout for system blockSYSALARMPROGRAM /dbms/informix9/etc/evidence.sh # System Alarm program path # Optimization goal: -1 = ALL_ROWS(Default), 0 = FIRST_ROWS OPT_GOAL -1 ALLOW_NEWLINE 0 # embedded newlines(Yes = 1, No = 0 or anything START OF PROCESS QUERY: ------ delete from retwahst where empid = ? Estimated Cost: 7 Estimated # of Rows Returned: 36 1) bsidba.retwahst: INDEX PATH (1) Index Keys: empid (Serial, fragments: ALL) Lower Index Filter: bsidba.retwahst.empid = '101289 ' QUERY: ------ select count ( * ) from hr_retirewa , hr_pe_mstr where id = ? and hr_pe_id =? and ( currbeg <= ? or extractbeg is null or extractbeg = " " ) and ( currend >= ? or extractend is null or extractend = " " ) and ( retirestat = "A" or retirestat = "O" or retirestat = "G" or retirestat = "F" ) Estimated Cost: 4 Estimated # of Rows Returned: 1 1) bsi.hr_pe_mstr: INDEX PATH (1) Index Keys: hr_pe_id (Key-Only) (Serial, fragments: ALL) Lower Index Filter: bsi.hr_pe_mstr.hr_pe_id = '101289 ' 2) bsidba.hr_retirewa: INDEX PATH Filters: ((((bsidba.hr_retirewa.currend >= 05/01/2006 OR bsidba.hr_retirewa.extractend IS NULL ) OR bsidba.hr_retirewa.extractend = ) AND (((bsidba.hr_

Hello, Not easy without knowning the structure of you tables or the number of rows. First thing I would look into is the sequential scans. But as the cost is very low, I assume the tables have only a couple of rows, so this should not make any difference. If you say the snippet repeats for each record you work on, I assume you are using no prepared statements (Cursors). This could be a great enhancement to you program, because Informix does not have to compile again and again the sql. In case the source is not accessible, this is a problem. In your config, I do not see any temp Dbspaces. These could be useful for sort operations. The huge cost on insert operations I have also seen often on big databases, it shows that there are indices to update (maybe triggers which perform other work ?). You could try using more CPU VPs than active cpus (Informix will perform even better in most cases). The best information would be a measuring trace of your program (at which operation most of the time is spent really). You could try to get this info also by executing the same commands once with a small programm or a script (maybe only for one record). Is the database transactional ? Maybe time is taken at commit time or in checkpoints (how long are these when the operation takes place ?). Sorry, no more hints I can think of now with the information given. Marcus -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Doug Fossmeyer Sent: Friday, May 26, 2006 7:20 PM To: ids@iiug.org Subject: Performance issue after 732 to 94fc6 upgrade [6819] Hello, We finally upgraded from 7.32 to 9.40FC6. Some of our batch processes and most user OLTP are performing as well or better than the 732 version. However 3 of our large batch processes are now taking 30-40% longer to complete. For example a process that used to take 7 hrs is now taking 11.5 hrs. I am seeking some advice on what may be occurring. I have included our onconfig and an sqexplain for the process in question. One factor that seems strange in the sqexplain is the cost of the insert statement. I am unsure if that is just how sqexplain reports the data or if that is the issue. We are on hp-ux 11.11 with a 4 way 8 gig N class server. IDS 64 bit engine and 32 bit tools/network. The program in question is 4gl. Machine notes were followed and kernel params slightly modified. The machine does not appear to be taxed, not paging, cpu ok, etc. No excessive check points and onstat -p, g ioq, g iof, g iov are OK. We have run Art's dostats for the database in question and manually update stats for the system tables recommended by IBM. We did a dbexport and dbimport for the upgrade. We have rebuilt most of the indexes in question (but not all). PDQ is not enabled, no fragmentation scheme, did not specify detached indexes. (During the install we did have one major faux pas, we installed the 64 bit tools, 64 bit engine, 32 bit network. We realised afterwards that we grabbed the wrong tools cd (64 bit), and needed the 32 bit tools. Our application vendor and IDS reseller stated we could just re-install the 32 bit tools over the top w/o having to reinstall the engine and network.) I want to rule out IDS and server issues before address the business rule set up or the vendor's code. So any advice or critique is fine. Thanks in advance, Doug ROOTNAME rootdbs # Root dbspace nameROOTPATH /dbms/links/rootdbs9 # Path for device containing root dbspace ROOTOFFSET 0 # Offset of root dbspace into device (Kbytes) ROOTSIZE 30000 #Size of root dbspace (Kbytes) # Physical Log Configuration PHYSDBS plogdbs94 # Location (dbspace) of physical log PHYSFILE 127000 # Physical log file size (Kbytes) # Logical Log Configuration LOGFILES 101 # Number of logical log files LOGSIZE 2000 # Logical log size(Kbytes) TABLSPACE_STATS 0 # Maintain tblspace statistics # System Configuration SERVERNUM 1 # Unique id corresponding to a OnLine instance DBSERVERNAMEonline9 # Name of default database server DBSERVERALIASES test9 # List of alternate dbservernames NETTYPE ipcshm,1,100,CPU NETTYPE soctcp,2,100,NET DEADLOCK_TIMEOUT 120 # Max time to wait of lock in distributed env. RESIDENT 1 # Forced residency flag (Yes = 1, No = 0) MULTIPROCESSOR 1 # 0 for single-processor, 1 for multi-processor VPCLASS CPU,num=3,aff=1-3,noage VPCLASS AIO,num=2,aff=1-3 SINGLE_CPU_VP 0 # If non-zero, limit number of cpuvps to one # Shared Memory Parameters LOCKS 200000 # Maximum number of locks BUFFERS 900000 # Maximum number of shared buffers PHYSBUFF 64 # Physical logbuffer size (Kbytes) LOGBUFF 64 # Logical log buffer size (Kbytes) CLEANERS 8 # Number of buffer cleaner processes SHMBASE 0x0L # Shared memory base address SHMVIRTSIZE 327680 SHMADD 32768 # Size of new shared memory segments (Kbytes) SHMTOTAL 0 # Total shared memory (Kbytes). 0=>unlimited CKPTINTVL 300 # Check point interval (in sec) LRUS 128 # Number of LRU queues LRU_MAX_DIRTY 10.000000 # LRU percent dirty begin cleaning limit LRU_MIN_DIRTY 5.000000 # LRU percent dirty end cleaning limit TXTIMEOUT0x12c # Transaction timeout (in sec) STACKSIZE 64 # Stack size (Kbytes) # DYNAMIC_LOGS: DYNAMIC_LOGS 0 LTXHWM 40 LTXEHWM 50 # OFF_RECVRY_THREADS: OFF_RECVRY_THREADS 10 # Default number of offline worker threads ON_RECVRY_THREADS 1 # Default number of online worker threads # Backup/Restore variables BAR_ACT_LOG /dbms/informix9/log/bar_act.log BAR_DEBUG_LOG /dbms/informix9/log/informix/bar_dbug.log # ON-Bar Debug Log - not in /tmp please BAR_MAX_BACKUP 0 BAR_RETRY 1 BAR_NB_XPORT_COUNT 10 BAR_XFER_BUF_SIZE 31 RESTARTABLE_RESTORE on BAR_PROGRESS_FREQ 0 # Read Ahead Variables RA_PAGES 32 # Number of pages to attempt to read ahead RA_THRESHOLD 30 #Number of pages left before next group DBSPACETEMP tempdbs6:tempdbs7:tempdbs8:tempdbs9:tempdbs10 FILLFACTOR 90 # Fill factor for building indexes USEOSTIME 0 # 0: use internal time(fast), 1: get time from O # Parallel Database Queries (pdq) MAX_PDQPRIORITY 90 # Maximum allowed pdqpriority DS_MAX_QUERIES # Maximumnumber of decision support queries DS_TOTAL_MEMORY # Decision support memory (Kbytes) DS_MAX_SCANS 1048576 # Maximum number of decision support scans DATASKIP off # OPTCOMPIND OPTCOMPIND 1 # To hint the optimizer DIRECTIVES 1 # Optimizer DIRECTIVES ON (1/Default) or OFF (0) ONDBSPACEDOWN 2 # Dbspace down option: 0 = CONTINUE, 1 = ABORT, # HETERO_COMMIT (Gateway participation in distributed transactions) HETERO_COMMIT 0 SBSPACENAME # Default smartblob space name - this is where b SYSSBSPACENAME # Default smartblob space for use by the Informi BLOCKTIMEOUT 3600 # Default timeout for system block SYSALARMPROGRAM /dbms/informix9/etc/evidence.sh # System Alarm program path # Optimization goal: -1 = ALL_ROWS(Default), 0 = FIRST_ROWS OPT_GOAL -1 ALLOW_NEWLINE 0 # embedded newlines(Yes = 1, No = 0 or anything START OF PROCESS QUERY: ------ delete from retwahst where empid = ? Estimated Cost: 7 Estimated # of Rows Returned: 36 1) bsidba.retwahst: INDEX PATH (1) Index Keys: empid (Serial, fragments: A

Post some onstat output for us from shortly after the slow app completes: onstat -p onstat -d onstat -D onstat -P onstat -c onstat -g glo onstat -g iov onstat -g iof onstat -g rea (during normal load, preferably during the slow app's run) onstat -g dsc onstat -g dic onstat -g prc onstat -g cac Also post the time since these stats were zero'd (startup or onstat -z run). We'll look it all over and let you know if we see anything. FYI I do find the EXPLAIN output for the insert unusual. Also post the output of dbschema/myschema -d <db> -t retwahst -ss and dbschema -d <db> -hd retwahst for us. The only thing I notice in the ONCONFIG is that if you are using shared memory connections more than occassionally I would want to see NETTYPE shm,3,100,CPU so you have poll threads in all 3 CPU VPs rather than 1. Art S. Kagel ----- Original Message ----- From: Doug Fossmeyer <ids@iiug.org> At: 5/26 13:23:21 Hello, We finally upgraded from 7.32 to 9.40FC6. Some of our batch processes and most user OLTP are performing as well or better than the 732 version. However 3 of our large batch processes are now taking 30-40% longer to complete. For example a process that used to take 7 hrs is now taking 11.5 hrs. I am seeking some advice on what may be occurring. I have included our onconfig and an sqexplain for the process in question. One factor that seems strange in the sqexplain is the cost of the insert statement. I am unsure if that is just how sqexplain reports the data or if that is the issue. We are on hp-ux 11.11 with a 4 way 8 gig N class server. IDS 64 bit engine and 32 bit tools/network. The program in question is 4gl. Machine notes were followed and kernel params slightly modified. The machine does not appear to be taxed, not paging, cpu ok, etc. No excessive check points and onstat -p, g ioq, g iof, g iov are OK. We have run Art's dostats for the database in question and manually update stats for the system tables recommended by IBM. We did a dbexport and dbimport for the upgrade. We have rebuilt most of the indexes in question (but not all). PDQ is not enabled, no fragmentation scheme, did not specify detached indexes. (During the install we did have one major faux pas, we installed the 64 bit tools, 64 bit engine, 32 bit network. We realised afterwards that we grabbed the wrong tools cd (64 bit), and needed the 32 bit tools. Our application vendor and IDS reseller stated we could just re-install the 32 bit tools over the top w/o having to reinstall the engine and network.) I want to rule out IDS and server issues before address the business rule set up or the vendor's code. So any advice or critique is fine. Thanks in advance, Doug <SNIP>

Doug- We discovered when we made a similar transition that temporary tables built and indexed in a program will not use their indexes unless update statistics is run against them. The 9.x engine apparently has adifference in the optimizer which caused this. --EEM > -----Original Message----- > From: Doug Fossmeyer [mailto:DougF@SpokaneSchools.org] > Sent: Friday, May 26, 2006 12:20 PM > To: ids@iiug.org > Subject: Performance issue after 732 to 94fc6 upgrade [6819] > > > Hello, > > We finally upgraded from 7.32 to 9.40FC6. Some of our batch processes and > most > user OLTP are performing as well or better than the 732 version. However 3 > of > our large batch processes are now taking 30-40% longer to complete. For > example a process that used to take 7 hrs is now taking 11.5 hrs. I am > seeking > some advice on what may be occurring. I have included our onconfig and an > sqexplain for the process in question. One factor that seems strange in > the > sqexplain is the cost of the insert statement. I am unsure if that is just > how > sqexplain reports the data or if that is the issue. > We are on hp-ux 11.11 with a 4 way 8 gig N class server. IDS 64 bit engine > and > 32 bit tools/network. The program in question is 4gl. Machine notes were > followed and kernel params slightly modified. The machine does not appear > to > be taxed, not paging, cpu ok, etc. No excessive check points and onstat - > p, g > ioq, g iof, g iov are OK. > > We have run Art's dostats for the database in question and manually update > stats for the system tables recommended by IBM. We did a dbexport and > dbimport> for the upgrade. We have rebuilt most of the indexes in question (but not > all). PDQ is not enabled, no fragmentation scheme, did not specify > detached > indexes. (During the install we did have one major faux pas, we installed > the > 64 bit tools, 64 bit engine, 32 bit network. We realised afterwards that > we > grabbed the wrong tools cd (64 bit), and needed the 32 bit tools. Our > application vendor and IDS reseller stated we could just re-install the 32 > bit > tools over the top w/o having to reinstall the engine and network.) > > I want to rule out IDS and server issues before address the business rule > set > up or the vendor's code. So any advice or critique is fine. > > Thanks in advance, > Doug > > ROOTNAME rootdbs # Root dbspace name> ROOTPATH /dbms/links/rootdbs9 # Path for device containing root dbspace > ROOTOFFSET 0 # Offset of root dbspace into device (Kbytes) > ROOTSIZE 30000 # Size of root dbspace (Kbytes)> > # Physical Log Configuration > PHYSDBS plogdbs94 # Location (dbspace) of physical log > PHYSFILE 127000 # Physical log file size (Kbytes)> > # Logical Log Configuration > LOGFILES 101 # Number of logical log files > LOGSIZE 2000 # Logical log size (Kbytes)> TABLSPACE_STATS 0 # Maintain tblspace statistics > > # System Configuration > SERVERNUM 1 # Unique id corresponding to a OnLine instance > DBSERVERNAME online9 # Name of default database server > DBSERVERALIASES test9 # List of alternate dbservernames > NETTYPE ipcshm,1,100,CPU > NETTYPE soctcp,2,100,NET > DEADLOCK_TIMEOUT 120 # Max time to wait of lock in distributed env. > RESIDENT 1 # Forced residency flag (Yes = 1, No = 0) > MULTIPROCESSOR 1 # 0 for single-processor, 1 for multi-processor > VPCLASS CPU,num=3,aff=1-3,noage > VPCLASS AIO,num=2,aff=1-3 > SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps to one> > # Shared Memory Parameters > LOCKS 200000 # Maximum number of locks > BUFFERS 900000 # Maximum number of shared buffers > PHYSBUFF 64 # Physical log buffer size (Kbytes) > LOGBUFF 64 # Logical log buffer size (Kbytes) > CLEANERS 8 # Number of buffer cleaner processes > SHMBASE 0x0L # Shared memory base address > SHMVIRTSIZE 327680 > SHMADD 32768 # Size of new shared memory segments (Kbytes) > SHMTOTAL 0 # Total shared memory (Kbytes). 0=>unlimited > CKPTINTVL 300 # Check point interval (in sec) > LRUS 128 # Number of LRU queues > LRU_MAX_DIRTY 10.000000 # LRU percent dirty begin cleaning limit > LRU_MIN_DIRTY 5.000000 # LRU percent dirty end cleaning limit > TXTIMEOUT 0x12c # Transaction timeout (in sec) > STACKSIZE 64 # Stack size (Kbytes)> > # DYNAMIC_LOGS: > DYNAMIC_LOGS 0 > LTXHWM 40 > LTXEHWM 50> > # OFF_RECVRY_THREADS: > > OFF_RECVRY_THREADS 10 # Default number of offline worker threads > ON_RECVRY_THREADS 1 # Default number of online worker threads> > # Backup/Restore variables > BAR_ACT_LOG /dbms/informix9/log/bar_act.log > BAR_DEBUG_LOG /dbms/informix9/log/informix/bar_dbug.log > > # ON-Bar Debug Log - not in /tmp please > BAR_MAX_BACKUP 0 > BAR_RETRY 1 > BAR_NB_XPORT_COUNT 10 > BAR_XFER_BUF_SIZE 31 > RESTARTABLE_RESTORE on > BAR_PROGRESS_FREQ 0> > # Read Ahead Variables > RA_PAGES 32 # Number of pages to attempt to read ahead > RA_THRESHOLD 30 # Number of pages left before next group> > DBSPACETEMP tempdbs6:tempdbs7:tempdbs8:tempdbs9:tempdbs10 > > FILLFACTOR 90 # Fill factor for building indexes > > USEOSTIME 0 # 0: use internal time(fast), 1: get time from O> > # Parallel Database Queries (pdq) > MAX_PDQPRIORITY 90 # Maximum allowed pdqpriority > DS_MAX_QUERIES # Maximum number of decision support queries > DS_TOTAL_MEMORY # Decision support memory (Kbytes) > DS_MAX_SCANS 1048576 # Maximum number of decision support scans > DATASKIP off> > # OPTCOMPIND > OPTCOMPIND 1 # To hint the optimizer > DIRECTIVES 1 # Optimizer DIRECTIVES ON (1/Default) or OFF (0) > ONDBSPACEDOWN 2 # Dbspace down option: 0 = CONTINUE, 1 = ABORT,> > # HETERO_COMMIT (Gateway participation in distributed transactions) > HETERO_COMMIT 0 > SBSPACENAME # Default smartblob space name - this is where b > > SYSSBSPACENAME # Default smartblob space for use by the Informi > > BLOCKTIMEOUT 3600 # Default timeout for system block> SYSALARMPROGRAM /dbms/informix9/etc/evidence.sh # System Alarm program > path > > # Optimization goal: -1 = ALL_ROWS(Default), 0 = FIRST_ROWS > OPT_GOAL -1 > ALLOW_NEWLINE 0 # embedded newlines(Yes = 1, No = 0 or anything> > START OF PROCESS > QUERY: > ------ > delete from retwahst where empid = ?> > Estimated Cost: 7 > Estimated # of Rows Returned: 36 > > 1) bsidba.retwahst: INDEX PATH > > (1) Index Keys: empid (Serial, fragments: ALL) > > Lower Index Filter: bsidba.retwahst.empid = '101289 ' > > QUERY: > ------ > select count ( * ) from hr_retirewa , hr_pe_mstr where id = ? andhr_pe_id > = ? > and ( currbeg <= ? or extractbeg is null or extractbeg = " " ) and ( > currend > >= ? or extractend is null or extractend = " " ) and ( retirestat = "A" or > retirestat = "O" or retirestat = "G" or retirestat = "F" ) > > Estimated Cost: 4 > Estimated # of Rows Returned: 1 > > 1) bsi.hr_pe_mstr: INDEX PATH > > (1) Index Keys: hr_pe_id (Key-Only) (Serial, fragments: ALL)@@NL

Call tech support. I think there is an undocumented environment variable to correct this behavior. Ask about bugs 166507 and 151523. "Everett Mills" <eemills@nationalbeef.com> Sent by: ids-bounces@iiug.org 05/26/2006 03:23 PM Please respond to ids@iiug.org To ids@iiug.org cc Subject RE: Performance issue after 732 to 94fc6 upgrade [6824] Doug- We discovered when we made a similar transition that temporary tables built and indexed in a program will not use their indexes unless update statistics is run against them. The 9.x engine apparently has adifference in the optimizer which caused this. --EEM > -----Original Message----- > From: Doug Fossmeyer [mailto:DougF@SpokaneSchools.org] > Sent: Friday, May 26, 2006 12:20 PM > To: ids@iiug.org > Subject: Performance issue after 732 to 94fc6 upgrade [6819] > > > Hello, > > We finally upgraded from 7.32 to 9.40FC6. Some of our batch processes and > most > user OLTP are performing as well or better than the 732 version. However 3 > of > our large batch processes are now taking 30-40% longer to complete. For > example a process that used to take 7 hrs is now taking 11.5 hrs. I am > seeking > some advice on what may be occurring. I have included our onconfig and an > sqexplain for the process in question. One factor that seems strange in > the > sqexplain is the cost of the insert statement. I am unsure if that is just > how > sqexplain reports the data or if that is the issue. > We are on hp-ux 11.11 with a 4 way 8 gig N class server. IDS 64 bit engine > and > 32 bit tools/network. The program in question is 4gl. Machine notes were > followed and kernel params slightly modified. The machine does not appear > to > be taxed, not paging, cpu ok, etc. No excessive check points and onstat - > p, g > ioq, g iof, g iov are OK. > > We have run Art's dostats for the database in question and manually update > stats for the system tables recommended by IBM. We did a dbexport and > dbimport> for the upgrade. We have rebuilt most of the indexes in question (but not > all). PDQ is not enabled, no fragmentation scheme, did not specify > detached > indexes. (During the install we did have one major faux pas, we installed > the > 64 bit tools, 64 bit engine, 32 bit network. We realised afterwards that > we > grabbed the wrong tools cd (64 bit), and needed the 32 bit tools. Our > application vendor and IDS reseller stated we could just re-install the 32 > bit > tools over the top w/o having to reinstall the engine and network.) > > I want to rule out IDS and server issues before address the business rule > set > up or the vendor's code. So any advice or critique is fine. > > Thanks in advance, > Doug > > ROOTNAME rootdbs # Root dbspace name> ROOTPATH /dbms/links/rootdbs9 # Path for device containing root dbspace > ROOTOFFSET 0 # Offset of root dbspace into device (Kbytes) > ROOTSIZE 30000 # Size of root dbspace (Kbytes)> > # Physical Log Configuration > PHYSDBS plogdbs94 # Location (dbspace) of physical log > PHYSFILE 127000 # Physical log file size (Kbytes)> > # Logical Log Configuration > LOGFILES 101 # Number of logical log files > LOGSIZE 2000 # Logical log size (Kbytes)> TABLSPACE_STATS 0 # Maintain tblspace statistics > > # System Configuration > SERVERNUM 1 # Unique id corresponding to a OnLine instance > DBSERVERNAME online9 # Name of default database server > DBSERVERALIASES test9 # List of alternate dbservernames > NETTYPE ipcshm,1,100,CPU > NETTYPE soctcp,2,100,NET > DEADLOCK_TIMEOUT 120 # Max time to wait of lock in distributed env. > RESIDENT 1 # Forced residency flag (Yes = 1, No = 0) > MULTIPROCESSOR 1 # 0 for single-processor, 1 for multi-processor > VPCLASS CPU,num=3,aff=1-3,noage > VPCLASS AIO,num=2,aff=1-3 > SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps to one> > # Shared Memory Parameters > LOCKS 200000 # Maximum number of locks > BUFFERS 900000 # Maximum number of shared buffers > PHYSBUFF 64 # Physical log buffer size (Kbytes) > LOGBUFF 64 # Logical log buffer size (Kbytes) > CLEANERS 8 # Number of buffer cleaner processes > SHMBASE 0x0L # Shared memory base address > SHMVIRTSIZE 327680 > SHMADD 32768 # Size of new shared memory segments (Kbytes) > SHMTOTAL 0 # Total shared memory (Kbytes). 0=>unlimited > CKPTINTVL 300 # Check point interval (in sec) > LRUS 128 # Number of LRU queues > LRU_MAX_DIRTY 10.000000 # LRU percent dirty begin cleaning limit > LRU_MIN_DIRTY 5.000000 # LRU percent dirty end cleaning limit > TXTIMEOUT 0x12c # Transaction timeout (in sec) > STACKSIZE 64 # Stack size (Kbytes)> > # DYNAMIC_LOGS: > DYNAMIC_LOGS 0 > LTXHWM 40 > LTXEHWM 50> > # OFF_RECVRY_THREADS: > > OFF_RECVRY_THREADS 10 # Default number of offline worker threads > ON_RECVRY_THREADS 1 # Default number of online worker threads> > # Backup/Restore variables > BAR_ACT_LOG /dbms/informix9/log/bar_act.log > BAR_DEBUG_LOG /dbms/informix9/log/informix/bar_dbug.log > > # ON-Bar Debug Log - not in /tmp please > BAR_MAX_BACKUP 0 > BAR_RETRY 1 > BAR_NB_XPORT_COUNT 10 > BAR_XFER_BUF_SIZE 31 > RESTARTABLE_RESTORE on > BAR_PROGRESS_FREQ 0> > # Read Ahead Variables > RA_PAGES 32 # Number of pages to attempt to read ahead > RA_THRESHOLD 30 # Number of pages left before next group> > DBSPACETEMP tempdbs6:tempdbs7:tempdbs8:tempdbs9:tempdbs10 > > FILLFACTOR 90 # Fill factor for building indexes > > USEOSTIME 0 # 0: use internal time(fast), 1: get time from O> > # Parallel Database Queries (pdq) > MAX_PDQPRIORITY 90 # Maximum allowed pdqpriority > DS_MAX_QUERIES # Maximum number of decision support queries > DS_TOTAL_MEMORY # Decision support memory (Kbytes) > DS_MAX_SCANS 1048576 # Maximum number of decision support scans > DATASKIP off> > # OPTCOMPIND > OPTCOMPIND 1 # To hint the optimizer > DIRECTIVES 1 # Optimizer DIRECTIVES ON (1/Default) or OFF (0) > ONDBSPACEDOWN 2 # Dbspace down option: 0 = CONTINUE, 1 = ABORT,> > # HETERO_COMMIT (Gateway participation in distributed transactions) > HETERO_COMMIT 0 > SBSPACENAME # Default smartblob space name - this is where b > > SYSSBSPACENAME # Default smartblob space for use by the Informi > > BLOCKTIMEOUT 3600 # Default timeout for system block> SYSALARMPROGRAM /dbms/informix9/etc/evidence.sh # System Alarm program > path > > # Optimization goal: -1 = ALL_ROWS(Default), 0 = FIRST_ROWS > OPT_GOAL -1 > ALLOW_NEWLINE 0 # embedded newlines(Yes = 1, No = 0 or anything> > START OF PROCESS > QUERY: > ------ > delete from retwahst where empid = ?> > Estimated Cost: 7 > Estimated # of Rows Returned: 36 > > 1) bsidba.retwahst: INDEX PATH > > (1) Index Keys: empid (Serial, fragments: ALL) > > Lower Index Filter: bsidba.retwahst.empid = '101289 ' > > QUERY: > ------ > select count ( * ) from hr_retirewa , hr_pe_mstr where id = ? andhr_pe_id > = ? > and ( currbeg <= ? or extractbeg is null or extractbeg =

Sorry, the previous posting of with the onstat output file did not have the sequence of onstat commands. They were in order: informix211 /tmp$ onstat -g cac >> retwa.stats.out informix212 /tmp$ onstat -p > retwa.statsdone.out informix213 /tmp$ onstat -d >> retwa.statsdone.out informix214 /tmp$ onstat -D >> retwa.statsdone.out informix215 /tmp$ onstat -P >> retwa.statsdone.out informix216 /tmp$ onstat -c >> retwa.statsdone.out informix217 /tmp$ onstat -g glo >> retwa.statsdone.out informix218 /tmp$ onstat -g iov >> retwa.statsdone.out informix219 /tmp$ onstat -g iov >> retwa.statsdone.out informix220 /tmp$ onstat -g iof >> retwa.statsdone.out informix221 /tmp$ onstat -g rea >> retwa.statsdone.out informix222 /tmp$ onstat -g dsc >> retwa.statsdone.out informix223 /tmp$ onstat -g dic >> retwa.statsdone.out informix224 /tmp$ onstat -g prc >> retwa.statsdone.out informix225 /tmp$ onstat -g cac >> retwa.statsdone.out

Hello, I posted a request for assistance respect to an upgrade from IDS 73 to IDS 94FC2. It was suggested by Art Kagel to post various onstats just after a run of one of the offending processes. I zero'd out the statistics just before running the process. Nothing else is running at the same time. Our issue is that after the upgrade most OLTP and small batch processes are running fine, the processes with large datasets have increased by 35-45% in elapsed time. HP-UX 11.11, IDS 94FC2, CSDK 2.81, C4GL 7.32HC2, ESQL-C 9.53HC2, 4 cpu 8 gig memory; dbexport, install in TEN steps, dbimport, manually update stats of system tables, update stats using dostats. Any help or further ideas would be appreciated. (I have not tried to turn PDQ on as suggested by Mark Gentry, yet). Thanks in advance, Doug

Does the IDS list serv stripping attachments? I attached a file with the details.... >>> dougf@SpokaneSchools.org 06/12/2006 11:55 AM >>> Hello, I posted a request for assistance respect to an upgrade from IDS 73 to IDS 94FC2. It was suggested by Art Kagel to post various onstats just after a run of one of the offending processes. I zero'd out the statistics just before running the process. Nothing else is running at the same time. Our issue is that after the upgrade most OLTP and small batch processes are running fine, the processes with large datasets have increased by 35-45% in elapsed time. HP-UX 11.11, IDS 94FC2, CSDK 2.81, C4GL 7.32HC2, ESQL-C 9.53HC2, 4 cpu 8 gig memory; dbexport, install in TEN steps, dbimport, manually update stats of system tables, update stats using dostats. Any help or further ideas would be appreciated. (I have not tried to turn PDQ on as suggested by Mark Gentry, yet). Thanks in advance, Doug ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Your checkpoint interval may be an issue. If its not relatively short (10 min or less), then you may be having problems with the buffer pool. Turns out that the buffer pool set of high priority pages is only re-evaluated every checkpoint. If that's not frequent enough, the frequently used pages may not be updated often enough, and that means those large queries may be only using about 25% of the buffer pool. Its not an intuitive connection. Cheers, "Doug Fossmeyer" <dougf@SpokaneSchools.org> Sent by: ids-bounces@iiug.org 06/12/2006 02:55 PM Please respond to ids@iiug.org To ids@iiug.org cc Subject Re: Performance issue after 732 to 94fc6 upgrade [6926] Hello, I posted a request for assistance respect to an upgrade from IDS 73 to IDS 94FC2. It was suggested by Art Kagel to post various onstats just after a run of one of the offending processes. I zero'd out the statistics just before running the process. Nothing else is running at the same time. Our issue is that after the upgrade most OLTP and small batch processes are running fine, the processes with large datasets have increased by 35-45% in elapsed time. HP-UX 11.11, IDS 94FC2, CSDK 2.81, C4GL 7.32HC2, ESQL-C 9.53HC2, 4 cpu 8 gig memory; dbexport, install in TEN steps, dbimport, manually update stats of system tables, update stats using dostats. Any help or further ideas would be appreciated. (I have not tried to turn PDQ on as suggested by Mark Gentry, yet). Thanks in advance, Doug ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Attachments don't usually get through the listserv. Art ----- Original Message ----- From: Doug Fossmeyer <ids@iiug.org> At: 6/12 15:05:55 Does the IDS list serv stripping attachments? I attached a file with the details.... >>> dougf@SpokaneSchools.org 06/12/2006 11:55 AM >>> Hello, I posted a request for assistance respect to an upgrade from IDS 73 to IDS 94FC2. It was suggested by Art Kagel to post various onstats just after a run of one of the offending processes. I zero'd out the statistics just before running the process. Nothing else is running at the same time. Our issue is that after the upgrade most OLTP and small batch processes are running fine, the processes with large datasets have increased by 35-45% in elapsed time. HP-UX 11.11, IDS 94FC2, CSDK 2.81, C4GL 7.32HC2, ESQL-C 9.53HC2, 4 cpu 8 gig memory; dbexport, install in TEN steps, dbimport, manually update stats of system tables, update stats using dostats. Any help or further ideas would be appreciated. (I have not tried to turn PDQ on as suggested by Mark Gentry, yet). Thanks in advance, Doug ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

This is embarrassing, our email server (groupwise is configured to limit email to 30 k. I am having to post my issue to the list serve with that limitation, meaning that to post the onstat and dbschema requires 3, yes 3 emails. I hope someone can evaluate the content and provide some guidance. Aside from upgrading to FC7 we are at a loss as to why the large batch processes are taking so long. Recap: I zero'd out the statistics just before running the process. Nothing else is running at the same time. Our issue is that after the upgrade most OLTP and small batch processes are running fine, the processes with large datasets have increased by 35-45% in elapsed time. HP-UX 11.11, IDS 94FC2, CSDK 2.81, C4GL 7.32HC2, ESQL-C 9.53HC2, 4 cpu 8 gig memory; dbexport, install seq Tools, Engine, Network steps, dbimport, manually update stats of system tables, update stats using dostats. Thanks in advance, Doug Part 1 onstat -p IBM Informix Dynamic Server Version 9.40.FC6 -- On-Line -- Up 2 days 19:51:54 -- 2325280 Kbytes Profile dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached 1692924 5680780 2148702238 99.92 98673 478066 9326835 98.94 isamtot open start read write rewrite delete commit rollbk 1403470631 5711631 27131297 1288458477 714047 182811 21956 192676 0 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 25339.64 948.51 217 1608 bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans 38861 0 1361203201 0 0 77 374795 1127856 ixda-RA idx-RA da-RA RA-pgsused lchwaits 483892 201 25013 509073 4265 onstat -d IBM Informix Dynamic Server Version 9.40.FC6 -- On-Line -- Up 2 days 19:52:11 -- 2325280 Kbytes Dbspaces address number flags fchunk nchunks flags owner name c00000008e3fbe60 1 0x20001 1 1 N informix rootdbs c00000008f581ce8 2 0x1 2 1 N informix syscat94 c00000008f581e68 3 0x1 3 10 N informix ifasnrdbs941 c00000008f584028 4 0x20001 13 11 N informix ifashrpy c00000008f5841a8 5 0x20001 23 1 N informix llogdbs94 c00000008f584328 6 0x2001 24 1 N T informix tempdbs6 c00000008f5844a8 7 0x2001 25 1 N T informix tempdbs7 c00000008f584628 8 0x2001 26 1 N T informix tempdbs8 c00000008f5847a8 9 0x2001 27 1 N T informix tempdbs9 c00000008f584928 10 0x20001 28 1 N informix tempdbs10 c00000008f584aa8 11 0x20001 29 10 N informix ifasdevdbs941 c00000008f584c28 12 0x20001 40 1 N informix plogdbs94 c00000008f584da8 13 0x20001 41 1 N informix ifasdbs94temp c00000008f586028 14 0x20001 42 1 N informix ifasdev2dbspyt c00000008f5861a8 15 0x20001 43 2 N informix ifasdev2dbspyt2 15 active, 2047 maximum Chunks address chunk/dbs offset size free bpages flags pathname c00000008e3fc028 1 1 0 15000 2826 PO-- /dbms/links/rootdbs9 c00000008f57d710 2 2 0 1023744 966451 PO-- /dbms/links/syscatdbs94 c00000008f57d8a8 3 3 0 1023744 3513 PO-- /dbms/links/ifasnrdbs941 c00000008f57da40 4 3 256 1023744 3 PO-- /dbms/links/ifasnrdbs942 c00000008f57dbd8 5 3 256 1023744 2029 PO-- /dbms/links/ifasnrdbs943 c00000008f57dd70 6 3 256 1023744 27788 PO-- /dbms/links/ifasnrdbs944 c00000008f57e028 7 3 256 1023744 53819 PO-- /dbms/links/ifasnrdbs945 c00000008f57e1c0 8 3 256 1023744 17378 PO-- /dbms/links/ifasnrdbs946 c00000008f57e358 9 3 256 1023744 235004 PO-- /dbms/links/ifasnrdbs947 c00000008f57e4f0 10 3 256 1023744 1023441 PO-- /dbms/links/ifasnrdbs948 c00000008f57e688 11 3 256 1023744 1023241 PO-- /dbms/links/ifasnrdbs949 c00000008f57e820 12 3 256 1023744 1023741 PO-- /dbms/links/ifasnrdbs9410 c00000008f57e9b8 13 4 0 1023744 8 PO-- /dbms/links/ifashrpy1 c00000008f57eb50 14 4 256 1023744 0 PO-- /dbms/links/ifashrpy2 c00000008f57ece8 15 4 256 1023744 89 PO-- /dbms/links/ifashrpy3 c00000008f57ee80 16 4 256 1023744 470 PO-- /dbms/links/ifashrpy4 c00000008f57f028 17 4 256 1023744 2 PO-- /dbms/links/ifashrpy5 c00000008f57f1c0 18 4 256 1023744 9 PO-- /dbms/links/ifashrpy6 c00000008f57f358 19 4 256 1023744 6 PO-- /dbms/links/ifashrpy7 c00000008f57f4f0 20 4 256 1023744 3 PO-- /dbms/links/ifashrpy9 c00000008f57f688 21 4 256 1023744 552910 PO-- /dbms/links/ifashrpy10 c00000008f57f820 22 4 256 1023744 1023741 PO-- /dbms/links/ifashrpy11 c00000008f57f9b8 23 5 0 511872 51819 PO-- /dbms/links/llogdbs94 c00000008f57fb50 24 6 256 1023744 1021639 PO-- /dbms/links/tempdbs6 c00000008f57fce8 25 7 256 1023744 1023583 PO-- /dbms/links/tempdbs7 c00000008f57fe80 26 8 256 1023744 1023591 PO-- /dbms/links/tempdbs8 c00000008f580028 27 9 256 1023744 1023591 PO-- /dbms/links/tempdbs9 c00000008f5801c0 28 10 256 1023744 1023675 PO-- /dbms/links/tempdbs10 c00000008f580358 29 11 0 1023744 3 PO-- /dbms/links/ifasdevdbs1 c00000008f5804f0 30 11 256 1023744 3817 PO-- /dbms/links/ifasdevdbs2 c00000008f580688 31 11 256 1023744 1276 PO-- /dbms/links/ifasdevdbs3 c00000008f580820 32 11 256 1023744 25494 PO-- /dbms/links/ifasdevdbs4 c00000008f5809b8 33 11 256 1023744 22984 PO-- /dbms/links/ifasdevdbs5 c00000008f580b50 34 11 256 1023744 143 PO-- /dbms/links/ifasdevdbs6 c00000008f580ce8 35 11 256 1023744 0 PO-- /dbms/links/ifasdevdbs7 c00000008f580e80 36 11 256 1023744 14017 PO-- /dbms/links/ifasdevdbs8 c00000008f581028 37 11 256 1023744 133739 PO-- /dbms/links/ifasdevdbs9 c00000008f5811c0 38 11 256 1023744 948330 PO-- /dbms/links/ifasdevdbs10 c00000008f581358 39 4 256 1023744 1023741 PO-- /dbms/links/ifashrpy12 c00000008f5814f0 40 12 0 63750 197 PO-- /dbms/links/plogdbs94 c00000008f581688 41 13 256 1023744 1023691 PO-- /dbms/links/ifasdbs94temp c00000008f581820 42 14 256 1023744 1004580 PO-- /dbms/links/ifasdev2dbspyt c00000008f5819b8 43 15 256 1023744 634273 PO-- /dbms/links/ifasdev2dbspyt2 c00000008f581b50 44 15 256 1023744 0 PO-- /dbms/links/ifasdev2dbspyt3 44 active, 2047 maximum Expanded chunk capacity mode: disabled onstat -D IBM Informix Dynamic Server Version 9.40.FC6 -- On-Line -- Up 2 days 19:52:43 -- 2325280 Kbytes Dbspaces address number flags fchunk nchunks flags owner name c00000008e3fbe60 1 0x20001 1 1 N informix rootdbs c00000008f581ce8 2 0x1 2 1 N informix syscat94 c00000008f581e68 3 0x1 3 10 N informix ifasnrdbs941 c00000008f584028 4 0x20001 13 11 N informix ifashrpy c00000008f5841a8 5 0x20001 23 1 N informix llogdbs94 c00000008f584328 6 0x2001 24 1 N T informix tempdbs6 c00000008f5844a8 7 0x2001 25 1 N T informix tempdbs7 c00000008f584628 8 0x2001 26 1 N T informix tempdbs8 c00000008f5847a8 9 0x2001 27 1 N T informix tempdbs9 c00000008f584928 10 0x20001 28 1 N informix tempdbs10 c00000008f584aa8 11 0x20001 29 10 N informix ifasdevdbs941 c00000008f584c28 12 0x20001 40 1 N informix plogdbs94 c00000008f584da8 13 0x20001 41 1 N informix ifasdbs94temp c00000008f586028 14 0x20001 42 1 N informix ifasdev2dbspyt c00000008f5861a8 15 0x20001 43 2 N informix ifasdev2dbspyt2 15 active, 2047 maximum Chunks address chunk/dbs offset page Rd page Wr pathname c00000008e3fc028 1 1 0 481 2063 /dbms/links/rootdbs9 c00000008f57d710 2 2 0 900 112 /dbms/links/syscatdbs94 c00000008f57d8a8 3 3 0 194832 241 /dbms/links/ifasnrdbs941 c00000008f57da40 4 3 256 566 20 /dbms/links/ifasnrdbs942 c00000008f57dbd8 5 3 256 15582 667 /dbms/links/ifasnrdbs943 c00000008f57dd70 6 3 256 18903 12243 /dbms/links/ifasn

Part 3 onstat -g prc IBM Informix Dynamic Server Version 9.40.FC6 -- On-Line -- Up 2 days 19:59:37 -- 2325280 Kbytes UDR Cache: Number of lists : 31 PC_POOLSIZE : 127 UDR Cache Entries: list# id ref_cnt dropped? heap_ptr udr name -------------------------------------------------------------- 11 231 0 0 c00000008f0acbc0 ifasdev2@online9:.getuniquekey2 11 1 0 0 c00000008fef7438 ifasdev2@online9:.indexkeyarray_out 21 230 0 0 c00000008fa82838 ifasdev2@online9:.getuserid 26 3 0 0 c00000008f777438 ifasdev2@online9:.ikeyextractcolno 27 229 0 0 c00000008f97d650 ifasdev2@online9:.setuserid Total number of udr entries: 5. Number of entries in use : 0 onstat -g cac IBM Informix Dynamic Server Version 9.40.FC6 -- On-Line -- Up 2 days 19:59:52 -- 2325280 Kbytes UDR Cache: Number of lists : 31 PC_POOLSIZE : 127 UDR Cache Entries: list# id ref_cnt dropped? heap_ptr udr name -------------------------------------------------------------- 11 231 0 0 c00000008f0acbc0 ifasdev2@online9:.getuniquekey2 11 1 0 0 c00000008fef7438 ifasdev2@online9:.indexkeyarray_out 21 230 0 0 c00000008fa82838 ifasdev2@online9:.getuserid 26 3 0 0 c00000008f777438 ifasdev2@online9:.ikeyextractcolno 27 229 0 0 c00000008f97d650 ifasdev2@online9:.setuserid Total number of udr entries: 5. Number of entries in use : 0 Resolved Routine Cache: Number of lists : 31 DS_POOLSIZE : 127 Resolved Routine Cache Entries: list# id ref_cnt dropped? heap_ptr udr name -------------------------------------------------------------- 2 0 0 0 c00000008feaa438 ifasdev2@online9:.greaterthan 2 0 0 0 c00000008feaa038 ifasdev2@online9:.greaterthan 4 0 0 0 c00000008f6ef838 ifasdev2@online9:.lessthanorequal 4 0 0 0 c00000008fa51438 ifasdev2@online9:.lessthanorequal 10 0 0 0 c00000008fa51838 ifasdev2@online9:.greaterthanorequal 10 0 0 0 c00000008feaac38 ifasdev2@online9:.greaterthanorequal 12 0 0 0 c00000008fa51038 ifasdev2@online9:.notequal 12 0 0 0 c00000008fa7a838 ifasdev2@online9:.notequal 15 0 0 0 c00000008fa7a438 ifasdev2@online9:.matches 15 0 0 0 c00000008fa7a038 ifasdev2@online9:.matches 18 0 0 0 c00000008feaa838 ifasdev2@online9:.lessthan 18 0 0 0 c00000008f6efc38 ifasdev2@online9:.lessthan 29 0 0 0 c00000008fa7ac38 ifasdev2@online9:.setuserid Total number of resolved routine entries: 13. Number of entries in use : 0 Distribution Cache: Number of lists : 31 DS_POOLSIZE : 127 Distribution Cache Entries: list# id ref_cnt dropped? heap_ptr distribution name ----------------------------------------------------------------- 0 0 0 0 c00000008f6f8838 ifasdev2:bsi. 0 0 0 0 c00000008fa6c838 ifasdev2:bsi. 1 0 0 0 c00000008f9dfc38 ifasdev2:bsi. 1 0 0 0 c00000008f849838 ifasdev2:bsi. 1 0 0 0 c00000008fb01038 ifasdev2:bsi. 2 0 0 0 c00000008f96e438 ifasdev2:bsi. 2 0 0 0 c00000008fb21c38 ifasdev2:bsi. 2 0 0 0 c00000008f87e438 ifasdev2:bsi. 3 0 0 0 c00000008f7ad038 ifasdev2:bsi. 3 0 0 0 c00000008fb39438 ifasdev2:bsi. 3 0 0 0 c00000008fb1cc38 ifasdev2:bsi. 4 0 0 0 c00000008fd93838 ifasdev2:bsi. 4 0 0 0 c00000008f7fc438 ifasdev2:bsi. 4 0 0 0 c00000008fafb838 ifasdev2:bsidba. 5 0 0 0 c00000008f7ae438 ifasdev2:bsi. 5 0 0 0 c00000008fbfe038 ifasdev2:bsidba. 5 0 0 0 c00000008f888838 ifasdev2:bsi. 6 0 0 0 c00000008f8e1438 ifasdev2:bsi. 6 0 0 0 c00000008f881038 ifasdev2:bsi. 6 0 0 0 c00000008fd49038 ifasdev2:bsidba. 7 0 0 0 c00000008f6b1438 ifasdev2:bsi. 7 0 0 0 c00000008fb25c38 ifasdev2:bsidba. 7 0 0 0 c00000008f8bc038 ifasdev2:bsi. 8 0 0 0 c00000008f874038 ifasdev2:bsi. 8 0 0 0 c00000008f7ad438 ifasdev2:bsi. 8 0 0 0 c00000008fa5a838 ifasdev2:bsi. 9 0 0 0 c00000008f5e2038 ifasdev2:bsidba. 9 0 0 0 c00000008fc14438 ifasdev2:bsi. 9 0 0 0 c00000008f7d3838 ifasdev2:bsi. 10 0 0 0 c00000008f887438 ifasdev2:bsidba. 10 0 0 0 c00000008fa20038 ifasdev2:bsi. 11 0 0 0 c00000008fa67438 ifasdev2:bsi. 11 0 0 0 c00000008f6edc38 ifasdev2:bsi. 11 0 0 0 c00000008f8d8c38 ifasdev2:bsi. 12 0 0 0 c00000008fb41838 ifasdev2:bsidba. 12 0 0 0 c00000008f71d038 ifasdev2:bsi. 13 0 0 0 c00000008fb2e438 ifasdev2:bsidba. 13 0 0 0 c00000008ff63038 ifasdev2:bsi. 13 0 0 0 c00000008fc7d838 ifasdev2:bsi. 14 0 0 0 c00000008fd10438 ifasdev2:bsi. 14 0 0 0 c00000008f91a438 ifasdev2:bsi. 14 0 0 0 c00000008f734c38 ifasdev2:bsi. 15 0 0 0 c00000008f726038 ifasdev2:bsi. 15 0 0 0 c00000008f8ef838 ifasdev2:bsi. 16 0 0 0 c00000008f6cd438 ifasdev2:bsidba. 16 0 0 0 c00000008f6f3838 ifasdev2:bsi. 16 0 0 0 c00000008fa9bc38 ifasdev2:bsi. 17 0 0 0 c00000008f843838 ifasdev2:bsi. 17 0 0 0 c00000008f68ec38 ifasdev2:bsi. 17 0 0 0 c00000008f7b6038 ifasdev2:bsi. 18 0 0 0 c00000008fd1f438 ifasdev2:bsi. 18 0 0 0 c00000008f962438 ifasdev2:bsi. 19 0 0 0 c00000008fa68038 ifasdev2:bsi. 19 0 0 0 c00000008fb34038 ifasdev2:bsi. 19 0 0 0 c00000008ff70038 ifasdev2:bsi. 20 0 0 0 c00000008fb34438 ifasdev2:bsi. 20 0 0 0 c00000008fb41438 ifasdev2:bsi. 20 0 0 0 c00000008fa52038 ifasdev2:bsidba. 21 0 0 0 c00000008fd10838 ifasdev2:bsi. 21 0 0 0 c00000008f9a2838 ifasdev2:bsi. 21 0 0 0 c00000008f8e5438 ifasdev2:bsidba. 22 0 0 0 c00000008fb34838 ifasdev2:bsi. 22 0 0 0 c00000008fb2ec38 ifasdev2:bsidba. 22 0 0 0 c00000008f874438 ifasdev2:bsi. 23 0 0 0 c00000008fda9038 ifasdev2:bsi. 23 0 0 0 c00000008fe80838 ifasdev2:bsi. 23 0 0 0 c00000008f962838 ifasdev2:bsi. 24 0 0 0 c00000008f906438 ifasdev2:bsi. 24 0 0 0 c00000008f9ed038 ifasdev2:bsidba. 24 0 0 0 c00000008f68e838 syscat:bsi. 25 0 0 0 c00000008f887038 ifasdev2:bsidba. 25 0 0 0 c00000008fdb5838 ifasdev2:bsi. 26 0 0 0 c00000008f784c38 ifasdev2:bsidba. 26 0 0 0 c00000008f957038 ifasdev2:bsi. 26 0 0 0 c00000008fa20438 ifasdev2:bsidba. 27 0 0 0 c00000008fa61438 ifasdev2:bsi. 27 0 0 0 c00000008f71a838 ifasdev2:bsi. 28 0 0 0 c00000008f686438 ifasdev2:bsi. 28 0 0 0 c00000008f906838 ifasdev2:bsidba. 28 0 0 0 c00000008fb21438 ifasdev2:bsidba. 29 0 0 0 c00000008f7b2c38 ifasdev2:bsi. 29 0 0 0 c00000008f6f9c38 ifasdev2:bsi. 29 0 0 0 c00000008f9c8038 ifasdev2:bsi. 30 0 0 0 c00000008fd93038 ifasdev2:bsi. 30 0 0 0 c00000008f7d3038 ifasdev2:bsi. Total number of distribution entries: 85. Number of entries in use : 0 Extended Type Name Cache: Number of lists : 31 DS_POOLSIZE : 127 Extended Type Name Cache Entries: list# id ref_cnt dropped? heap_ptr item -------------------------------------------------------------- 1 7 2 0 c00000008f74b838 ifasdev2@online9:informix.indexkeyarray (40,7) 3 1 1 0 c00000008f74bc38 ifasdev2@online9:informix.lvarchar (40,1) Total number of entries: 2. Number of entries in use : 2 Extended Type ID Cache: Number of lists : 31 DS_POOLSIZE : 127 Extended Type ID Cache Entries: list# id ref_cnt dropped? heap_ptr item -------------------------------------------------------------- 12 7 0 0 c00000008f74b038 ifasdev2@online9:informix.indexkeyarray (40,7) 19 1 0 0 c00000008f74b438 ifasdev2@online9:informix.lvarchar (40,1) Total number of entries: 2. Number of entries i

Part 2 of posting onstat -c IBM Informix Dynamic Server Version 9.40.FC6 -- On-Line -- Up 2 days 19:54:35 -- 2325280 Kbytes Configuration File: /dbms/informix9/etc/onconfig.std #************************************************************************** # # INFORMIX SOFTWARE, INC. # # Title: onconfig.std# Description: Informix Dynamic Server 9.4 Configuration Parameters #************************************************************************** # Root Dbspace Configuration ROOTNAME rootdbs # Root dbspace nameROOTPATH /dbms/links/rootdbs9 # Path for device containing root dbspace ROOTOFFSET 0 # Offset of root dbspace into device (Kbytes) ROOTSIZE 30000 # 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 plogdbs94 # Location (dbspace) of physical log PHYSFILE 127000 # Physical log file size (Kbytes) # Logical Log Configuration LOGFILES 101 # Number of logical log files LOGSIZE 2000 # Logical log size (Kbytes) # Diagnostics MSGPATH /dbms/informix9/log/online.log # System message log file path CONSOLE /dev/console # System console message path # To automatically backup logical logs, edit alarmprogram.sh and set # BACKUPLOGS=Y ALARMPROGRAM /dbms/informix9/etc/no_log.sh # Alarm program path TBLSPACE_STATS 0 # Maintain tblspace statistics # System Archive Tape Device TAPEDEV /dev/null # Tape device path TAPEBLK 256 # Tape block size (Kbytes) TAPESIZE 70000000 # Maximum amount of data to put on tape (Kbytes) # Log Archive Tape Device LTAPEDEV /dr_data/log.bak # Log tape device path LTAPEBLK 256 # Log tape block size (Kbytes) LTAPESIZE 70000000 # Max amount of data to put on log tape (Kbytes) # Optical STAGEBLOB # Informix Dynamic Server staging area # System Configuration SERVERNUM 1 # Unique id corresponding to a OnLine instance DBSERVERNAME online9 # Name of default database server DBSERVERALIASES test9 # List of alternate dbservernames NETTYPE ipcshm,2,100,CPU NETTYPE soctcp,2,100,NET #NETTYPE ipcshm,4,250,CPU DEADLOCK_TIMEOUT 120 # Max time to wait of lock in distributed env. RESIDENT 4294967295 # Forced residency flag (Yes = 1, No = 0) MULTIPROCESSOR 1 # 0 for single-processor, 1 for multi-processor #turn this off if using VPCLASS #NUMCPUVPS 3 # Number of user (cpu) vps SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps to one#turn these 3 off if using above VPCLASS #NOAGE 0 # Process aging #AFF_SPROC 1 # Affinity start processor #AFF_NPROCS 0 # Affinity number of processors # Shared Memory Parameters LOCKS 200000 # Maximum number of locks BUFFERS 900000 # Maximum number of shared buffers#turn this off if using VPCLASS #NUMAIOVPS 3 # Number of IO vps PHYSBUFF 320 # Physical log buffer size (Kbytes) LOGBUFF 64 # Logical log buffer size (Kbytes) CLEANERS 8 # Number of buffer cleaner processes SHMBASE 0x0 # Shared memory base address SHMVIRTSIZE 327680 SHMADD 32768 # Size of new shared memory segments (Kbytes) SHMTOTAL 0 # Total shared memory (Kbytes). 0=>unlimited CKPTINTVL 300 # Check point interval (in sec) LRUS 128 # Number of LRU queues LRU_MAX_DIRTY 5.000000 # LRU percent dirty begin cleaning limit LRU_MIN_DIRTY 2.000000 # LRU percent dirty end cleaning limit TXTIMEOUT 0x12c # Transaction timeout (in sec) STACKSIZE 64 # Stack size (Kbytes) # Dynamic Logging # DYNAMIC_LOGS: # 2 : server automatically add a new logical log when necessary. (ON) # 1 : notify DBA to add new logical logs when necessary. (ON) # 0 : cannot add logical log on the fly. (OFF) # # When dynamic logging is on, we can have higher values for LTXHWM/LTXEHWM, # because the server can add new logical logs during long transaction rollback. # However, to limit the number of new logical logs being added, LTXHWM/LTXEHWM # can be set to smaller values. # # If dynamic logging is off, LTXHWM/LTXEHWM need to be set to smaller values # to avoid long transaction rollback hanging the server due to lack of logical # log space, i.e. 50/60 or lower. DYNAMIC_LOGS 0 LTXHWM 40 LTXEHWM 50 # 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 DRINTERVAL 30 # DR max time between DR buffer flushes (in sec) DRTIMEOUT 30 # DR network timeout (in sec) DRLOSTFOUND /usr/informix/etc/dr.lostfound # DR lost+found file path # CDR Variables 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) CDR_NIFCOMPRESS 0 # Link level compression (-1 never, 0 none, 9 max) CDR_SERIAL 0,0 # Serial Column Sequence CDR_DBSPACE # dbspace for syscdr database CDR_QHDR_DBSPACE # CDR queue dbspace (default same as catalog) CDR_QDATA_SBSPACE # List of CDR queue smart blob spaces # CDR_MAX_DYNAMIC_LOGS # -1 => unlimited # 0 => disable dynamic log addition # >0 => limit the no. of dynamic log additions with the specified value. # Max dynamic log requests that CDR can make within one server session. CDR_MAX_DYNAMIC_LOGS 0 # Dynamic log addition disabled by default # Backup/Restore variables BAR_ACT_LOG /dbms/informix9/log/bar_act.log # ON-Bar Log file - not in /tmp please BAR_DEBUG_LOG /dbms/informix9/log/informix/bar_dbug.log # ON-Bar Debug Log - not in /tmp please BAR_MAX_BACKUP 0 BAR_RETRY 1 BAR_NB_XPORT_COUNT 10 BAR_XFER_BUF_SIZE 31 RESTARTABLE_RESTORE on BAR_PROGRESS_FREQ 0 # Informix Storage Manager variables ISM_DATA_POOL ISMData ISM_LOG_POOL ISMLogs # Read Ahead Variables # was 16 and 8, using Belevue settings for test RA_PAGES 32 # Number of pages to attempt to read ahead RA_THRESHOLD 30 # 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 # Default temp dbspaces DBSPACETEMP tempdbs6:tempdbs7:tempdbs8:tempdbs9:tempdbs10:ifasdbs94temp # DUMP*: # The following parameters control the type of diagnostics information which # is preserved when an unanticipated error condition (asserti

Hi, Try setting OPTCOMPIND to 2 in case your queries are optimized for 7.3x. MAX_PDQPRIO should be 100 in case you want to use PDQ when your machine is executing only one heavy task. Also DS_TOTAL_MEMORY should not be limited in this case (empty param). Maybe this helps. Marcus -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Doug Fossmeyer Sent: Tuesday, June 13, 2006 2:04 AM To: ids@iiug.org Subject: Re: Performance issue after 732 to 94fc6 upgra.... [6934] Part 2 of posting onstat -c IBM Informix Dynamic Server Version 9.40.FC6 -- On-Line -- Up 2 days 19:54:35 -- 2325280 Kbytes Configuration File: /dbms/informix9/etc/onconfig.std #************************************************************************** # # INFORMIX SOFTWARE, INC. # # Title: onconfig.std# Description: Informix Dynamic Server 9.4 Configuration Parameters #************************************************************************** # Root Dbspace Configuration ROOTNAME rootdbs # Root dbspace nameROOTPATH /dbms/links/rootdbs9 # Path for device containing root dbspace ROOTOFFSET 0 # Offset of root dbspace into device (Kbytes) ROOTSIZE 30000 #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 plogdbs94 # Location (dbspace) of physical log PHYSFILE 127000 # Physical log file size (Kbytes) # Logical Log Configuration LOGFILES 101 # Number of logical log files LOGSIZE 2000 # Logical log size (Kbytes) # Diagnostics MSGPATH /dbms/informix9/log/online.log # System message log file path CONSOLE /dev/console # System console message path # To automatically backup logical logs, edit alarmprogram.sh and set # BACKUPLOGS=Y ALARMPROGRAM /dbms/informix9/etc/no_log.sh # Alarm program path TBLSPACE_STATS 0 # Maintain tblspace statistics # System Archive Tape Device TAPEDEV /dev/null # Tape device path TAPEBLK 256 # Tape block size (Kbytes) TAPESIZE 70000000 # Maximum amount of data to put on tape (Kbytes) # Log Archive Tape Device LTAPEDEV /dr_data/log.bak # Log tape device path LTAPEBLK 256 # Log tape block size (Kbytes) LTAPESIZE 70000000 # Max amount of data to put on log tape (Kbytes) # Optical STAGEBLOB # Informix Dynamic Server staging area # System Configuration SERVERNUM 1 # Unique id corresponding to a OnLine instance DBSERVERNAMEonline9 # Name of default database server DBSERVERALIASES test9 # List of alternate dbservernames NETTYPE ipcshm,2,100,CPU NETTYPE soctcp,2,100,NET #NETTYPE ipcshm,4,250,CPU DEADLOCK_TIMEOUT 120 # Max time to wait of lock in distributed env. RESIDENT 4294967295 # Forced residency flag (Yes = 1, No = 0) MULTIPROCESSOR 1 # 0 for single-processor, 1 for multi-processor #turn this off if using VPCLASS #NUMCPUVPS 3 # Number of user (cpu) vps SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps to one #turn these 3 off if using above VPCLASS #NOAGE 0 # Process aging #AFF_SPROC 1 # Affinity start processor #AFF_NPROCS 0 # Affinity number of processors # Shared Memory Parameters LOCKS 200000 # Maximum number of locks BUFFERS 900000 # Maximum number of shared buffers #turn this off if using VPCLASS #NUMAIOVPS 3 # Number of IO vps PHYSBUFF 320 # Physical log buffersize (Kbytes) LOGBUFF 64 # Logical log buffer size (Kbytes) CLEANERS 8 # Number of buffer cleaner processes SHMBASE 0x0 # Shared memory base address SHMVIRTSIZE 327680 SHMADD 32768 # Size of new shared memory segments (Kbytes) SHMTOTAL 0 # Total shared memory (Kbytes). 0=>unlimited CKPTINTVL300 # Check point interval (in sec) LRUS 128 # Number of LRU queues LRU_MAX_DIRTY 5.000000 # LRU percent dirty begin cleaning limit LRU_MIN_DIRTY 2.000000 # LRU percent dirty end cleaning limit TXTIMEOUT0x12c # Transaction timeout (in sec) STACKSIZE 64 # Stack size (Kbytes) # Dynamic Logging # DYNAMIC_LOGS: # 2 : server automatically add a new logical log when necessary. (ON) # 1 : notify DBA to add new logical logs when necessary. (ON) # 0 : cannot add logical log on the fly. (OFF) # # When dynamic logging is on, we can have higher values for LTXHWM/LTXEHWM, # because the server can add new logical logs during long transaction rollback. # However, to limit the number of new logical logs being added, LTXHWM/LTXEHWM # can be set to smaller values. # # If dynamic logging is off, LTXHWM/LTXEHWM need to be set to smaller values # to avoid long transaction rollback hanging the server due to lack of logical # log space, i.e. 50/60 or lower. DYNAMIC_LOGS 0 LTXHWM 40 LTXEHWM 50 # 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 DRINTERVAL 30 # DR max time between DR buffer flushes (in sec) DRTIMEOUT 30 # DR network timeout (in sec) DRLOSTFOUND /usr/informix/etc/dr.lostfound #DR lost+found file path # CDR Variables CDR_EVALTHREADS 1,2 # evaluator threads (per-cpu-vp,additional) CDR_DSLOCKWAIT 5 # DS lockwait timeout (seconds) CDR_QUEUEMEM 4096 # Maximumamount of memory for any CDR queue (Kbytes) CDR_NIFCOMPRESS 0 # Link level compression (-1 never, 0 none, 9 max) CDR_SERIAL 0,0 # Serial Column Sequence CDR_DBSPACE # dbspace for syscdr database CDR_QHDR_DBSPACE # CDR queue dbspace (default same as catalog) CDR_QDATA_SBSPACE # List of CDR queue smart blob spaces # CDR_MAX_DYNAMIC_LOGS # -1 => unlimited # 0 => disable dynamic log addition # >0 => limit the no. of dynamic log additions with the specified value. # Max dynamic log requests that CDR can make within one server session. CDR_MAX_DYNAMIC_LOGS 0 # Dynamic log addition disabled by default # Backup/Restore variables BAR_ACT_LOG /dbms/informix9/log/bar_act.log # ON-Bar Log file - not in /tmp please BAR_DEBUG_LOG /dbms/informix9/log/informix/bar_dbug.log # ON-Bar Debug Log - not in /tmp please BAR_MAX_BACKUP 0 BAR_RETRY 1 BAR_NB_XPORT_COUNT 10 BAR_XFER_BUF_SIZE 31 RESTARTABLE_RESTORE on BAR_PROGRESS_FREQ 0 # Informix Storage Manager variables ISM_DATA_POOL ISMData ISM_LOG_POOL ISMLogs # Read Ahead Variables # was 16 and 8, using Belevue settings for test RA_PAGES 32 # Number of pages to attempt to read ahead RA_THRESHOLD 30 # 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 b

Doug See embedded comments below Keith -> -----Original Message----- -> From: Doug Fossmeyer [mailto:DougF@SpokaneSchools.org] -> Sent: Tuesday, June 13, 2006 1:04 AM -> To: ids@iiug.org -> Subject: Re: Performance issue after 732 to 94fc6 upgra.... [6934] -> -> -> -> Part 2 of posting -> -> onstat -c -> -> IBM Informix Dynamic Server Version 9.40.FC6 -- On-Line -- -> Up 2 days 19:54:35 -> -- 2325280 Kbytes -> -> Configuration File: /dbms/informix9/etc/onconfig.std -> #************************************************************ -> ************** -> # -> # INFORMIX SOFTWARE, INC. -> # -> # Title: onconfig.std -> # Description: Informix Dynamic Server 9.4 Configuration Parameters -> #************************************************************ -> ************** -> -> # Root Dbspace Configuration -> -> ROOTNAME rootdbs # Root dbspace name -> ROOTPATH /dbms/links/rootdbs9 # Path for device containing -> root dbspace -> ROOTOFFSET 0 # Offset of root dbspace into device (Kbytes) -> ROOTSIZE 30000 # 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 plogdbs94 # Location (dbspace) of physical log -> PHYSFILE 127000 # Physical log file size (Kbytes) -> -> # Logical Log Configuration -> -> LOGFILES 101 # Number of logical log files -> LOGSIZE 2000 # Logical log size (Kbytes) -> -> # Diagnostics -> -> MSGPATH /dbms/informix9/log/online.log # System message log -> file path -> CONSOLE /dev/console # System console message path -> -> # To automatically backup logical logs, edit alarmprogram.sh and set -> # BACKUPLOGS=Y -> ALARMPROGRAM /dbms/informix9/etc/no_log.sh # Alarm program path -> TBLSPACE_STATS 0 # Maintain tblspace statistics -> -> # System Archive Tape Device -> -> TAPEDEV /dev/null # Tape device path -> TAPEBLK 256 # Tape block size (Kbytes) -> TAPESIZE 70000000 # Maximum amount of data to put on tape (Kbytes) -> -> # Log Archive Tape Device -> -> LTAPEDEV /dr_data/log.bak # Log tape device path -> LTAPEBLK 256 # Log tape block size (Kbytes) -> LTAPESIZE 70000000 # Max amount of data to put on log tape (Kbytes) -> -> # Optical -> -> STAGEBLOB # Informix Dynamic Server staging area -> -> # System Configuration -> -> SERVERNUM 1 # Unique id corresponding to a OnLine instance -> DBSERVERNAME online9 # Name of default database server -> DBSERVERALIASES test9 # List of alternate dbservernames -> NETTYPE ipcshm,2,100,CPU -> NETTYPE soctcp,2,100,NET -> #NETTYPE ipcshm,4,250,CPU -> DEADLOCK_TIMEOUT 120 # Max time to wait of lock in distributed env. -> RESIDENT 4294967295 # Forced residency flag (Yes = 1, No = 0) Why this figure, alternatives are 1 or 0 ?? -> -> MULTIPROCESSOR 1 # 0 for single-processor, 1 for multi-processor -> -> #turn this off if using VPCLASS -> #NUMCPUVPS 3 # Number of user (cpu) vps -> SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps to one -> #turn these 3 off if using above VPCLASS -> #NOAGE 0 # Process aging -> #AFF_SPROC 1 # Affinity start processor -> #AFF_NPROCS 0 # Affinity number of processors -> -> # Shared Memory Parameters -> -> LOCKS 200000 # Maximum number of locks -> BUFFERS 900000 # Maximum number of shared buffers -> #turn this off if using VPCLASS -> #NUMAIOVPS 3 # Number of IO vps -> PHYSBUFF 320 # Physical log buffer size (Kbytes) -> LOGBUFF 64 # Logical log buffer size (Kbytes) -> CLEANERS 8 # Number of buffer cleaner processes Should be the same as LRUS -> SHMBASE 0x0 # Shared memory base address -> SHMVIRTSIZE 327680 -> SHMADD 32768 # Size of new shared memory segments (Kbytes) -> SHMTOTAL 0 # Total shared memory (Kbytes). 0=>unlimited -> CKPTINTVL 300 # Check point interval (in sec) -> LRUS 128 # Number of LRU queues Perceived wisdom indicates 127 is a better figure -> LRU_MAX_DIRTY 5.000000 # LRU percent dirty begin cleaning limit -> LRU_MIN_DIRTY 2.000000 # LRU percent dirty end cleaning limit -> TXTIMEOUT 0x12c # Transaction timeout (in sec) -> STACKSIZE 64 # Stack size (Kbytes) Try a larger figure here (256) -> -> # Dynamic Logging -> # DYNAMIC_LOGS: -> # 2 : server automatically add a new logical log when -> necessary. (ON) -> # 1 : notify DBA to add new logical logs when necessary. (ON) -> # 0 : cannot add logical log on the fly. (OFF) -> # -> # When dynamic logging is on, we can have higher values for -> LTXHWM/LTXEHWM, -> # because the server can add new logical logs during long -> transaction -> rollback. -> # However, to limit the number of new logical logs being -> added, LTXHWM/LTXEHWM -> # can be set to smaller values. -> # -> # If dynamic logging is off, LTXHWM/LTXEHWM need to be set -> to smaller values -> # to avoid long transaction rollback hanging the server due -> to lack of logical -> # log space, i.e. 50/60 or lower. -> -> DYNAMIC_LOGS 0 Not for performance, but set this to 1 -> LTXHWM 40 -> LTXEHWM 50 -> -> # 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 -> DRINTERVAL 30 # DR max time between DR buffer flushes (in sec) -> DRTIMEOUT 30 # DR network timeout (in sec) -> DRLOSTFOUND /usr/informix/etc/dr.lostfound # DR lost+found file path -> -> # CDR Variables -> 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) -> CDR_NIFCOMPRESS 0 # Link level compression (-1 never, 0 none, 9 max) -> CDR_SERIAL 0,0 # Serial Column Sequence -> CDR_DBSPACE # dbspace for syscdr database -> CDR_QHDR_DBSPACE # CDR queue dbspace (default same as catalog) -> CDR_QDATA_SBSPACE # List of CDR queue smart blob spaces -> -> # CDR_MAX_DYNAMIC_LOGS -> # -1 => unlimited -> # 0 => disable dynamic log addition -> # >0 => limit the no. of dynamic log additions with the -> specified value. -> # Max dynamic log requests that CDR can make within one -> server session. -> -> CDR_MAX_DYNAMIC_LOGS 0 # Dynamic log addition disabled by default -> -> # Backup/Restore variables -> BAR_ACT_LOG /dbms/informix9/log/bar_act.log -> -> # ON-Bar Log file - not in /tmp please -> BAR_DEBUG_LOG /dbms/informix9/log/informix/bar_dbug.log -> -> # ON-Bar Debug Log - not in /tmp

See below: > ----- Original Message ----- > From: Doug Fossmeyer <ids@iiug.org> > At: 6/12 20:13:55 > > > Part 2 of posting > > onstat -c> > IBM Informix Dynamic Server Version 9.40.FC6 -- On-Line -- Up 2 days > 19:54:35 > -- 2325280 Kbytes > <SNIP>> TBLSPACE_STATS 0 # Maintain tblspace statistics Set TBLSPACE_STATS to 1, the cost is minimal and it will tell you about host spots. > <SNIP> > NETTYPE ipcshm,2,100,CPU > NETTYPE soctcp,2,100,NET You have 3 CPU VPs configured but only have ipcshm pollers in 2 of them, use all 3 unless shared memory connections are rare. > #NETTYPE ipcshm,4,250,CPU > DEADLOCK_TIMEOUT 120 # Max time to wait of lock in distributed env. > RESIDENT > # Forced residency flag (Yes = 1, No = 0) What's this? RESIDENT has only a small number of valid settings, and 2^32-1 is not one of them. Possible settings: RESIDENT -1 -- Set all resident and virtual segments as memory resident in the OS RESIDENT 0 -- Set all segments swapable including the 'resident' segment containing BUFFERS RESIDENT >0 -- Number of VIRTUAL segments in addition to the 'resident' segment to mark as memory resident in the OS. While the last setting would seem to indicate that setting it to allow 4billion and perhaps if that wraps in memory it MIGHT actually be a setting of -1, I wouldn't trust that value. > <SNIP> > CLEANERS 8 # Number of buffer cleaner processes > <SNIP> > LRUS 128 # Number of LRU queues I have found that you almost always want CLEANERS to be set >= LRUS for best checkpoint performance and best LRU flush performance at peak load, of which batch jobs are sometimes the instigators. > <SNIP> > # Read Ahead Variables > # was 16 and 8, using Belevue settings for test > RA_PAGES 32 # Number of pages to attempt to read ahead > RA_THRESHOLD 30 # Number of pages left before next group I don't like setting the RA_PAGES and RA_THRESHOLD so close together. With modern, fast, cached disks, controllers, and SANs it's unneccessary. Try 32 and 16. > # 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 # Default temp dbspaces > DBSPACETEMP tempdbs6:tempdbs7:tempdbs8:tempdbs9:tempdbs10:ifasdbs94temp You should have a few (3+) non-temp dbspaces listed in DBSPACETEMP for use in creating logged temp tables. Otherwise all of those are being written to ROOTDBS which can cause contention. > <SNIP> > # Parallel Database Queries (pdq) > MAX_PDQPRIORITY 10 # Maximum allowed pdqpriority > DS_MAX_QUERIES 2 # Maximum number of decision support queries > DS_TOTAL_MEMORY 17392 # Decision support memory (Kbytes) > DS_MAX_SCANS 20 # Maximum number of decision support scans Setting MAX_PDQPRIORITY so low will prevent you from using high PDQPRIORITY settings to update statistics and build indexes quickly! I'd raise it to 100 and include PDQPRIORITY=10 in /etc/profile. Increase DS_TOTAL_MEMORY so you can have more sort memory when using PDQPRIORITY>0 to update statistics and build indexes quickly! As it's set now you are getting DS_TOTAL_MEMORY/DS_MAX_QUERIES or ~8MB for sorting. Non-PDQ sorts get 15MB be default and can get up to 50MB using DBUPSPACE. Likewise for DS_MAX_QUERIES. If you have the memory to use, allocate up to 1GB to DS_TOTAL_MEMORY and use DS_MAX_QUERIES to limit the memory any single query can use. > DATASKIP off> # OPTCOMPIND > # 0 => Nested loop joins will be preferred (where > # possible) over sortmerge joins and hash joins. > # 1 => If the transaction isolation mode is not > # "repeatable read", optimizer behaves as in (2) > # below. Otherwise it behaves as in (0) above. > # 2 => Use costs regardless of the transaction isolation > # mode. Nested loop joins are not necessarily > # preferred. Optimizer bases its decision purely > # on costs. > OPTCOMPIND 1 # To hint the optimizer Don't know your apps, but most OLTP shops prefer OPTCOMPIND set to 0 while DSS & DW servers function better set to 2. > <SNIP> > VPCLASS CPU,num=3,aff=1-3,noage > VPCLASS AIO,num=4,aff=0-3,noage The stats may be for too short a period, but keep an eye. The onstat -g iov indicates that you may be able to get away with only 2 or 3 AIO VPs. > SNIP> > 46 ifasdev2dbspyt3 990414 990414 0 4.1 This disk is a potential hot spot, albeit a mild one. Keep an eye on it. <SNIP> Art S. Kagel

The only thing I notice in this is that there are only HIGH stats for one column. If you are really using dostats that would indicate that there is only one index (or several with identical keys in different sort directions [ASC/DESC]). Very odd. BTW, you CAN use dostats to update stats on the system catalog, though you only have to do that once in a long while unless you made serious schema changes recently, by adding the -m flag. A few columns with a relatively small number of values could probably benefit from changing MEDIUM to HIGH for them or by increasing the resolution and confidence of the MEDIUM run. Art S. Kagel ----- Original Message ----- From: Doug Fossmeyer <ids@iiug.org> At: 6/12 20:08:40 Part 3 <SNIP>

I cannot calculate accurate metrics for you, as you did not post the time since stats were zero'd and I remember you saying you did zero them at some point. The guestimated numbers look OK, though. Otherwise everything in the tuning dept looks OK from these output. I do see very extensive tempdbspace usage which would indicate that the poorly performing apps are using temp tables. That COULD be a difference in the way the 7.3x and 9.xx optimizers are handling certain complex queries. You should turn dynamic SET EXPLAIN on for the app (onmode -Y <sid> 1) and see what's doing with the queries. Perhaps the change in OPTCOMPIND from 1 to 0 will help by eliminating HASH table creation overhead, but other things may be going on also. Also one partition seems to be dominating the buffer cache: partnum 15728642 with 690224 pages. This could be a red herring caused by the time of day you ran the onstat -P (analysis of onstat -P works best during typical load or peak load or be comparing the two). Reports taken at off hours can show dominance simply because some partition was the last one that was extensively accessed before the server became quiet. Art S. Kagel ----- Original Message ----- From: Doug Fossmeyer <ids@iiug.org> At: 6/12 20:06:08 This is embarrassing, our email server (groupwise is configured to limit email to 30 k. I am having to post my issue to the list serve with that limitation, meaning that to post the onstat and dbschema requires 3, yes 3 emails. I hope someone can evaluate the content and provide some guidance. Aside from upgrading to FC7 we are at a loss as to why the large batch processes are taking so long. Recap: I zero'd out the statistics just before running the process. Nothing else is running at the same time. Our issue is that after the upgrade most OLTP and small batch processes are running fine, the processes with large datasets have increased by 35-45% in elapsed time. HP-UX 11.11, IDS 94FC2, CSDK 2.81, C4GL 7.32HC2, ESQL-C 9.53HC2, 4 cpu 8 gig memory; dbexport, install seq Tools, Engine, Network steps, dbimport, manually update stats of system tables, update stats using dostats. Thanks in advance, Doug Part 1 onstat -p IBM Informix Dynamic Server Version 9.40.FC6 -- On-Line -- Up 2 days 19:51:54 -- 2325280 Kbytes Profile dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached 1692924 5680780 2148702238 99.92 98673 478066 9326835 98.94 isamtot open start read write rewrite delete commit rollbk 1403470631 5711631 27131297 1288458477 714047 182811 21956 192676 0 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 25339.64 948.51 217 1608 bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans 38861 0 1361203201 0 0 77 374795 1127856 ixda-RA idx-RA da-RA RA-pgsused lchwaits 483892 201 25013 509073 4265 onstat -d IBM Informix Dynamic Server Version 9.40.FC6 -- On-Line -- Up 2 days 19:52:11 -- 2325280 Kbytes Dbspaces address number flags fchunk nchunks flags owner name c00000008e3fbe60 1 0x20001 1 1 N informix rootdbs c00000008f581ce8 2 0x1 2 1 N informix syscat94 c00000008f581e68 3 0x1 3 10 N informix ifasnrdbs941 c00000008f584028 4 0x20001 13 11 N informix ifashrpy c00000008f5841a8 5 0x20001 23 1 N informix llogdbs94 c00000008f584328 6 0x2001 24 1 N T informix tempdbs6 c00000008f5844a8 7 0x2001 25 1 N T informix tempdbs7 c00000008f584628 8 0x2001 26 1 N T informix tempdbs8 c00000008f5847a8 9 0x2001 27 1 N T informix tempdbs9 c00000008f584928 10 0x20001 28 1 N informix tempdbs10 c00000008f584aa8 11 0x20001 29 10 N informix ifasdevdbs941 c00000008f584c28 12 0x20001 40 1 N informix plogdbs94 c00000008f584da8 13 0x20001 41 1 N informix ifasdbs94temp c00000008f586028 14 0x20001 42 1 N informix ifasdev2dbspyt c00000008f5861a8 15 0x20001 43 2 N informix ifasdev2dbspyt2 15 active, 2047 maximum Chunks address chunk/dbs offset size free bpages flags pathname c00000008e3fc028 1 1 0 15000 2826 PO-- /dbms/links/rootdbs9 c00000008f57d710 2 2 0 1023744 966451 PO-- /dbms/links/syscatdbs94 c00000008f57d8a8 3 3 0 1023744 3513 PO-- /dbms/links/ifasnrdbs941 c00000008f57da40 4 3 256 1023744 3 PO-- /dbms/links/ifasnrdbs942 c00000008f57dbd8 5 3 256 1023744 2029 PO-- /dbms/links/ifasnrdbs943 c00000008f57dd70 6 3 256 1023744 27788 PO-- /dbms/links/ifasnrdbs944 c00000008f57e028 7 3 256 1023744 53819 PO-- /dbms/links/ifasnrdbs945 c00000008f57e1c0 8 3 256 1023744 17378 PO-- /dbms/links/ifasnrdbs946 c00000008f57e358 9 3 256 1023744 235004 PO-- /dbms/links/ifasnrdbs947 c00000008f57e4f0 10 3 256 1023744 1023441 PO-- /dbms/links/ifasnrdbs948 c00000008f57e688 11 3 256 1023744 1023241 PO-- /dbms/links/ifasnrdbs949 c00000008f57e820 12 3 256 1023744 1023741 PO-- /dbms/links/ifasnrdbs9410 c00000008f57e9b8 13 4 0 1023744 8 PO-- /dbms/links/ifashrpy1 c00000008f57eb50 14 4 256 1023744 0 PO-- /dbms/links/ifashrpy2 c00000008f57ece8 15 4 256 1023744 89 PO-- /dbms/links/ifashrpy3 c00000008f57ee80 16 4 256 1023744 470 PO-- /dbms/links/ifashrpy4 c00000008f57f028 17 4 256 1023744 2 PO-- /dbms/links/ifashrpy5 c00000008f57f1c0 18 4 256 1023744 9 PO-- /dbms/links/ifashrpy6 c00000008f57f358 19 4 256 1023744 6 PO-- /dbms/links/ifashrpy7 c00000008f57f4f0 20 4 256 1023744 3 PO-- /dbms/links/ifashrpy9 c00000008f57f688 21 4 256 1023744 552910 PO-- /dbms/links/ifashrpy10 c00000008f57f820 22 4 256 1023744 1023741 PO-- /dbms/links/ifashrpy11 c00000008f57f9b8 23 5 0 511872 51819 PO-- /dbms/links/llogdbs94 c00000008f57fb50 24 6 256 1023744 1021639 PO-- /dbms/links/tempdbs6 c00000008f57fce8 25 7 256 1023744 1023583 PO-- /dbms/links/tempdbs7 c00000008f57fe80 26 8 256 1023744 1023591 PO-- /dbms/links/tempdbs8 c00000008f580028 27 9 256 1023744 1023591 PO-- /dbms/links/tempdbs9 c00000008f5801c0 28 10 256 1023744 1023675 PO-- /dbms/links/tempdbs10 c00000008f580358 29 11 0 1023744 3 PO-- /dbms/links/ifasdevdbs1 c00000008f5804f0 30 11 256 1023744 3817 PO-- /dbms/links/ifasdevdbs2 c00000008f580688 31 11 256 1023744 1276 PO-- /dbms/links/ifasdevdbs3 c00000008f580820 32 11 256 1023744 25494 PO-- /dbms/links/ifasdevdbs4 c00000008f5809b8 33 11 256 1023744 22984 PO-- /dbms/links/ifasdevdbs5 c00000008f580b50 34 11 256 1023744 143 PO-- /dbms/links/ifasdevdbs6 c00000008f580ce8 35 11 256 1023744 0 PO-- /dbms/links/ifasdevdbs7 c00000008f580e80 36 11 256 1023744 14017 PO-- /dbms/links/ifasdevdbs8 c00000008f581028 37 11 256 1023744 133739 PO-- /dbms/links/ifasdevdbs9 c00000008f5811c0 38 11 256 1023744 948330 PO-- /dbms/links/ifasdevdbs10 c00000008f581358 39 4 256 1023744 1023741 PO-- /dbms/links/ifashrpy12 c00000008f5814f0 40 12 0 63750 197 PO-- /dbms/links/plogdbs94 c00000008f581688 41 13 256 1023744 1023691 PO-- /dbms/links/ifasdbs94temp c00000008f581820 42 14 256 1023744 1004580 PO-- /dbms/links/ifasdev2dbspyt c00000008f5819b8 43 15 256 1023744 634273 PO-- /dbms/links/ifasdev2dbspyt2 c00000008f581b50 44 15 256 1023744 0 PO-- /dbms/links/ifasdev2dbspyt3 44 active, 2047 maximum Expanded chunk capacity mode: disabled onstat -D IBM Informix Dynamic Server Version 9.40.FC6 -- On-Line -- Up 2 days 19:52:43 -- 2325280 Kbytes Dbspaces address number

Hi Doug, I noticed that 75% of the buffers are occupied by a single table accessed sequentially (its partnum is the following 15728642). Another table occupies 10% of the buffer cache (its partnum is 3147332). You can look at the onstat -P output for this info. It is either a lack of indexes or most likely an optimisation problem. 1/ Try setting OPTCOMPIND to 0 2/ The resident parameter should be 0 or 1 and not : RESIDENT 4294967295 # Forced residency flag (Yes = 1, No = 0) 3/ The affinity should be set to off : VPCLASS CPU,num=3,aff=1-3,noage VPCLASS AIO,num=4,aff=0-3,noage 4/ Why are you using 4 AIO VPs? You should use one or 2 AIO Vps max in your case. 5/ Drop the statistics high and test. 6/ Increase the numbre of cleaners I think that you have an optimisation problem since it is slow on some large tables. The optimizer is using the wrong routes. Keep me posted. Regards, Khaled Bentebal ConsultiX Tél: 33 (0) 1 39 12 18 00 Fax: 33 (0) 1 39 12 18 18 Mobile: 33 (0) 6 07 78 41 97 Email: khaled.bentebal@consult-ix.fr Site Web: http://www.consult-ix.fr ----- Original Message ----- From: "Doug Fossmeyer" <DougF@SpokaneSchools.org> To: <ids@iiug.org> Sent: Tuesday, June 13, 2006 2:02 AM Subject: Re: Performance issue after 732 to 94fc6 upgra.... [6932] > > This is embarrassing, our email server (groupwise is configured to limit email > to 30 k. I am having to post my issue to the list serve with that limitation, > meaning that to post the onstat and dbschema requires 3, yes 3 emails. > > I hope someone can evaluate the content and provide some guidance. Aside from > upgrading to FC7 we are at a loss as to why the large batch processes are > taking so long. Recap: I zero'd out the statistics just before running the > process. Nothing else is running at the same time. Our issue is that after the > upgrade most OLTP and small batch processes are running fine, the processes > with large datasets have increased by 35-45% in elapsed time. HP-UX 11.11, IDS > 94FC2, CSDK 2.81, C4GL 7.32HC2, ESQL-C 9.53HC2, 4 cpu 8 gig memory; dbexport, > install seq Tools, Engine, Network steps, dbimport, manually update stats of > system tables, update stats using dostats. > > Thanks in advance, Doug > > Part 1 > > onstat -p> > IBM Informix Dynamic Server Version 9.40.FC6 -- On-Line -- Up 2 days 19:51:54 > -- 2325280 Kbytes> > Profile > dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached > 1692924 5680780 2148702238 99.92 98673 478066 9326835 98.94 > > isamtot open start read write rewrite delete commit rollbk > 1403470631 5711631 27131297 1288458477 714047 182811 21956 192676 0 > > 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 25339.64 948.51 217 1608 > > bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans > 38861 0 1361203201 0 0 77 374795 1127856 > > ixda-RA idx-RA da-RA RA-pgsused lchwaits > 483892 201 25013 509073 4265 > > onstat -d> > IBM Informix Dynamic Server Version 9.40.FC6 -- On-Line -- Up 2 days 19:52:11 > -- 2325280 Kbytes> > Dbspaces > address number flags fchunk nchunks flags owner name > c00000008e3fbe60 1 0x20001 1 1 N informix rootdbs > c00000008f581ce8 2 0x1 2 1 N informix syscat94 > c00000008f581e68 3 0x1 3 10 N informix ifasnrdbs941 > c00000008f584028 4 0x20001 13 11 N informix ifashrpy > c00000008f5841a8 5 0x20001 23 1 N informix llogdbs94 > c00000008f584328 6 0x2001 24 1 N T informix tempdbs6 > c00000008f5844a8 7 0x2001 25 1 N T informix tempdbs7 > c00000008f584628 8 0x2001 26 1 N T informix tempdbs8 > c00000008f5847a8 9 0x2001 27 1 N T informix tempdbs9 > c00000008f584928 10 0x20001 28 1 N informix tempdbs10 > c00000008f584aa8 11 0x20001 29 10 N informix ifasdevdbs941 > c00000008f584c28 12 0x20001 40 1 N informix plogdbs94 > c00000008f584da8 13 0x20001 41 1 N informix ifasdbs94temp > c00000008f586028 14 0x20001 42 1 N informix ifasdev2dbspyt > c00000008f5861a8 15 0x20001 43 2 N informix ifasdev2dbspyt2 > 15 active, 2047 maximum > > Chunks > address chunk/dbs offset size free bpages flags pathname > c00000008e3fc028 1 1 0 15000 2826 PO-- /dbms/links/rootdbs9 > c00000008f57d710 2 2 0 1023744 966451 PO-- /dbms/links/syscatdbs94 > c00000008f57d8a8 3 3 0 1023744 3513 PO-- /dbms/links/ifasnrdbs941 > c00000008f57da40 4 3 256 1023744 3 PO-- /dbms/links/ifasnrdbs942 > c00000008f57dbd8 5 3 256 1023744 2029 PO-- /dbms/links/ifasnrdbs943 > c00000008f57dd70 6 3 256 1023744 27788 PO-- /dbms/links/ifasnrdbs944 > c00000008f57e028 7 3 256 1023744 53819 PO-- /dbms/links/ifasnrdbs945 > c00000008f57e1c0 8 3 256 1023744 17378 PO-- /dbms/links/ifasnrdbs946 > c00000008f57e358 9 3 256 1023744 235004 PO-- /dbms/links/ifasnrdbs947 > c00000008f57e4f0 10 3 256 1023744 1023441 PO-- /dbms/links/ifasnrdbs948 > c00000008f57e688 11 3 256 1023744 1023241 PO-- /dbms/links/ifasnrdbs949 > c00000008f57e820 12 3 256 1023744 1023741 PO-- /dbms/links/ifasnrdbs9410 > c00000008f57e9b8 13 4 0 1023744 8 PO-- /dbms/links/ifashrpy1 > c00000008f57eb50 14 4 256 1023744 0 PO-- /dbms/links/ifashrpy2 > c00000008f57ece8 15 4 256 1023744 89 PO-- /dbms/links/ifashrpy3 > c00000008f57ee80 16 4 256 1023744 470 PO-- /dbms/links/ifashrpy4 > c00000008f57f028 17 4 256 1023744 2 PO-- /dbms/links/ifashrpy5 > c00000008f57f1c0 18 4 256 1023744 9 PO-- /dbms/links/ifashrpy6 > c00000008f57f358 19 4 256 1023744 6 PO-- /dbms/links/ifashrpy7 > c00000008f57f4f0 20 4 256 1023744 3 PO-- /dbms/links/ifashrpy9 > c00000008f57f688 21 4 256 1023744 552910 PO-- /dbms/links/ifashrpy10 > c00000008f57f820 22 4 256 1023744 1023741 PO-- /dbms/links/ifashrpy11 > c00000008f57f9b8 23 5 0 511872 51819 PO-- /dbms/links/llogdbs94 > c00000008f57fb50 24 6 256 1023744 1021639 PO-- /dbms/links/tempdbs6 > c00000008f57fce8 25 7 256 1023744 1023583 PO-- /dbms/links/tempdbs7 > c00000008f57fe80 26 8 256 1023744 1023591 PO-- /dbms/links/tempdbs8 > c00000008f580028 27 9 256 1023744 1023591 PO-- /dbms/links/tempdbs9 > c00000008f5801c0 28 10 256 1023744 1023675 PO-- /dbms/links/tempdbs10 > c00000008f580358 29 11 0 1023744 3 PO-- /dbms/links/ifasdevdbs1 > c00000008f5804f0 30 11 256 1023744 3817 PO-- /dbms/links/ifasdevdbs2 > c00000008f580688 31 11 256 1023744 1276 PO-- /dbms/links/ifasdevdbs3 > c00000008f580820 32 11 256 1023744 25494 PO-- /dbms/links/ifasdevdbs4 > c00000008f5809b8 33 11 256 1023744 22984 PO-- /dbms/links/ifasdevdbs5 > c00000008f580b50 34 11 256 1023744 143 PO-- /dbms/links/ifasdevdbs6 > c00000008f580ce8 35 11 256 1023744 0 PO-- /dbms/links/ifasdevdbs7 > c00000008f580e80 36 11 256 1023744 14017 PO-- /dbms/links/ifasdevdbs8 > c00000008f581028 37 11 256 1023744 133739 PO-- /dbms/links/ifasdevdbs9 > c00000008f5811c0 38 11 256 1023744 948330 PO-- /dbms/links/ifasdevdbs10 > c00000008f581358 39 4 256 1023744 1023741 PO-- /dbms/links/ifashrpy12 > c00000008f5814f0 40 12 0 63750 197 PO-- /dbms/links/plogdbs94 > c00000008f581688 41 13 256 1023744 1023691 PO-- /dbms/links/ifasdbs94temp > c00000008f581820 42 14 256 1023744 1004580 PO-- /dbms/links/ifasdev2dbspyt > c00000008f5819b8 43 15 256 1023744 634273 PO-- /dbms/links/ifasdev2dbspyt

RE: resident value. The resident value that folks have commented on is generated by onmonitor 9or ?). If we edit the file manually with vi or another tool and modify the value to -1 and restart the engine, the file is then overrided with the absurd value of 4294967295. It also alters the SHMEMBASE from 0x0L to 0x0, even though we are on FC with 64bit OS. This was occuring with an initial install of fc2; our vendor gave us FC6, yet the issue remains. >>> DougF@SpokaneSchools.org 06/12/2006 5:04 PM >>> Part 2 of posting onstat -c IBM Informix Dynamic Server Version 9.40.FC6 -- On-Line -- Up 2 days 19:54:35 -- 2325280 Kbytes Configuration File: /dbms/informix9/etc/onconfig.std #************************************************************************** # # INFORMIX SOFTWARE, INC. # # Title: onconfig.std# Description: Informix Dynamic Server 9.4 Configuration Parameters #************************************************************************** # Root Dbspace Configuration ROOTNAME rootdbs # Root dbspace nameROOTPATH /dbms/links/rootdbs9 # Path for device containing root dbspace ROOTOFFSET 0 # Offset of root dbspace into device (Kbytes) ROOTSIZE 30000 # 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 plogdbs94 # Location (dbspace) of physical log PHYSFILE 127000 # Physical log file size (Kbytes) # Logical Log Configuration LOGFILES 101 # Number of logical log files LOGSIZE 2000 # Logical log size (Kbytes) # Diagnostics MSGPATH /dbms/informix9/log/online.log # System message log file path CONSOLE /dev/console # System console message path # To automatically backup logical logs, edit alarmprogram.sh and set # BACKUPLOGS=Y ALARMPROGRAM /dbms/informix9/etc/no_log.sh # Alarm program path TBLSPACE_STATS 0 # Maintain tblspace statistics # System Archive Tape Device TAPEDEV /dev/null # Tape device path TAPEBLK 256 # Tape block size (Kbytes) TAPESIZE 70000000 # Maximum amount of data to put on tape (Kbytes) # Log Archive Tape Device LTAPEDEV /dr_data/log.bak # Log tape device path LTAPEBLK 256 # Log tape block size (Kbytes) LTAPESIZE 70000000 # Max amount of data to put on log tape (Kbytes) # Optical STAGEBLOB # Informix Dynamic Server staging area # System Configuration SERVERNUM 1 # Unique id corresponding to a OnLine instance DBSERVERNAME online9 # Name of default database server DBSERVERALIASES test9 # List of alternate dbservernames NETTYPE ipcshm,2,100,CPU NETTYPE soctcp,2,100,NET #NETTYPE ipcshm,4,250,CPU DEADLOCK_TIMEOUT 120 # Max time to wait of lock in distributed env. RESIDENT 4294967295 # Forced residency flag (Yes = 1, No = 0) MULTIPROCESSOR 1 # 0 for single-processor, 1 for multi-processor #turn this off if using VPCLASS #NUMCPUVPS 3 # Number of user (cpu) vps SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps to one#turn these 3 off if using above VPCLASS #NOAGE 0 # Process aging #AFF_SPROC 1 # Affinity start processor #AFF_NPROCS 0 # Affinity number of processors # Shared Memory Parameters LOCKS 200000 # Maximum number of locks BUFFERS 900000 # Maximum number of shared buffers#turn this off if using VPCLASS #NUMAIOVPS 3 # Number of IO vps PHYSBUFF 320 # Physical log buffer size (Kbytes) LOGBUFF 64 # Logical log buffer size (Kbytes) CLEANERS 8 # Number of buffer cleaner processes SHMBASE 0x0 # Shared memory base address SHMVIRTSIZE 327680 SHMADD 32768 # Size of new shared memory segments (Kbytes) SHMTOTAL 0 # Total shared memory (Kbytes). 0=>unlimited CKPTINTVL 300 # Check point interval (in sec) LRUS 128 # Number of LRU queues LRU_MAX_DIRTY 5.000000 # LRU percent dirty begin cleaning limit LRU_MIN_DIRTY 2.000000 # LRU percent dirty end cleaning limit TXTIMEOUT 0x12c # Transaction timeout (in sec) STACKSIZE 64 # Stack size (Kbytes) # Dynamic Logging # DYNAMIC_LOGS: # 2 : server automatically add a new logical log when necessary. (ON) # 1 : notify DBA to add new logical logs when necessary. (ON) # 0 : cannot add logical log on the fly. (OFF) # # When dynamic logging is on, we can have higher values for LTXHWM/LTXEHWM, # because the server can add new logical logs during long transaction rollback. # However, to limit the number of new logical logs being added, LTXHWM/LTXEHWM # can be set to smaller values. # # If dynamic logging is off, LTXHWM/LTXEHWM need to be set to smaller values # to avoid long transaction rollback hanging the server due to lack of logical # log space, i.e. 50/60 or lower. DYNAMIC_LOGS 0 LTXHWM 40 LTXEHWM 50 # 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 DRINTERVAL 30 # DR max time between DR buffer flushes (in sec) DRTIMEOUT 30 # DR network timeout (in sec) DRLOSTFOUND /usr/informix/etc/dr.lostfound # DR lost+found file path # CDR Variables 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) CDR_NIFCOMPRESS 0 # Link level compression (-1 never, 0 none, 9 max) CDR_SERIAL 0,0 # Serial Column Sequence CDR_DBSPACE # dbspace for syscdr database CDR_QHDR_DBSPACE # CDR queue dbspace (default same as catalog) CDR_QDATA_SBSPACE # List of CDR queue smart blob spaces # CDR_MAX_DYNAMIC_LOGS # -1 => unlimited # 0 => disable dynamic log addition # >0 => limit the no. of dynamic log additions with the specified value. # Max dynamic log requests that CDR can make within one server session. CDR_MAX_DYNAMIC_LOGS 0 # Dynamic log addition disabled by default # Backup/Restore variables BAR_ACT_LOG /dbms/informix9/log/bar_act.log # ON-Bar Log file - not in /tmp please BAR_DEBUG_LOG /dbms/informix9/log/informix/bar_dbug.log # ON-Bar Debug Log - not in /tmp please BAR_MAX_BACKUP 0 BAR_RETRY 1 BAR_NB_XPORT_COUNT 10 BAR_XFER_BUF_SIZE 31 RESTARTABLE_RESTORE on BAR_PROGRESS_FREQ 0 # Informix Storage Manager variables ISM_DATA_POOL ISMData ISM_LOG_POOL ISMLogs # Read Ahead Variables # was 16 and 8, using Belevue settings for test RA_PAGES 32 # Number of pages to attempt to read ahead RA_THRESHOLD 30 # 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