What are best onconfig parameters for a DSS app

1. Informix 11.70.TC4DE, Windows Vista with Dual Core Processor, 8GB RAM, 1TB HDD: 2. DSS app for querying a 200MB table, joining to a couple of fact tables. 3. Mainly aggregate and time series queries against a transaction history table. 4. Only one user will be submitting queries. 5. No updates or deletes will be done. 6. No inserts, other than the initial load of the target and fact tables. During the installation of this server, I specified it was going to be used for a data warehousing application. The install script generated an onconfig parameter file, based on my supplied answers. Can any of these parameters be changed to maximize the performance of the server? #(onconfig.ol_informix1170) - for data warehousing app. ROOTNAME rootdbs ROOTPATH C:\\\\PROGRA~1\\\\IBM\\\\Informix\\\\11.70\\\\OL_INF~2\\\\dbspaces\\\\rootdbs.000 ROOTOFFSET 0 ROOTSIZE 212992MIRROR 0 MIRRORPATH MIRROROFFSET 0 PHYSFILE 49152 PLOG_OVERFLOW_PATH PHYSBUFF 512 LOGFILES 6 LOGSIZE 10000 DYNAMIC_LOGS 2 LOGBUFF 256 LTXHWM 70 LTXEHWM 80 MSGPATH C:\\\\PROGRA~1\\\\IBM\\\\Informix\\\\11.70\\\\ol_informix1170_1.log CONSOLE C:\\\\PROGRA~1\\\\IBM\\\\Informix\\\\11.70\\\\ol_informix1170_1.con TBLTBLFIRST 0 TBLTBLNEXT 0 TBLSPACE_STATS 1 DBSPACETEMP tempdbs SBSPACETEMP SBSPACENAME sbspace SYSSBSPACENAME ONDBSPACEDOWN 2 SERVERNUM 6 DBSERVERNAME ol_informix1170_1 DBSERVERALIASES dr_informix1170_1 NETTYPE olsoctcp,1,150,NET LISTEN_TIMEOUT 60 MAX_INCOMPLETE_CONNECTIONS 1024 FASTPOLL 1 NS_CACHE host=900,service=900,user=900,group=900 MULTIPROCESSOR 0 VPCLASS cpu,num=1,noage VP_MEMORY_CACHE_KB 0 SINGLE_CPU_VP 1 #VPCLASS aio,num=1 CLEANERS 2AUTO_AIOVPS 1 DIRECT_IO 0 LOCKS 2000 DEF_TABLE_LOCKMODE page RESIDENT 0 SHMBASE 0xc000000L SHMVIRTSIZE 209920 SHMADD 6560 EXTSHMADD 8192 SHMTOTAL 0 SHMVIRT_ALLOCSEG 0,3#SHMNOACCESS 0x70000000-0x7FFFFFFF CKPTINTVL 300AUTO_CKPTS 1 RTO_SERVER_RESTART 60 BLOCKTIMEOUT 3600 CONVERSION_GUARD 2 RESTORE_POINT_DIR $INFORMIXDIR\\\\tmp TXTIMEOUT 300 DEADLOCK_TIMEOUT 60 HETERO_COMMIT 0 TAPEDEV \\\\\\\\.\\\\TAPE0 TAPEBLK 16 TAPESIZE 0 LTAPEDEV LTAPEBLK 16 LTAPESIZE 0 BAR_ACT_LOG $INFORMIXDIR\\\\tmp\\\\bar_act.log BAR_DEBUG_LOG $INFORMIXDIR\\\\tmp\\\\bar_dbug.log BAR_DEBUG 0 BAR_MAX_BACKUP 0 BAR_RETRY 1 BAR_NB_XPORT_COUNT 20 BAR_XFER_BUF_SIZE 15 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 PRELOAD_DLL_FILE STMT_CACHE 0 STMT_CACHE_HITS 0 STMT_CACHE_SIZE 512 STMT_CACHE_NOLIMIT 0 STMT_CACHE_NUMPOOL 1 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 188928 MAX_PDQPRIORITY 100 DS_MAX_QUERIES 1 DS_TOTAL_MEMORY 188928 DS_MAX_SCANS 1 DS_NONPDQ_QUERY_MEM 188928 DATASKIP OPTCOMPIND 2 DIRECTIVES 1 EXT_DIRECTIVES 0 OPT_GOAL -1 IFX_FOLDVIEW 0AUTO_REPREPARE 1 USTLOW_SAMPLE 0 RA_PAGES 64 RA_THRESHOLD 16 BATCHEDREAD_TABLE 1 BATCHEDREAD_INDEX 1BATCHEDREAD_KEYONLY 0 EXPLAIN_STAT 1 #SQLTRACE level=low,ntraces=1000,size=2,mode=global #DBCREATE_PERMISSION informix #DB_LIBRARY_PATH IFX_EXTEND_ROLE 1 SECURITY_LOCALCONNECTION UNSECURE_ONSTAT ADMIN_USER_MODE_WITH_DBSA ADMIN_MODE_USERS PLCY_POOLSIZE 127 PLCY_HASHSIZE 31 USRC_POOLSIZE 127 USRC_HASHSIZE 31 STAGEBLOB OPCACHEMAX 0 SQL_LOGICAL_CHAR OFF SEQ_CACHE_SIZE 10 ENCRYPT_HDR ENCRYPT_SMX ENCRYPT_CDR 0 ENCRYPT_CIPHERS ENCRYPT_MAC ENCRYPT_MACFILE ENCRYPT_SWITCH CDR_EVALTHREADS 1,2 CDR_DSLOCKWAIT 5 CDR_QUEUEMEM 4096 CDR_NIFCOMPRESS 0 CDR_SERIAL 0 CDR_DBSPACE CDR_QHDR_DBSPACE CDR_QDATA_SBSPACE CDR_SUPPRESS_ATSRISWARN CDR_DELAY_PURGE_DTC 0 CDR_LOG_LAG_ACTION ddrblock CDR_LOG_STAGING_MAXSIZE 0 CDR_MAX_DYNAMIC_LOGS 0 DRAUTO 0 DRINTERVAL 30 DRTIMEOUT 30 HA_ALIAS DRLOSTFOUND $INFORMIXDIR\\\\etc\\\\dr.lostfound DRIDXAUTO 0 LOG_INDEX_BUILDS SDS_ENABLE SDS_TIMEOUT 20 SDS_TEMPDBS SDS_PAGING SDS_LOGCHECK 0 UPDATABLE_SECONDARY 0 FAILOVER_CALLBACK FAILOVER_TX_TIMEOUT 0 TEMPTAB_NOLOG 0 DELAY_APPLY 0 STOP_APPLY 0 LOG_STAGING_DIR RSS_FLOW_CONTROL 0 ENABLE_SNAPSHOT_COPY 0 SMX_COMPRESS 0 ON_RECVRY_THREADS 2 OFF_RECVRY_THREADS 5 DUMPDIR $INFORMIXDIR\\\\tmp DUMPSHMEM 1 DUMPGCORE 0 DUMPCORE 0 DUMPCNT 1 ALARMPROGRAM $INFORMIXDIR\\\\etc\\\\alarmprogram.bat ALRM_ALL_EVENTS 0 #SYSALARMPROGRAM $INFORMIXDIR\\\\etc\\\\evidence.bat STORAGE_FULL_ALARM 600,3 RAS_PLOG_SPEED 10982 RAS_LLOG_SPEED 0 EILSEQ_COMPAT_MODE 0 QSTATS 0 WSTATS 0 #VPCLASS MQ,noyield MQSERVER MQCHLLIB MQCHLTAB #VPCLASS jvp,num=1 #JVPJAVAHOME $INFORMIXDIR\\\\extend\\\\krakatoa\\\\jre #JVPHOME $INFORMIXDIR\\\\extend\\\\krakatoa JVPPROPFILE $INFORMIXDIR\\\\extend\\\\krakatoa\\\\.jvpprops JVPLOGFILE $INFORMIXDIR\\\\jvp.log #JDKVERSION 1.5 #JVPJAVALIB \\\\bin #JVPJAVAVM jvm #JVPARGS -verbose:jni #JVPCLASSPATH $INFORMIXDIR\\\\extend\\\\krakatoa\\\\krakatoa_g.jar;$INFORMIXDIR\\\\extend\\\\krakatoa\\\\jdbc_g. jar JVPARGS -Dcom.ibm.tools.attach.enable=no JVPCLASSPATH $INFORMIXDIR\\\\extend\\\\krakatoa\\\\krakatoa.jar;$INFORMIXDIR\\\\extend\\\\krakatoa\\\\jdbc.jar BUFFERPOOL default,buffers=10000,lrus=8,lru_min_dirty=50.00,lru_max_dirty=60.50 BUFFERPOOL size=4K,buffers=13108,lrus=16,lru_min_dirty=70.00,lru_max_dirty=80.00 AUTO_LRU_TUNING 1 USERMAPPING OFF SP_AUTOEXPAND 1 SP_THRESHOLD 0 SP_WAITTIME 30 DEFAULTESCCHAR \\\\ LOW_MEMORY_RESERVE 0 LOW_MEMORY_MGR 0 REMOTE_SERVER_CFG REMOTE_USERS_CFG S6_USE_REMOTE_SERVER_CFG 0 GSKIT_VERSION NETTYPE drsoctcp,1,150,NET

