Performance Tuning Discussion

Dear all, I would like to ask opinnion about performance tuning according to an installation configuration. The server machine: Informix 11.7 FC4 Ultimate Edition Redhat 5.5 64 bit RAM 48 Gbyte 4 Disk Storage Database Many tables with 3 big tables. For these 3, I use seperate dbspace with 16K page size. On the 3 big tables each has about 200 million rows and growing. The config I use: SHMTOTAL= 48.234.496 Buffer 16K = 1.130.496, LRU 136 Buffer 2K = 3.014.656, LRU 362 Locks =1.000.000 CLEANERS 8AUTO_AIOVPS 1 DIRECT_IO 1 RESIDENT -1 BTSCANNER num=1,threshold=5000,rangesize=-1,alice=6,compression=default AUTO_LRU_TUNING 0 The query on the big table use lots of join with other tables. I also add indexes for the joined column. Each big tables is store in 2 dbspace where the dbspace chunk is located in 2 seperate disk. Only 2 disk is big enough to store chunks for the dbspace. I also create 4 temporary dbspace, 2 with default page and 2 with 16K page. Spread in the 4 disk. The query performance feels still slow. a query for a year of data took 30 minutes. Is there any suggestion to increase the performance in terms of onconfig file? Thank you.

There is no way to properly tune a server without watching it run and getting live and historical performance data. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Feb 28, 2013 at 11:19 PM, MOHAMMAD IRFAN <irfan199@yahoo.com> wrote: > Dear all, > > I would like to ask opinnion about performance tuning according to an > installation configuration. > > The server machine: > Informix 11.7 FC4 Ultimate Edition > Redhat 5.5 64 bit > RAM 48 Gbyte > 4 Disk Storage > > Database > Many tables with 3 big tables. For these 3, I use seperate dbspace with 16K > page size. > On the 3 big tables each has about 200 million rows and growing. > > The config I use: > SHMTOTAL= 48.234.496 > Buffer 16K = 1.130.496, LRU 136 > Buffer 2K = 3.014.656, LRU 362 > Locks =1.000.000 > CLEANERS 8> AUTO_AIOVPS 1 > DIRECT_IO 1 > RESIDENT -1 > BTSCANNER num=1,threshold=5000,rangesize=-1,alice=6,compression=default > AUTO_LRU_TUNING 0> > The query on the big table use lots of join with other tables. I also add > indexes for the joined column. > Each big tables is store in 2 dbspace where the dbspace chunk is located > in 2 > seperate disk. Only 2 disk is big enough to store chunks for the dbspace. I > also create 4 temporary dbspace, 2 with default page and 2 with 16K page. > Spread in the 4 disk. The query performance feels still slow. a query for a > year of data took 30 minutes. > > Is there any suggestion to increase the performance in terms of onconfig > file? > > Thank you. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --f46d0401fb63cfb2ed04d6d6f0f3

