IDS Forum

Re: What are best onconfig parameters for a DSS ap

Posted By: Art Kagel
Date: Monday, 7 May 2012, at 6:25 p.m.

In Response To: What are best onconfig parameters for a DSS app (FRANK J. COMPUTER)

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 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
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--e89a8f3ba9595e299204bf79bf67

Messages In This Thread

• What are best onconfig parameters for a DSS app
  FRANK J. COMPUTER -- Monday, 7 May 2012, at 5:12 p.m.
  • RE: What are best onconfig parameters for a DSS ap
    Alexandre Marini -- Monday, 7 May 2012, at 5:28 p.m.
    • Re: RE: What are best onconfig parameters for a DS
      FRANK J. COMPUTER -- Monday, 7 May 2012, at 5:47 p.m.
  • Re: What are best onconfig parameters for a DSS ap
    Art Kagel -- Monday, 7 May 2012, at 6:25 p.m.
    • Re: What are best onconfig parameters for a DSS ap
      FRANK J. COMPUTER -- Monday, 7 May 2012, at 6:45 p.m.
      • Re: What are best onconfig parameters for a DSS ap
        Art Kagel -- Monday, 7 May 2012, at 6:48 p.m.
    • Re: What are best onconfig parameters for a DSS ap
      FRANK J. COMPUTER -- Monday, 7 May 2012, at 7:56 p.m.
      • Re: What are best onconfig parameters for a DSS ap
        Jonathan Leffler -- Monday, 7 May 2012, at 8:06 p.m.
        • Re: What are best onconfig parameters for a DSS ap
          FRANK J. COMPUTER -- Monday, 7 May 2012, at 8:20 p.m.
          • Re: What are best onconfig parameters for a DSS ap
            Art Kagel -- Monday, 7 May 2012, at 9:06 p.m.
        • Re: What are best onconfig parameters for a DSS ap
          FRANK J. COMPUTER -- Monday, 7 May 2012, at 8:33 p.m.
          • Re: What are best onconfig parameters for a DSS ap
            Art Kagel -- Monday, 7 May 2012, at 9:16 p.m.
            • Re: What are best onconfig parameters for a DSS ap
              FRANK J. COMPUTER -- Monday, 7 May 2012, at 10:59 p.m.
      • Re: What are best onconfig parameters for a DSS ap
        Art Kagel -- Monday, 7 May 2012, at 9:02 p.m.
        • Re: What are best onconfig parameters for a DSS ap
          FRANK J. COMPUTER -- Monday, 7 May 2012, at 9:32 p.m.
        • Re: What are best onconfig parameters for a DSS ap
          FRANK J. COMPUTER -- Monday, 7 May 2012, at 9:44 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.