Frank.... first question: Is your database installation being made for a production system???? Alexandre Marini IBM Informix Certified Professional v10 / v11.50 / v11.70 IBM Information Management Informix Technical Professional IBM Infosphere DataStage Technical Professional Database Administrator > To: ids@iiug.org > From: frank_in_pr@hotmail.com > Subject: What are best onconfig parameters for a DSS app [27024] > Date: Mon, 7 May 2012 17:12:10 -0400 > > 1. Informix 11.70.TC4DE, Windows Vista with Dual Core Processor, 8GB RAM, 1TB > HDD: > 2. DSS app for querying a 200MB table, joining to a couple of fact tables. > 3. Mainly aggregate and time series queries against a transaction history > table. > 4. Only one user will be submitting queries. > 5. No updates or deletes will be done. > 6. No inserts, other than the initial load of the target and fact tables. > > During the installation of this server, I specified it was going to be used > for a data warehousing application. The install script generated an onconfig > parameter file, based on my supplied answers. Can any of these parameters be > changed to maximize the performance of the server? > > #(onconfig.ol_informix1170) - for data warehousing app. > > ROOTNAME rootdbs > ROOTPATH C:\\\\PROGRA~1\\\\IBM\\\\Informix\\\\11.70\\\\OL_INF~2\\\\dbspaces\\\\rootdbs.000 > ROOTOFFSET 0 > ROOTSIZE 212992> MIRROR 0 > MIRRORPATH > MIRROROFFSET 0 > > PHYSFILE 49152 > PLOG_OVERFLOW_PATH > PHYSBUFF 512 > > LOGFILES 6 > LOGSIZE 10000 > DYNAMIC_LOGS 2 > LOGBUFF 256 > > LTXHWM 70 > LTXEHWM 80> > MSGPATH C:\\\\PROGRA~1\\\\IBM\\\\Informix\\\\11.70\\\\ol_informix1170_1.log > CONSOLE C:\\\\PROGRA~1\\\\IBM\\\\Informix\\\\11.70\\\\ol_informix1170_1.con > > TBLTBLFIRST 0 > TBLTBLNEXT 0 > TBLSPACE_STATS 1 > > DBSPACETEMP tempdbs > SBSPACETEMP > > SBSPACENAME sbspace > SYSSBSPACENAME > ONDBSPACEDOWN 2 > > SERVERNUM 6 > DBSERVERNAME ol_informix1170_1 > DBSERVERALIASES dr_informix1170_1 > > NETTYPE olsoctcp,1,150,NET > LISTEN_TIMEOUT 60 > MAX_INCOMPLETE_CONNECTIONS 1024 > FASTPOLL 1 > NS_CACHE host=900,service=900,user=900,group=900 > > MULTIPROCESSOR 0 > VPCLASS cpu,num=1,noage > VP_MEMORY_CACHE_KB 0 > SINGLE_CPU_VP 1> > #VPCLASS aio,num=1 > CLEANERS 2> AUTO_AIOVPS 1 > DIRECT_IO 0 > > LOCKS 2000 > DEF_TABLE_LOCKMODE page > > RESIDENT 0 > SHMBASE 0xc000000L > SHMVIRTSIZE 209920 > SHMADD 6560 > EXTSHMADD 8192 > SHMTOTAL 0 > SHMVIRT_ALLOCSEG 0,3> #SHMNOACCESS 0x70000000-0x7FFFFFFF > > CKPTINTVL 300> AUTO_CKPTS 1 > RTO_SERVER_RESTART 60 > > BLOCKTIMEOUT 3600 > > CONVERSION_GUARD 2 > RESTORE_POINT_DIR $INFORMIXDIR\\\\tmp > > TXTIMEOUT 300 > DEADLOCK_TIMEOUT 60 > > HETERO_COMMIT 0 > > TAPEDEV \\\\\\\\.\\\\TAPE0 > TAPEBLK 16 > TAPESIZE 0 > > LTAPEDEV > LTAPEBLK 16 > LTAPESIZE 0> > BAR_ACT_LOG $INFORMIXDIR\\\\tmp\\\\bar_act.log > BAR_DEBUG_LOG $INFORMIXDIR\\\\tmp\\\\bar_dbug.log > BAR_DEBUG 0 > BAR_MAX_BACKUP 0 > BAR_RETRY 1 > BAR_NB_XPORT_COUNT 20 > BAR_XFER_BUF_SIZE 15 > 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 > PRELOAD_DLL_FILE > > STMT_CACHE 0 > STMT_CACHE_HITS 0 > STMT_CACHE_SIZE 512 > STMT_CACHE_NOLIMIT 0 > STMT_CACHE_NUMPOOL 1 > > 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 188928 > > MAX_PDQPRIORITY 100 > DS_MAX_QUERIES 1 > DS_TOTAL_MEMORY 188928 > DS_MAX_SCANS 1 > DS_NONPDQ_QUERY_MEM 188928 > DATASKIP > > OPTCOMPIND 2 > DIRECTIVES 1 > EXT_DIRECTIVES 0 > OPT_GOAL -1 > IFX_FOLDVIEW 0> AUTO_REPREPARE 1 > USTLOW_SAMPLE 0 > > RA_PAGES 64 > RA_THRESHOLD 16 > BATCHEDREAD_TABLE 1 > BATCHEDREAD_INDEX 1> BATCHEDREAD_KEYONLY 0 > > EXPLAIN_STAT 1 > #SQLTRACE level=low,ntraces=1000,size=2,mode=global> > #DBCREATE_PERMISSION informix > #DB_LIBRARY_PATH > IFX_EXTEND_ROLE 1 > SECURITY_LOCALCONNECTION > UNSECURE_ONSTAT > ADMIN_USER_MODE_WITH_DBSA > ADMIN_MODE_USERS > > PLCY_POOLSIZE 127 > PLCY_HASHSIZE 31 > USRC_POOLSIZE 127 > > USRC_HASHSIZE 31> > STAGEBLOB > OPCACHEMAX 0 > > SQL_LOGICAL_CHAR OFF > > SEQ_CACHE_SIZE 10 > > ENCRYPT_HDR > ENCRYPT_SMX > ENCRYPT_CDR 0 > ENCRYPT_CIPHERS > ENCRYPT_MAC > ENCRYPT_MACFILE > ENCRYPT_SWITCH > > CDR_EVALTHREADS 1,2 > CDR_DSLOCKWAIT 5 > CDR_QUEUEMEM 4096 > CDR_NIFCOMPRESS 0 > CDR_SERIAL 0 > CDR_DBSPACE > CDR_QHDR_DBSPACE > CDR_QDATA_SBSPACE > CDR_SUPPRESS_ATSRISWARN > CDR_DELAY_PURGE_DTC 0 > CDR_LOG_LAG_ACTION ddrblock > CDR_LOG_STAGING_MAXSIZE 0 > CDR_MAX_DYNAMIC_LOGS 0 > > DRAUTO 0 > DRINTERVAL 30 > DRTIMEOUT 30 > HA_ALIAS > DRLOSTFOUND $INFORMIXDIR\\\\etc\\\\dr.lostfound > DRIDXAUTO 0 > LOG_INDEX_BUILDS > SDS_ENABLE > SDS_TIMEOUT 20 > SDS_TEMPDBS > SDS_PAGING > SDS_LOGCHECK 0 > UPDATABLE_SECONDARY 0 > FAILOVER_CALLBACK > FAILOVER_TX_TIMEOUT 0 > TEMPTAB_NOLOG 0 > DELAY_APPLY 0 > STOP_APPLY 0 > LOG_STAGING_DIR > RSS_FLOW_CONTROL 0 > ENABLE_SNAPSHOT_COPY 0 > SMX_COMPRESS 0 > > ON_RECVRY_THREADS 2 > OFF_RECVRY_THREADS 5 > > DUMPDIR $INFORMIXDIR\\\\tmp > DUMPSHMEM 1 > DUMPGCORE 0 > DUMPCORE 0 > > DUMPCNT 1> > ALARMPROGRAM $INFORMIXDIR\\\\etc\\\\alarmprogram.bat > ALRM_ALL_EVENTS 0 > #SYSALARMPROGRAM $INFORMIXDIR\\\\etc\\\\evidence.bat > STORAGE_FULL_ALARM 600,3 > > RAS_PLOG_SPEED 10982 > RAS_LLOG_SPEED 0 > > EILSEQ_COMPAT_MODE 0 > > QSTATS 0 > WSTATS 0> > #VPCLASS MQ,noyield > MQSERVER > MQCHLLIB > > MQCHLTAB> > #VPCLASS jvp,num=1 > #JVPJAVAHOME $INFORMIXDIR\\\\extend\\\\krakatoa\\\\jre > #JVPHOME $INFORMIXDIR\\\\extend\\\\krakatoa > JVPPROPFILE $INFORMIXDIR\\\\extend\\\\krakatoa\\\\.jvpprops > JVPLOGFILE $INFORMIXDIR\\\\jvp.log > #JDKVERSION 1.5 > #JVPJAVALIB \\\\bin > #JVPJAVAVM jvm > #JVPARGS -verbose:jni > #JVPCLASSPATH > $INFORMIXDIR\\\\extend\\\\krakatoa\\\\krakatoa_g.jar;$INFORMIXDIR\\\\extend\\\\krakatoa\\\\jdbc_g. jar > JVPARGS -Dcom.ibm.tools.attach.enable=no > JVPCLASSPATH> $INFORMIXDIR\\\\extend\\\\krakatoa\\\\krakatoa.jar;$INFORMIXDIR\\\\extend\\\\krakatoa\\\\jdbc.jar > > BUFFERPOOL > default,buffers=10000,lrus=8,lru_min_dirty=50.00,lru_max_dirty=60.50 > BUFFERPOOL > size=4K,buffers=13108,lrus=16,lru_min_dirty=70.00,lru_max_dirty=80.00 > AUTO_LRU_TUNING 1 > > USERMAPPING OFF > > SP_AUTOEXPAND 1 > SP_THRESHOLD 0 > SP_WAITTIME 3