Hi, I agree with Art Kagel in that performance tuning is a process done in cycles: tune, watch, tune, watch, ... If queries adhere to a star or snowflake schema, you may want to consider using the Informix Warehouse Accelerator to increase performance considerably, without much tuning. Regards, Martin -- Martin Fuerderer IBM Informix Development Munich, Germany Information Management Read about the Informix Warehouse Accelerator: http://tinyurl.com/the-iwa-blog IBM Deutschland Research & Development GmbH Chairman of the Supervisory Board: Martina Koederitz Board of Management: Dirk Wittkopp Corporate Seat: Boeblingen, Germany Reg.-Gericht: Amtsgericht Stuttgart, HRB 243294 From: "MOHAMMAD IRFAN" <irfan199@yahoo.com> To: ids@iiug.org, Date: 03/01/2013 05:20 Subject: Performance Tuning Discussion [29634] Sent by: ids-bounces@iiug.org Dear all, I would like to ask opinnion about performance tuning according to an installation configuration. The server machine: Informix 11.7 FC4 Ultimate Edition Redhat 5.5 64 bit RAM 48 Gbyte 4 Disk Storage Database Many tables with 3 big tables. For these 3, I use seperate dbspace with 16K page size. On the 3 big tables each has about 200 million rows and growing. The config I use: SHMTOTAL= 48.234.496 Buffer 16K = 1.130.496, LRU 136 Buffer 2K = 3.014.656, LRU 362 Locks =1.000.000 CLEANERS 8AUTO_AIOVPS 1 DIRECT_IO 1 RESIDENT -1 BTSCANNER num=1,threshold=5000,rangesize=-1,alice=6,compression=default AUTO_LRU_TUNING 0 The query on the big table use lots of join with other tables. I also add indexes for the joined column. Each big tables is store in 2 dbspace where the dbspace chunk is located in 2 seperate disk. Only 2 disk is big enough to store chunks for the dbspace. I also create 4 temporary dbspace, 2 with default page and 2 with 16K page. Spread in the 4 disk. The query performance feels still slow. a query for a year of data took 30 minutes. Is there any suggestion to increase the performance in terms of onconfig file? Thank you. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Hello. You should start from the basics: 1) check your slow query plan, and watch for some improvements there 2) is this a new machine, I suppose, so.... how are your RAID disks levels (are you using raw devices, I also suppose) 3) look for buffers, how are your performance metrics? I´d suggest you to get ratios.sql, and follow up investigating through what metrics suggest (disk/memory, buffers, etc) 4) as you´ve mentioned BTSCANNER, how is your "onstat -C hot" output? I think it must be a huge list.... check it out. Your complete onconfig file should give us a better idea of what could be changed to watch for improvements. Hope it helps. Alexandre Marini IBM Informix Certified Professional v10 / v11.50 / v11.70 IBM Information Management Informix Technical Professional IBM Infosphere DataStage Technical Professional Informix Senior DBA - Orizon Brasil BRIUG website administrator Informix independent consultant > To: ids@iiug.org > From: irfan199@yahoo.com > Subject: Performance Tuning Discussion [29634] > Date: Thu, 28 Feb 2013 23:19:48 -0500 > > Dear all, > > I would like to ask opinnion about performance tuning according to an > installation configuration. > > The server machine: > Informix 11.7 FC4 Ultimate Edition > Redhat 5.5 64 bit > RAM 48 Gbyte > 4 Disk Storage > > Database > Many tables with 3 big tables. For these 3, I use seperate dbspace with 16K > page size. > On the 3 big tables each has about 200 million rows and growing. > > The config I use: > SHMTOTAL= 48.234.496 > Buffer 16K = 1.130.496, LRU 136 > Buffer 2K = 3.014.656, LRU 362 > Locks =1.000.000 > CLEANERS 8> AUTO_AIOVPS 1 > DIRECT_IO 1 > RESIDENT -1 > BTSCANNER num=1,threshold=5000,rangesize=-1,alice=6,compression=default > AUTO_LRU_TUNING 0> > The query on the big table use lots of join with other tables. I also add > indexes for the joined column. > Each big tables is store in 2 dbspace where the dbspace chunk is located in 2 > seperate disk. Only 2 disk is big enough to store chunks for the dbspace. I > also create 4 temporary dbspace, 2 with default page and 2 with 16K page. > Spread in the 4 disk. The query performance feels still slow. a query for a > year of data took 30 minutes. > > Is there any suggestion to increase the performance in terms of onconfig file? > > Thank you. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