No, a user sent me their transaction history file and I am evaluating 1170 for Windows so that I can avoid having to do the aggregate and temporal DSS queries within an MS-DOS SE4.10 database I created, separately from their production database. So far I've noticed a dramatic increase in performance with 1170, but there seems to be a problem with starting up the 1170 server, after the instance was successfully closed with onshutdown. In DBACCESS, it displays "Running..." but hangs when trying to connect.

That's tough to do in a vacuum without seeing how the server is performing in-situ or at least historically. However, there are a couple of glaring items. 1. If your server has 8GB or memory and the database's main table for querying is 200MB, the a buffer cache of ~13000 4K pages is a bit small for DSS style queries, so: BUFFERPOOL size=4K,buffers=55000,lrus=16,lru_min_dirty=70.00,lru_max_dirty=80.00 2. You have DS_TOTAL_MEMORY set to 188928 and also DS_NONPDQ_QUERY_MEM set to 188928, but the latter is limited to 25% of the former, so either increase DS_TOTAL_MEMORY or reduce the other. Since this is for DSS queries, I'd suggest increasing DS_TOTAL_MEMORY to 1024000 3. #2 above will require you to increase SHM_VIRTSIZE to at least 1248000 4. Since you have more than a single CPU core MULTIPROCESSOR must be set to 1 to avoid memory corruption. 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, May 7, 2012 at 5:12 PM, FRANK J. COMPUTER <frank_in_pr@hotmail.com>wrote: > 1. Informix 11.70.TC4DE, Windows Vista with Dual Core Processor, 8GB RAM, > 1TB > HDD: > 2. DSS app for querying a 200MB table, joining to a couple of fact tables. > 3. Mainly aggregate and time series queries against a transaction history > table. > 4. Only one user will be submitting queries. > 5. No updates or deletes will be done. > 6. No inserts, other than the initial load of the target and fact tables. > > During the installation of this server, I specified it was going to be used > for a data warehousing application. The install script generated an > onconfig > parameter file, based on my supplied answers. Can any of these parameters > be > changed to maximize the performance of the server? > > #(onconfig.ol_informix1170) - for data warehousing app. > > ROOTNAME rootdbs > ROOTPATH C:\\\\PROGRA~1\\\\IBM\\\\Informix\\\\11.70\\\\OL_INF~2\\\\dbspaces\\\\rootdbs.000 > ROOTOFFSET 0 > ROOTSIZE 212992> MIRROR 0 > MIRRORPATH > MIRROROFFSET 0 > > PHYSFILE 49152 > PLOG_OVERFLOW_PATH > PHYSBUFF 512 > > LOGFILES 6 > LOGSIZE 10000 > DYNAMIC_LOGS 2 > LOGBUFF 256 > > LTXHWM 70 > LTXEHWM 80> > MSGPATH C:\\\\PROGRA~1\\\\IBM\\\\Informix\\\\11.70\\\\ol_informix1170_1.log > CONSOLE C:\\\\PROGRA~1\\\\IBM\\\\Informix\\\\11.70\\\\ol_informix1170_1.con > > TBLTBLFIRST 0 > TBLTBLNEXT 0 > TBLSPACE_STATS 1 > > DBSPACETEMP tempdbs > SBSPACETEMP > > SBSPACENAME sbspace > SYSSBSPACENAME > ONDBSPACEDOWN 2 > > SERVERNUM 6 > DBSERVERNAME ol_informix1170_1 > DBSERVERALIASES dr_informix1170_1 > > NETTYPE olsoctcp,1,150,NET > LISTEN_TIMEOUT 60 > MAX_INCOMPLETE_CONNECTIONS 1024 > FASTPOLL 1 > NS_CACHE host=900,service=900,user=900,group=900 > > MULTIPROCESSOR 0 > VPCLASS cpu,num=1,noage > VP_MEMORY_CACHE_KB 0 > SINGLE_CPU_VP 1> > #VPCLASS aio,num=1 > CLEANERS 2> AUTO_AIOVPS 1 > DIRECT_IO 0 > > LOCKS 2000 > DEF_TABLE_LOCKMODE page > > RESIDENT 0 > SHMBASE 0xc000000L > SHMVIRTSIZE 209920 > SHMADD 6560 > EXTSHMADD 8192 > SHMTOTAL 0 > SHMVIRT_ALLOCSEG 0,3> #SHMNOACCESS 0x70000000-0x7FFFFFFF > > CKPTINTVL 300> AUTO_CKPTS 1 > RTO_SERVER_RESTART 60 > > BLOCKTIMEOUT 3600 > > CONVERSION_GUARD 2 > RESTORE_POINT_DIR $INFORMIXDIR\\\\tmp > > TXTIMEOUT 300 > DEADLOCK_TIMEOUT 60 > > HETERO_COMMIT 0 > > TAPEDEV \\\\\\\\.\\\\TAPE0 > TAPEBLK 16 > TAPESIZE 0 > > LTAPEDEV > LTAPEBLK 16 > LTAPESIZE 0> > BAR_ACT_LOG $INFORMIXDIR\\\\tmp\\\\bar_act.log > BAR_DEBUG_LOG $INFORMIXDIR\\\\tmp\\\\bar_dbug.log > BAR_DEBUG 0 > BAR_MAX_BACKUP 0 > BAR_RETRY 1 > BAR_NB_XPORT_COUNT 20 > BAR_XFER_BUF_SIZE 15 > 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 > PRELOAD_DLL_FILE > > STMT_CACHE 0 > STMT_CACHE_HITS 0 > STMT_CACHE_SIZE 512 > STMT_CACHE_NOLIMIT 0 > STMT_CACHE_NUMPOOL 1 > > 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 188928 > > MAX_PDQPRIORITY 100 > DS_MAX_QUERIES 1 > DS_TOTAL_MEMORY 188928 > DS_MAX_SCANS 1 > DS_NONPDQ_QUERY_MEM 188928 > DATASKIP > > OPTCOMPIND 2 > DIRECTIVES 1 > EXT_DIRECTIVES 0 > OPT_GOAL -1 > IFX_FOLDVIEW 0> AUTO_REPREPARE 1 > USTLOW_SAMPLE 0 > > RA_PAGES 64 > RA_THRESHOLD 16 > BATCHEDREAD_TABLE 1 > BATCHEDREAD_INDEX 1> BATCHEDREAD_KEYONLY 0 > > EXPLAIN_STAT 1 > #SQLTRACE level=low,ntraces=1000,size=2,mode=global> > #DBCREATE_PERMISSION informix > #DB_LIBRARY_PATH > IFX_EXTEND_ROLE 1 > SECURITY_LOCALCONNECTION > UNSECURE_ONSTAT > ADMIN_USER_MODE_WITH_DBSA > ADMIN_MODE_USERS > > PLCY_POOLSIZE 127 > PLCY_HASHSIZE 31 > USRC_POOLSIZE 127 > > USRC_HASHSIZE 31> > STAGEBLOB > OPCACHEMAX 0 > > SQL_LOGICAL_CHAR OFF > > SEQ_CACHE_SIZE 10 > > ENCRYPT_HDR > ENCRYPT_SMX > ENCRYPT_CDR 0 > ENCRYPT_CIPHERS > ENCRYPT_MAC > ENCRYPT_MACFILE > ENCRYPT_SWITCH > > CDR_EVALTHREADS 1,2 > CDR_DSLOCKWAIT 5 > CDR_QUEUEMEM 4096 > CDR_NIFCOMPRESS 0 > CDR_SERIAL 0 > CDR_DBSPACE > CDR_QHDR_DBSPACE > CDR_QDATA_SBSPACE > CDR_SUPPRESS_ATSRISWARN > CDR_DELAY_PURGE_DTC 0 > CDR_LOG_LAG_ACTION ddrblock > CDR_LOG_STAGING_MAXSIZE 0 > CDR_MAX_DYNAMIC_LOGS 0 > > DRAUTO 0 > DRINTERVAL 30 > DRTIMEOUT 30 > HA_ALIAS > DRLOSTFOUND $INFORMIXDIR\\\\etc\\\\dr.lostfound > DRIDXAUTO 0 > LOG_INDEX_BUILDS > SDS_ENABLE > SDS_TIMEOUT 20 > SDS_TEMPDBS > SDS_PAGING > SDS_LOGCHECK 0 > UPDATABLE_SECONDARY 0 > FAILOVER_CALLBACK > FAILOVER_TX_TIMEOUT 0 > TEMPTAB_NOLOG 0 > DELAY_APPLY 0 > STOP_APPLY 0 > LOG_STAGING_DIR > RSS_FLOW_CONTROL 0 > ENABLE_SNAPSHOT_COPY 0 > SMX_COMPRESS 0 > > ON_RECVRY_THREADS 2 > OFF_RECVRY_THREADS 5 > > DUMPDIR $INFORMIXDIR\\\\tmp > DUMPSHMEM 1 > DUMPGCORE 0 > DUMPCORE 0 > > DUMPCNT 1> > ALARMPROGRAM $INFORMIXDIR\\\\etc\\\\alarmprogram.bat > ALRM_ALL_EVENTS 0 > #SYSALARMPROGRAM $INFORMIXDIR\\\\etc\\\\evidence.bat > STORAGE_FULL_ALARM 600,3 > > RAS_PLOG_SPEED 1

OK, thank you for all your advice! Now let's see if after I delete the instance, create a new one and reload the data, I can conduct my tests. Perhaps I should never shutdown the server in order to avoid the re-connect problem?

Also, since you will be alone on the machine, set the PDQPRIORITY environment variable in setnet32 to 100 giving you 100% of the server's resources and enabling full parallel query features. 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, May 7, 2012 at 6:45 PM, FRANK J. COMPUTER <frank_in_pr@hotmail.com>wrote: > OK, thank you for all your advice! Now let's see if after I delete the > instance, create a new one and reload the data, I can conduct my tests. > > Perhaps I should never shutdown the server in order to avoid the re-connect > problem? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae9340e21d2c9d504bf7a12d9

OK, so I've made your suggested changes to onconfig. Now, since my app is only does queries, is it possible for me to disable transaction logging and checkpoints? Also: 1. What about enabling DIRECT_IO, or is that feature only available for UX raw disk partitions? 2. Do I need to keep locks at 2000, or can I do with less? 3. Will I need PDQ's if I'm the only user submitting queries?.. Remember, this is a single user app. 4. Should I change the way the query optimizer determines the best path?.. e.g. OPT_GOAL, OPTCOMIND, etc.? 5. I didn't see the AUTO_RA_PAGES parameter you mentioned in your reply. 6. Since I am the only single-user (ADMIN) who will run these queries, can I disable permissions, replication or any other non-relevant multi-user parameters?.. Would that make performance faster? 7. I would like to get the best possible diagnostics (with no tracing) to assist me in fine-tuning this app. Should I change any SQLEXPLAIN or QSTATS parms? 8. I'm not using any Java, ESQL or other API's. I'm just loading the data into this server and doing single-user DSS queries. Can I disable any of these API's? 9. There's also no networking with this server. Can I disable LISTENERS, TCP/IP, etc.? 10. Any other parms you can think of to optimize this app?.. I'm pretty sure that 200MB's is not a performance issue for 1170, but in the future, my table sizes could grow to 1GB+, and I also want to learn how to fine-tune DSS and OLTP apps as well!.. I've been stuck in the SE world of 20 years ago for too long and need to catch up on all of this.. thanks for your help!