What are the tools to perform monitoring and tuning on the informix instance? As far as I know I've been using onstat - (lots of options) but I doesn't seem to enable to interpret all the information. My goals is to get Nice DSS Performance. For OLTP function it is necessary to load big data. onstat -c result: ROOTNAME rootdbs ROOTPATH /opt/zzz/dbspaces/rootdbs ROOTOFFSET 0# ROOTSIZE 200000 ROOTSIZE 5000000MIRROR 1 #MIRRORPATH $INFORMIXDIR/tmp/demo_on.root_mirror MIRRORPATH /opt/zzz/mdbspaces/rootdbs MIRROROFFSET 0 PHYSFILE 8304722 PLOG_OVERFLOW_PATH /opt/informix/tmp PHYSBUFF 128 LOGFILES 17 LOGSIZE 5000 DYNAMIC_LOGS 0 LOGBUFF 128 LTXHWM 70 LTXEHWM 80 MSGPATH /opt/zzz/online.log #CONSOLE $INFORMIXDIR/tmp/online.con CONSOLE /opt/zzz/online.con TBLTBLFIRST 0 TBLTBLNEXT 0 TBLSPACE_STATS 1 #DBSPACETEMP tempdbs:tempdbs1:tempdbs2:tempdbs4 DBSPACETEMP tempdbs1:tempdbs2:tempdbs3:tempdbs4 SBSPACETEMP SBSPACENAME sbspace SYSSBSPACENAME ONDBSPACEDOWN 2 SERVERNUM 0 DBSERVERNAME zzz# DBSERVERALIASES dr_informix1170 FULL_DISK_INIT 0 NETTYPE ipcshm,1,50,CPU NETTYPE soctcp,1,150,NET LISTEN_TIMEOUT 60 MAX_INCOMPLETE_CONNECTIONS 1024 FASTPOLL 1 NS_CACHE host=900,service=900,user=900,group=900 MULTIPROCESSOR 1 VPCLASS cpu,num=15,noage #VP_MEMORY_CACHE_KB 800 #VP_MEMORY_CACHE_KB 262144 VP_MEMORY_CACHE_KB 100000 SINGLE_CPU_VP 0 #VPCLASS aio,num=1 VPCLASS aio,num=10,noage CLEANERS 8AUTO_AIOVPS 1 DIRECT_IO 1 #LOCKS 100000 LOCKS 50000 #DEF_TABLE_LOCKMODE page DEF_TABLE_LOCKMODE row #RESIDENT 0 #RESIDENT 1 RESIDENT -1 SHMBASE 0x44000000#SHMVIRTSIZE 32656 #SHMADD 8192 #EXTSHMADD 8192 SHMVIRTSIZE 35651584 SHMADD 32768 EXTSHMADD 32768# SHMTOTAL 0 # SHMTOTAL 15099494 SHMTOTAL 48234496 SHMVIRT_ALLOCSEG 0.000000 SHMNOACCESS aio,num=10,noage CKPTINTVL 300AUTO_CKPTS 1 RTO_SERVER_RESTART 0 BLOCKTIMEOUT 3600 CONVERSION_GUARD 2 RESTORE_POINT_DIR /opt/informix/tmp TXTIMEOUT 300 DEADLOCK_TIMEOUT 60 HETERO_COMMIT 0 TAPEDEV /dev/tapedev TAPEBLK 32 TAPESIZE 0 LTAPEDEV /dev/null LTAPEBLK 32 LTAPESIZE 0 BAR_ACT_LOG /opt/informix/tmp/bar_act.log BAR_DEBUG_LOG /opt/informix/tmp/bar_dbug.log BAR_DEBUG 0 BAR_MAX_BACKUP 0 BAR_RETRY 1 BAR_NB_XPORT_COUNT 20 BAR_XFER_BUF_SIZE 31 RESTARTABLE_RESTORE on BAR_PROGRESS_FREQ 0 BAR_BSALIB_PATH BACKUP_FILTER RESTORE_FILTER BAR_PERFORMANCE 0 BAR_CKPTSEC_TIMEOUT 15 ISM_DATA_POOL ISMData ISM_LOG_POOL ISMLogs DD_HASHSIZE 31 DD_HASHMAX 10 DS_HASHSIZE 31 DS_POOLSIZE 127 PC_HASHSIZE 31 PC_POOLSIZE 127 #STMT_CACHE 0 #STMT_CACHE 1 STMT_CACHE 2 STMT_CACHE_HITS 0 #STMT_CACHE_SIZE 512 STMT_CACHE_SIZE 2046 STMT_CACHE_NOLIMIT 0 #STMT_CACHE_NUMPOOL 1 STMT_CACHE_NUMPOOL 256 USEOSTIME 0 STACKSIZE 64 ALLOW_NEWLINE 0 USELASTCOMMITTED NONE FILLFACTOR 90 MAX_FILL_DATA_PAGES 0 BTSCANNER num=1,threshold=5000,rangesize=-1,alice=6,compression=default ONLIDX_MAXMEM 5120 MAX_PDQPRIORITY 100 #DS_MAX_QUERIES DS_MAX_QUERIES 245760 #DS_TOTAL_MEMORY DS_TOTAL_MEMORY 33554432 #DS_MAX_SCANS 1048576 DS_MAX_SCANS 1048576 #DS_NONPDQ_QUERY_MEM 128 DS_NONPDQ_QUERY_MEM 7864320 PSORT_NPROCS 4 DATASKIP off #OPTCOMPIND 2 OPTCOMPIND 0 DIRECTIVES 1 EXT_DIRECTIVES 0 OPT_GOAL -1 #IFX_FOLDVIEW 0 IFX_FOLDVIEW 1AUTO_REPREPARE 1 AUTO_STAT_MODE 1 STATCHANGE 10 RA_PAGES 128 RA_THRESHOLD 120 BATCHEDREAD_TABLE 1 BATCHEDREAD_INDEX 1 BATCHEDREAD_KEYONLY 0 #SQLTRACE level=low,ntraces=1000,size=2,mode=global EXPLAIN_STAT 1 SQLTRACE level=low,ntraces=1000,size=2,mode=global #DBCREATE_PERMISSION informix #DB_LIBRARY_PATH IFX_EXTEND_ROLE 1 SECURITY_LOCALCONNECTION 0 UNSECURE_ONSTAT 0 ADMIN_USER_MODE_WITH_DBSA 0 PLCY_POOLSIZE 127 PLCY_HASHSIZE 31 USRC_POOLSIZE 127 USRC_HASHSIZE 31 STAGEBLOB OPCACHEMAX 0 ENCRYPT_HDR 0 ENCRYPT_SMX 0 ENCRYPT_CDR 0 ENCRYPT_CIPHERS ENCRYPT_MAC medium ENCRYPT_MACFILE builtin,builtin,builtin,builtin,builtin,builtin,builtin,builtin,builtin,builtin, builtin,builtin,builtin,builtin,builtin,builtin,builtin,builtin,builtin,builtin, builtin,builtin,builtin,builtin,builtin,builtin,builtin,builtin ENCRYPT_SWITCH 0,0 CDR_EVALTHREADS 1,2 CDR_DSLOCKWAIT 5 CDR_QUEUEMEM 4096 CDR_NIFCOMPRESS 0 CDR_SERIAL 0,0 CDR_DBSPACE CDR_QHDR_DBSPACE CDR_QDATA_SBSPACE CDR_SUPPRESS_ATSRISWARN CDR_LOG_LAG_ACTION ddrblock CDR_LOG_STAGING_MAXSIZE 0 CDR_MAX_DYNAMIC_LOGS 0 DRAUTO 0 DRINTERVAL 30 DRTIMEOUT 30 HA_ALIAS DRLOSTFOUND /opt/informix/etc/dr.lostfound DRIDXAUTO 0 LOG_INDEX_BUILDS 0 SDS_ENABLE 0 SDS_TIMEOUT 20 SDS_PAGING UPDATABLE_SECONDARY 0 FAILOVER_CALLBACK FAILOVER_TX_TIMEOUT 0 TEMPTAB_NOLOG 0 ENABLE_SNAPSHOT_COPY 0 SMX_COMPRESS 0 ON_RECVRY_THREADS 1 OFF_RECVRY_THREADS 10 DUMPDIR /opt/informix/tmp DUMPSHMEM 1 DUMPGCORE 0 DUMPCORE 0 DUMPCNT 1 ALARMPROGRAM /opt/informix/etc/alarmprogram.sh ALRM_ALL_EVENTS 0 STORAGE_FULL_ALARM 600,3 SYSALARMPROGRAM /opt/informix/etc/evidence.sh RAS_PLOG_SPEED 12232 RAS_LLOG_SPEED 0 EILSEQ_COMPAT_MODE 0 QSTATS 0 WSTATS 0 USERMAPPING OFF SP_AUTOEXPAND 1 SP_THRESHOLD 0.000000 SP_WAITTIME 30 DEFAULTESCCHAR \\\\ #VPCLASS MQ,noyield MQSERVER MQCHLLIB #VPCLASS jvp,num=1 #JVPJAVAHOME $INFORMIXDIR/extend/krakatoa/jre #JVPHOME $INFORMIXDIR/extend/krakatoa JVPPROPFILE /opt/informix/extend/krakatoa/.jvpprops #JDKVERSION 1.5 JVPLOGFILE /opt/informix/tmp/jvp.log #JVPJAVALIB /bin/j9vm #JVPJAVAVM jvm #JVPARGS -verbose:jni #JVPCLASSPATH $INFORMIXDIR/extend/krakatoa/krakatoa_g.jar:$INFORMIXDIR/extend/krakatoa/jdbc_g. jar JVPCLASSPATH /opt/informix/extend/krakatoa/krakatoa.jar:/opt/informix/extend/krakatoa/jdbc.jar BUFFERPOOL default,buffers=1000,lrus=15,lru_min_dirty=50.00,lru_max_dirty=60.00 BUFFERPOOL size=2K,buffers=500000,lrus=60,lru_min_dirty=50.00,lru_max_dirty=60.00 BUFFERPOOL size=16K,buffers=500000,lrus=60,lru_min_dirty=50.00,lru_max_dirty=60.00 AUTO_LRU_TUNING 0 DBSERVERALIASES NUMFDSERVERS 4 USTLOW_SAMPLE 0 ADMIN_MODE_USERS SQL_LOGICAL_CHAR off SEQ_CACHE_SIZE 10 SDS_LOGCHECK 0 DELAY_APPLY STOP_APPLY LOG_STAGING_DIR RSS_FLOW_CONTROL REMOTE_SERVER_CFG REMOTE_USERS_CFG S6_USE_REMOTE_SERVER_CFG 0 LOW_MEMORY_RESERVE LOW_MEMORY_MGR 0GSKIT_VERSION 0x8 JVPARGSDBUPSPACE 0:50