On Mon, May 7, 2012 at 4:56 PM, FRANK J. COMPUTER <frank_in_pr@hotmail.com>wrote: > OK, so I've made your suggested changes to onconfig. Now, since my app is > only > does queries, is it possible for me to disable transaction logging and > checkpoints? > > You can convert the database to unlogged (though it may already be unlogged unless you created it WITH LOG or WITH BUFFERED LOG or WITH LOG MODE ANSI). This will disable transaction logging. You cannot stop checkpoints; they are used by the server to keep track of disk space allocations, even with unlogged databases. > Also: > > 1. What about enabling DIRECT_IO, or is that feature only available for UX > raw > disk partitions? > > 2. Do I need to keep locks at 2000, or can I do with less? > More, if anything. > 3. Will I need PDQ's if I'm the only user submitting queries?.. Remember, > this > is a single user app. > PDQ is for a single session doing a complex query. It allows your multi-core CPU to work on different parts of the query in parallel. You don't have to use it; it can be a big win for DSS queries if you have the other parts configured right. > 4. Should I change the way the query optimizer determines the best path?.. > e.g. OPT_GOAL, OPTCOMIND, etc.? > > 5. I didn't see the AUTO_RA_PAGES parameter you mentioned in your reply. > > 6. Since I am the only single-user (ADMIN) who will run these queries, can > I > disable permissions, replication or any other non-relevant multi-user > parameters?.. Would that make performance faster? > Permissions: no. Replication: they're ignored until you enable replication, so it is "doesn't matter". > 7. I would like to get the best possible diagnostics (with no tracing) to > assist me in fine-tuning this app. Should I change any SQLEXPLAIN or QSTATS > parms? > > 8. I'm not using any Java, ESQL or other API's. I'm just loading the data > into > this server and doing single-user DSS queries. Can I disable any of these > API's? > You're using one of the APIs just for loading and doing DSS queries. If you're using DB-Access, that is effectively an ESQL/C program. There's no real distinction in the server; just don't configure any DRDA connections unless you need them. > 9. There's also no networking with this server. Can I disable LISTENERS, > TCP/IP, etc.? > Even for a localhost, TCP is useful. I was running some tests this morning, and getting 35 seconds using loopback TCP vs 50 seconds for SHM. YMMV, of course. You need listeners; you don't need listeners for the protocols you don't configure. So, if you're going to use olipcshm connections exclusively, make sure you don't have a NETTYPE for soctcp or ipcstr. Etc. 10. Any other parms you can think of to optimize this app?.. I'm pretty sure > that 200MB's is not a performance issue for 1170, but in the future, my > table > sizes could grow to 1GB+, and I also want to learn how to fine-tune DSS and > OLTP apps as well!.. I've been stuck in the SE world of 20 years ago for > too > long and need to catch up on all of this.. thanks for your help! > 200 MB of data is an 'in memory database' if you configure your buffer pool correctly. Even 1 GiB of data could be 'in memory' on a typical modern PC. -- Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h> Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." --f46d0421ad616af3ac04bf7b262a