Ok it seems you have a big hardware, what´s your Informix version? What´s your box architecture? For performance tips, there is a good article in Andrew Ford´s blog, that should clarify your thoughts.... http://www.informix-dba.com/p/informix-innovator-c-tuning-basics.html This article, from , is very good, also: http://www.ibm.com/developerworks/data/library/techarticle/dm-1104tuneinformix1/ index.html I´d suggest you to go through Art Kagel engine metrics, search into CDI archives for newratios package, install it, and then you should have a good "first steps" to follow.... If you have any specific doubt, we could help you further.... Good luck, and regards. Alexandre Marini IBM Informix Certified Professional v10 / v11.50 / v11.70 IBM Information Management Informix Technical Professional IBM Infosphere DataStage Technical Professional Informix Senior DBA - Orizon Brasil BRIUG website administrator Informix independent consultant > To: ids@iiug.org > From: irfan199@yahoo.com > Subject: Re: Performance Tuning Discussion [29665] > Date: Mon, 4 Mar 2013 06:46:12 -0500 > > What are the tools to perform monitoring and tuning on the informix instance? > As far as I know I've been using onstat - (lots of options) but I doesn't seem > to enable to interpret all the information. > > My goals is to get Nice DSS Performance. For OLTP function it is necessary to > load big data. > > onstat -c result:> > ROOTNAME rootdbs > ROOTPATH /opt/zzz/dbspaces/rootdbs > ROOTOFFSET 0> # ROOTSIZE 200000 > ROOTSIZE 5000000> MIRROR 1 > #MIRRORPATH $INFORMIXDIR/tmp/demo_on.root_mirror > MIRRORPATH /opt/zzz/mdbspaces/rootdbs > > MIRROROFFSET 0 > > PHYSFILE 8304722 > PLOG_OVERFLOW_PATH /opt/informix/tmp > > PHYSBUFF 128 > > LOGFILES 17 > LOGSIZE 5000 > DYNAMIC_LOGS 0 > > LOGBUFF 128 > > LTXHWM 70 > > LTXEHWM 80> > MSGPATH /opt/zzz/online.log > > #CONSOLE $INFORMIXDIR/tmp/online.con > CONSOLE /opt/zzz/online.con > > TBLTBLFIRST 0 > TBLTBLNEXT 0 > > TBLSPACE_STATS 1> > #DBSPACETEMP tempdbs:tempdbs1:tempdbs2:tempdbs4 > DBSPACETEMP tempdbs1:tempdbs2:tempdbs3:tempdbs4 > > SBSPACETEMP > > SBSPACENAME sbspace > SYSSBSPACENAME > > ONDBSPACEDOWN 2 > > SERVERNUM 0 > DBSERVERNAME zzz> # DBSERVERALIASES dr_informix1170 > > FULL_DISK_INIT 0 > > NETTYPE ipcshm,1,50,CPU > NETTYPE soctcp,1,150,NET > LISTEN_TIMEOUT 60 > MAX_INCOMPLETE_CONNECTIONS 1024 > FASTPOLL 1> > NS_CACHE host=900,service=900,user=900,group=900 > > MULTIPROCESSOR 1 > VPCLASS cpu,num=15,noage > #VP_MEMORY_CACHE_KB 800 > #VP_MEMORY_CACHE_KB 262144 > VP_MEMORY_CACHE_KB 100000 > > SINGLE_CPU_VP 0> > #VPCLASS aio,num=1 > VPCLASS aio,num=10,noage > CLEANERS 8> AUTO_AIOVPS 1 > > DIRECT_IO 1> > #LOCKS 100000 > LOCKS 50000> > #DEF_TABLE_LOCKMODE page > DEF_TABLE_LOCKMODE row> > #RESIDENT 0 > #RESIDENT 1 > RESIDENT -1 > SHMBASE 0x44000000> #SHMVIRTSIZE 32656 > #SHMADD 8192 > #EXTSHMADD 8192 > SHMVIRTSIZE 35651584 > SHMADD 32768 > EXTSHMADD 32768> # SHMTOTAL 0 > # SHMTOTAL 15099494 > SHMTOTAL 48234496 > SHMVIRT_ALLOCSEG 0.000000> > SHMNOACCESS aio,num=10,noage > > CKPTINTVL 300> AUTO_CKPTS 1 > RTO_SERVER_RESTART 0 > > BLOCKTIMEOUT 3600 > > CONVERSION_GUARD 2> > RESTORE_POINT_DIR /opt/informix/tmp > > TXTIMEOUT 300 > DEADLOCK_TIMEOUT 60 > > HETERO_COMMIT 0 > > TAPEDEV /dev/tapedev > TAPEBLK 32 > > TAPESIZE 0 > > LTAPEDEV /dev/null > LTAPEBLK 32 > > LTAPESIZE 0> > BAR_ACT_LOG /opt/informix/tmp/bar_act.log > BAR_DEBUG_LOG /opt/informix/tmp/bar_dbug.log > BAR_DEBUG 0 > BAR_MAX_BACKUP 0 > BAR_RETRY 1 > BAR_NB_XPORT_COUNT 20 > BAR_XFER_BUF_SIZE 31 > RESTARTABLE_RESTORE on > BAR_PROGRESS_FREQ 0 > BAR_BSALIB_PATH > BACKUP_FILTER > RESTORE_FILTER > BAR_PERFORMANCE 0 > > BAR_CKPTSEC_TIMEOUT 15> > ISM_DATA_POOL ISMData > > ISM_LOG_POOL ISMLogs > > DD_HASHSIZE 31 > > DD_HASHMAX 10 > > DS_HASHSIZE 31 > > DS_POOLSIZE 127 > > PC_HASHSIZE 31 > PC_POOLSIZE 127> > #STMT_CACHE 0 > #STMT_CACHE 1 > STMT_CACHE 2 > STMT_CACHE_HITS 0 > #STMT_CACHE_SIZE 512 > STMT_CACHE_SIZE 2046 > STMT_CACHE_NOLIMIT 0> > #STMT_CACHE_NUMPOOL 1 > STMT_CACHE_NUMPOOL 256 > > USEOSTIME 0 > STACKSIZE 64 > ALLOW_NEWLINE 0 > > USELASTCOMMITTED NONE > > FILLFACTOR 90 > MAX_FILL_DATA_PAGES 0 > BTSCANNER num=1,threshold=5000,rangesize=-1,alice=6,compression=default > > ONLIDX_MAXMEM 5120 > > MAX_PDQPRIORITY 100 > #DS_MAX_QUERIES > DS_MAX_QUERIES 245760 > #DS_TOTAL_MEMORY > DS_TOTAL_MEMORY 33554432 > #DS_MAX_SCANS 1048576 > DS_MAX_SCANS 1048576 > #DS_NONPDQ_QUERY_MEM 128 > DS_NONPDQ_QUERY_MEM 7864320> > PSORT_NPROCS 4 > > DATASKIP off> > #OPTCOMPIND 2 > OPTCOMPIND 0 > DIRECTIVES 1 > EXT_DIRECTIVES 0 > OPT_GOAL -1 > #IFX_FOLDVIEW 0 > IFX_FOLDVIEW 1> AUTO_REPREPARE 1 > AUTO_STAT_MODE 1 > > STATCHANGE 10 > > RA_PAGES 128 > RA_THRESHOLD 120 > BATCHEDREAD_TABLE 1 > BATCHEDREAD_INDEX 1> > BATCHEDREAD_KEYONLY 0 > > #SQLTRACE level=low,ntraces=1000,size=2,mode=global > EXPLAIN_STAT 1 > SQLTRACE level=low,ntraces=1000,size=2,mode=global> > #DBCREATE_PERMISSION informix > #DB_LIBRARY_PATH > IFX_EXTEND_ROLE 1 > SECURITY_LOCALCONNECTION 0 > UNSECURE_ONSTAT 0 > ADMIN_USER_MODE_WITH_DBSA 0 > > PLCY_POOLSIZE 127 > PLCY_HASHSIZE 31 > USRC_POOLSIZE 127 > > USRC_HASHSIZE 31> > STAGEBLOB > > OPCACHEMAX 0 > > ENCRYPT_HDR 0 > ENCRYPT_SMX 0 > ENCRYPT_CDR 0 > ENCRYPT_CIPHERS > ENCRYPT_MAC medium > ENCRYPT_MACFILE> builtin,builtin,builtin,builtin,builtin,builtin,builtin,builtin,builtin,builtin, builtin,builtin,builtin,builtin,builtin,builtin,builtin,builtin,builtin,builtin, builtin,builtin,builtin,builtin,builtin,builtin,builtin,builtin > > ENCRYPT_SWITCH 0,0 > > CDR_EVALTHREADS 1,2 > CDR_DSLOCKWAIT 5 > CDR_QUEUEMEM 4096 > CDR_NIFCOMPRESS 0 > CDR_SERIAL 0,0 > CDR_DBSPACE > CDR_QHDR_DBSPACE > CDR_QDATA_SBSPACE > CDR_SUPPRESS_ATSRISWARN > CDR_LOG_LAG_ACTION ddrblock > CDR_LOG_STAGING_MAXSIZE 0 > > CDR_MAX_DYNAMIC_LOGS 0 > > DRAUTO 0 > DRINTERVAL 30 > DRTIMEOUT 30 > HA_ALIAS > DRLOSTFOUND /opt/informix/etc/dr.lostfound > DRIDXAUTO 0 > LOG_INDEX_BUILDS 0 > SDS_ENABLE 0 > SDS_TIMEOUT 20 > SDS_PAGING > UPDATABLE_SECONDARY 0 > FAILOVER_CALLBACK > FAILOVER_TX_TIMEOUT 0 > TEMPTAB_NOLOG 0 > ENABLE_SNAPSHOT_COPY 0 > > SMX_COMPRESS 0 > > ON_RECVRY_THREADS 1 > > OFF_RECVRY_THREADS 10> > DUMPDIR /opt/informix/tmp > DUMPSHMEM 1 > DUMPGCORE 0 > DUMPCORE 0> > DUMPCN