OK thanks! So then I can increase PDQ parms for hairy queries? If checkpoints are necessary, what is the impact of increasing the interval time so that it doesn't checkpoint more often?.. Jonathan, do you have any knowledge about the problem that Art and I were discussing about the Windows version of 1170 hanging up when trying to reconnect to it?.. He experienced the same problems and gave up on trying to fix it.

.. and where are all the Red Brick SQL definition statements like CREATE STAR INDEX and all the other DSS related statements?.. I didn't see it in DBACCESS' help screen. I thought 1170 had this included in it?

See below: 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, May 7, 2012 at 7:56 PM, FRANK J. COMPUTER <frank_in_pr@hotmail.com>wrote: > OK, so I've made your suggested changes to onconfig. Now, since my app is > only > does queries, is it possible for me to disable transaction logging and > checkpoints? > Logging yes. If your database was created with logging (see the sysmaster:sysdatabases record to find out if you are not sure) you can only turn logging off as part of a level 0 archive: ontape -s -L 0 -N <mydatabase> -t STDIO >/some/file/path Checkpoints can't be disabled, but if your database has no logging and/or you are not updating data, checkpoints will have nothing much to do (checkpoints don't block most work in 11.xx anyway). > > Also: > > 1. What about enabling DIRECT_IO, or is that feature only available for UX > raw > disk partitions? > Actually, directo_io only works for filesystem chunks on UNIX platforms (direct_io is an operating system feature and I don't think that windows supports it). > > 2. Do I need to keep locks at 2000, or can I do with less? > Locks only take up about 48 bytes each. The lock table is dynamic, so you could set it smaller and it will expand if needed, but as I say, there's little cost to lock. OLTP systems often configure up to a million of them. > > 3. Will I need PDQ's if I'm the only user submitting queries?.. Remember, > this > is a single user app. > Yes. Extra memory is allocated to complex queries only if PDQPRIORITY is >0 and parallel processing is only performed if it is set >1. Values over 1 are a percent of total DS_ resources, so set to 10 you would get up to 10% of the configured DS_TOTAL_MEMORY and 10% of the CPU VP's available (OK you only have one, but if the machine is fast enough you could configure more). > > 4. Should I change the way the query optimizer determines the best path?.. > e.g. OPT_GOAL, OPTCOMIND, etc.? > For DSS & DW style queries it is recommended to set OPTCOMPIND to 2, OPT_GOAL defaults to ALL_ROWS which is the appropriate setting. The FIRST_ROWS setting is for interactive applications that have to display the first <N> rows of a large result set as quickly as possible, even if the entire result set will take longer to produce completely. > > 5. I didn't see the AUTO_RA_PAGES parameter you mentioned in your reply. > 'cause it is called AUTO_READAHEAD. > > 6. Since I am the only single-user (ADMIN) who will run these queries, can > I > disable permissions, replication or any other non-relevant multi-user > parameters?.. Would that make performance faster? > MACH-11 replication is only active if you have a secondary server and Enterprise Replication is only active if the server is defined to be a node in an ER cluster. Privileges are always checked, nothing you can do about it. > > 7. I would like to get the best possible diagnostics (with no tracing) to > assist me in fine-tuning this app. Should I change any SQLEXPLAIN or QSTATS > parms? > QSTATS and WSTATS config parameters are for diagnostics purposes only. They add lots of overhead. You can run your queries initially with SET EXPLAIN ON; to see the query plan the engine has selected and possibly use the details in the sqexplain.out file to restructure the SQL, add indexes, partition the tables, etc. Note that any query that has more than 5 or 6 tables joined should probably be run with SET OPTIMIZATION LOW; since the costs of testing and costing every possible query plan (the default behavior) can be more than the difference between the runtime of the best query plan and one that is 3rd or 4th best. The number of query plans that have to be examined under the default HIGH optimization is <#tables> factorial while under low it is just SUM(1...<#tables-1>). > > 8. I'm not using any Java, ESQL or other API's. I'm just loading the data > into > this server and doing single-user DSS queries. Can I disable any of these > API's? > > 9. There's also no networking with this server. Can I disable LISTENERS, > TCP/IP, etc.? > Not sure if Windows supports shared memory connections but it might support named pipe connection types. You would have to modify the NETTYPE onconfig parameters and the sqlhosts file. > > 10. Any other parms you can think of to optimize this app?.. I'm pretty > sure > that 200MB's is not a performance issue for 1170, but in the future, my > table > sizes could grow to 1GB+, and I also want to learn how to fine-tune DSS and > OLTP apps as well!.. I've been stuck in the SE world of 20 years ago for > too > long and need to catch up on all of this.. thanks for your help! > Go to the IIUG web site on the members' pages and download some of the conference presentations on optimizing and tuning the server. There are a couple of mine on the subject and several from other users and from IBMers. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae9340d59cc353604bf7bee23