The metrics script package is ratios.shr_ak. You can download it from the IIUG Software Repository. The report that it produces displays five basic metrics which will tell you at a glance how well your server is performing. The suggested interpretations (described in the report output) assume an OLTP instance with some DSS style queries present. Your may also want to look at some of my performance tuning presentations from past International Informix Users Group conferences which you can download from the IIUG members' pages once you log in to the IIUG site. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Mar 4, 2013 at 7:04 AM, Alexandre Marini <alexandre@briug.org>wrote: > Ok it seems you have a big hardware, what´s your Informix version? What´s > your > box architecture? > > For performance tips, there is a good article in Andrew Ford´s blog, that > should clarify your thoughts.... > http://www.informix-dba.com/p/informix-innovator-c-tuning-basics.html > > This article, from , is very good, also: > > > http://www.ibm.com/developerworks/data/library/techarticle/dm-1104tuneinformix1/ index.html > > I´d suggest you to go through Art Kagel engine metrics, search into CDI > archives for newratios package, install it, and then you should have a good > "first steps" to follow.... > > If you have any specific doubt, we could help you further.... > > Good luck, and regards. > > Alexandre Marini > IBM Informix Certified Professional v10 / v11.50 / v11.70 > > IBM Information Management Informix Technical Professional > > IBM Infosphere DataStage Technical Professional > Informix Senior DBA - Orizon Brasil > BRIUG website administrator > Informix independent consultant > > > To: ids@iiug.org > > From: irfan199@yahoo.com > > Subject: Re: Performance Tuning Discussion [29665] > > Date: Mon, 4 Mar 2013 06:46:12 -0500 > > > > What are the tools to perform monitoring and tuning on the informix > instance? > > As far as I know I've been using onstat - (lots of options) but I doesn't > seem > > to enable to interpret all the information. > > > > My goals is to get Nice DSS Performance. For OLTP function it is > necessary > to > > load big data. > > > > onstat -c result:> > > > ROOTNAME rootdbs > > ROOTPATH /opt/zzz/dbspaces/rootdbs > > ROOTOFFSET 0> > # ROOTSIZE 200000 > > ROOTSIZE 5000000> > MIRROR 1 > > #MIRRORPATH $INFORMIXDIR/tmp/demo_on.root_mirror > > MIRRORPATH /opt/zzz/mdbspaces/rootdbs > > > > MIRROROFFSET 0 > > > > PHYSFILE 8304722 > > PLOG_OVERFLOW_PATH /opt/informix/tmp > > > > PHYSBUFF 128 > > > > LOGFILES 17 > > LOGSIZE 5000 > > DYNAMIC_LOGS 0 > > > > LOGBUFF 128 > > > > LTXHWM 70 > > > > LTXEHWM 80> > > > MSGPATH /opt/zzz/online.log > > > > #CONSOLE $INFORMIXDIR/tmp/online.con > > CONSOLE /opt/zzz/online.con > > > > TBLTBLFIRST 0 > > TBLTBLNEXT 0 > > > > TBLSPACE_STATS 1> > > > #DBSPACETEMP tempdbs:tempdbs1:tempdbs2:tempdbs4 > > DBSPACETEMP tempdbs1:tempdbs2:tempdbs3:tempdbs4 > > > > SBSPACETEMP > > > > SBSPACENAME sbspace > > SYSSBSPACENAME > > > > ONDBSPACEDOWN 2 > > > > SERVERNUM 0 > > DBSERVERNAME zzz> > # DBSERVERALIASES dr_informix1170 > > > > FULL_DISK_INIT 0 > > > > NETTYPE ipcshm,1,50,CPU > > NETTYPE soctcp,1,150,NET > > LISTEN_TIMEOUT 60 > > MAX_INCOMPLETE_CONNECTIONS 1024 > > FASTPOLL 1> > > > NS_CACHE host=900,service=900,user=900,group=900 > > > > MULTIPROCESSOR 1 > > VPCLASS cpu,num=15,noage > > #VP_MEMORY_CACHE_KB 800 > > #VP_MEMORY_CACHE_KB 262144 > > VP_MEMORY_CACHE_KB 100000 > > > > SINGLE_CPU_VP 0> > > > #VPCLASS aio,num=1 > > VPCLASS aio,num=10,noage > > CLEANERS 8> > AUTO_AIOVPS 1 > > > > DIRECT_IO 1> > > > #LOCKS 100000 > > LOCKS 50000> > > > #DEF_TABLE_LOCKMODE page > > DEF_TABLE_LOCKMODE row> > > > #RESIDENT 0 > > #RESIDENT 1 > > RESIDENT -1 > > SHMBASE 0x44000000> > #SHMVIRTSIZE 32656 > > #SHMADD 8192 > > #EXTSHMADD 8192 > > SHMVIRTSIZE 35651584 > > SHMADD 32768 > > EXTSHMADD 32768> > # SHMTOTAL 0 > > # SHMTOTAL 15099494 > > SHMTOTAL 48234496 > > SHMVIRT_ALLOCSEG 0.000000> > > > SHMNOACCESS aio,num=10,noage > > > > CKPTINTVL 300> > AUTO_CKPTS 1 > > RTO_SERVER_RESTART 0 > > > > BLOCKTIMEOUT 3600 > > > > CONVERSION_GUARD 2> > > > RESTORE_POINT_DIR /opt/informix/tmp > > > > TXTIMEOUT 300 > > DEADLOCK_TIMEOUT 60 > > > > HETERO_COMMIT 0 > > > > TAPEDEV /dev/tapedev > > TAPEBLK 32 > > > > TAPESIZE 0 > > > > LTAPEDEV /dev/null > > LTAPEBLK 32 > > > > LTAPESIZE 0> > > > BAR_ACT_LOG /opt/informix/tmp/bar_act.log > > BAR_DEBUG_LOG /opt/informix/tmp/bar_dbug.log > > BAR_DEBUG 0 > > BAR_MAX_BACKUP 0 > > BAR_RETRY 1 > > BAR_NB_XPORT_COUNT 20 > > BAR_XFER_BUF_SIZE 31 > > RESTARTABLE_RESTORE on > > BAR_PROGRESS_FREQ 0 > > BAR_BSALIB_PATH > > BACKUP_FILTER > > RESTORE_FILTER > > BAR_PERFORMANCE 0 > > > > BAR_CKPTSEC_TIMEOUT 15> > > > ISM_DATA_POOL ISMData > > > > ISM_LOG_POOL ISMLogs > > > > DD_HASHSIZE 31 > > > > DD_HASHMAX 10 > > > > DS_HASHSIZE 31 > > > > DS_POOLSIZE 127 > > > > PC_HASHSIZE 31 > > PC_POOLSIZE 127> > > > #STMT_CACHE 0 > > #STMT_CACHE 1 > > STMT_CACHE 2 > > STMT_CACHE_HITS 0 > > #STMT_CACHE_SIZE 512 > > STMT_CACHE_SIZE 2046 > > STMT_CACHE_NOLIMIT 0> > > > #STMT_CACHE_NUMPOOL 1 > > STMT_CACHE_NUMPOOL 256 > > > > USEOSTIME 0 > > STACKSIZE 64 > > ALLOW_NEWLINE 0 > > > > USELASTCOMMITTED NONE > > > > FILLFACTOR 90 > > MAX_FILL_DATA_PAGES 0 > > BTSCANNER num=1,threshold=5000,rangesize=-1,alice=6,compression=default > > > > ONLIDX_MAXMEM 5120 > > > > MAX_PDQPRIORITY 100 > > #DS_MAX_QUERIES > > DS_MAX_QUERIES 245760 > > #DS_TOTAL_MEMORY > > DS_TOTAL_MEMORY 33554432 > > #DS_MAX_SCANS 1048576 > > DS_MAX_SCANS 1048576 > > #DS_NONPDQ_QUERY_MEM 128 > > DS_NONPDQ_QUERY_MEM 7864320> > > > PSORT_NPROCS 4 > > > > DATASKIP off> > > > #OPTCOMPIND 2 > > OPTCOMPIND 0 > > DIRECTIVES 1 > > EXT_DIRECTIVES 0 > > OPT_GOAL -1 > > #IFX_FOLDVIEW 0 > > IFX_FOLDVIEW 1> > AUTO_REPREPARE 1 > > AUTO_STAT_MODE 1 > > > > STATCHANGE 10 > > > > RA_PAGES 128 > > RA_THRESHOLD 120 > > BATCHEDREAD_TABLE 1 > > BATCHEDREAD_INDEX 1> > > > BATCHEDREAD_KEYONLY 0 > > > > #SQLTRA