Don't worry about checkpoints. If you are only doing DSS/DW queries on an unlogged database they will only last a fraction of a second and do only a handful of IOs. 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, May 7, 2012 at 8:20 PM, FRANK J. COMPUTER <frank_in_pr@hotmail.com>wrote: > OK thanks! So then I can increase PDQ parms for hairy queries? If > checkpoints > are necessary, what is the impact of increasing the interval time so that > it > doesn't checkpoint more often?.. Jonathan, do you have any knowledge about > the > problem that Art and I were discussing about the Windows version of 1170 > hanging up when trying to reconnect to it?.. He experienced the same > problems > and gave up on trying to fix it. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae9340661f0b4e404bf7bfd5c

IDS 11.70 has Star Join, multi-index, and other query plans including push-down hash joins. To enable star-join optimization you need one of the following: A singleton index on the fact table for each of the dimension table foreign key columns -- or -- An index on the fact table that has all of the dimension table foreign keys in a single index. If you have the first set of indexes, the optimizer can perform filtering directly on the dimension foreign key columns on the fact table using the multiple single column indexes in parallel and produce a net list of fact table rowids that match all criteria. If you have the second massively compound index, the engine can perform filters on the dimension tables using their primary key indexes, produce a set of compound keys from the cross join of the filter results and look those keys up in the compound index on the fact table. If you have both, the optimizer will calculate the best query path based on cost. There are optimizer directives you can use to guide the optimizer to a "better" decision (rare but its possible) or to just see if the optimizer is doing a better job than you would. See the section on optimizer directives in the Guide to SQL Syntax manual. Remember that the tables' data distributions (produced with UPDATE STATISTICS MEDIUM and HIGH are needed for the optimizer to make good decisions. There are recommendations in the Performance Guide on how to get a useful level of data distributions at minimal cost. Also see my presentation from this year's IIUG Conference entitled Kagel_Do_Stats_Right.... 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, May 7, 2012 at 8:33 PM, FRANK J. COMPUTER <frank_in_pr@hotmail.com>wrote: > ... and where are all the Red Brick SQL definition statements like CREATE > STAR > INDEX and all the other DSS related statements?.. I didn't see it in > DBACCESS' > help screen. I thought 1170 had this included in it? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae9340661f46ce804bf7c2200

These are some of the current settings I have: MAX_PDQPRIORITY 100 DS_MAX_QUERIES 1 DS_TOTAL_MEMORY 1024000 DS_MAX_SCANS 1 DS_NONPDQ_QUERY_MEM 256000 OPTCOMPIND 2 OPT_GOAL -1 QSTATS 0 WSTATS 0 NETTYPE olsoctcp,1,150,NET Still couldn't find AUTO_READAHEAD in this onconfig, so I set: RA_PAGES 256 RA_THRESHOLD 64 I'll take a look at the conference presentations on onconfig you mentioned. Thank You for all your help!

Someone posted this answer in http://stackoverflow.com/questions/10477914/which-of-the-ids-11-70-onconfig-para meters-can-be-changed-to-maximize-performanc and your reply contradicts some of the following: "..But in essence, DS_MAX_QUERIES is the maxumum number of parallel queries permitted at any time, and DS_MAX_SCANS determines the number of IO threads for scanning your tables. DS_TOTAL_MEMORY determines the amount of memory allocated for PDQ processing, and there is an algorithm in the manual that shows how these variables and the user's PDQPRIORITY setting combine. You might also want to consider lifting the RA_PAGES and RA_THRESHOLD values - these determine how many pages are read into memory as 'blocks' before grabbing the next batch. If you're wanting to favour table-scans (which generally you do in DSS) then increasing these to something like 256 and 128 might improve performance. My experience is with SMP and MPP unix boxes, rather than Windows, so I'm not sure how much you can wring out of your architecture, but this is where you want to start. I would recommend identifying a good DSS query that runs for a decent length of time, and changing one parameter at a time to see the effect. SET EXPLAIN ON is your friend here, too. One last thing - 11.7 supports table compression, and the tests I've seen show dramatic improvements in a DSS environment with large reads and irregular writes."

Good to know!.. I was looking through the docs on DSS features of 1170 and couldn't find the TMU (Table Management Utility) for high-performance loading of tables. OK, so now I'm going to do lots of reading and begin to design a good model for conducting category analysis in order to achieve the goals of producing customer profiles, product and promotional analysis in order to understand the results and make predictions! The good part is that none of the load data has to be sanitized, just needs to be consolidate some temporal ranges and create a few buckets to minimize some redundant data.