Tuning advice needed - IDS 11.5.FC1

Greetings All, I am looking for advice on tuning this instance. The overview is: IBM P5 55A - dual quad core cpu's with 32G memory Disk space is via two 2G fiber HBA's to an IBM 2105-800 (Shark) Each database is in its own dbspace. Each dbspace is in its own LV. O/S is AIX 5.3. IDS is 11.5.FC1 No one ever accesses this instance other than the unload script, which is run locally. This instance is a FlashCopy of the Live instance. The singular purpose is to unload each database for archiving. I started with the onconfig.std onconfig file. Using the largest database as the point of reference, it took 3 hours and 45 minutes to unload. I use a script that calls dbaccess and uses the unload command to unload each table, one after the other, to a dedicated file system. Using tips and suggestions found here in the CDI I now have the unload time down to 1 hour and 23 minutes. The edited onconfig file is listed below. If you want any additional info, or would like some onstat outputs, let me know. I've saved several onstat -a outputs at various times, and could run other utilities as requested. I appreciate everyone's input, and will post any results I get due to recommended changes. Thanks in advance, Tom ################################################################### ROOTNAME rootdbs ROOTPATH rrdbs ROOTOFFSET 4 ROOTSIZE 1999996MIRROR 0 MIRRORPATH MIRROROFFSET 0 ################################################################### PHYSFILE 2239784 PLOG_OVERFLOW_PATH $INFORMIXDIR/tmp/asphaL1 PHYSBUFF 128 ################################################################### LOGFILES 8 LOGSIZE 999000 DYNAMIC_LOGS 2 LOGBUFF 128 ################################################################### LTXHWM 70 LTXEHWM 80 ################################################################### MSGPATH $INFORMIXDIR/tmp/on_asphaA1.log CONSOLE $INFORMIXDIR/tmp/on_asphaA1.con ################################################################### TBLTBLFIRST 0 TBLTBLNEXT 0 TBLSPACE_STATS 1 ################################################################### DBSPACETEMP tmp1_dbs:tmp2_dbs:tmp3_dbs:tmp4_dbs SBSPACETEMP ################################################################### SBSPACENAME SYSSBSPACENAME ONDBSPACEDOWN 0 ################################################################### SERVERNUM 7 DBSERVERNAME asphaA1 DBSERVERALIASES ################################################################### NETTYPE ipcshm,2,50,CPU LISTEN_TIMEOUT 60 MAX_INCOMPLETE_CONNECTIONS 1024 FASTPOLL 1 ################################################################### MULTIPROCESSOR 1 VPCLASS cpu,num=2,noage VP_MEMORY_CACHE_KB 0 SINGLE_CPU_VP 0 ################################################################### VPCLASS aio,num=1 CLEANERS 64AUTO_AIOVPS 1 DIRECT_IO 0 ################################################################### LOCKS 20000 DEF_TABLE_LOCKMODE page ################################################################### RESIDENT 1 SHMBASE 0x700000000000000L SHMVIRTSIZE 4096000 SHMADD 8192 EXTSHMADD 8192 SHMTOTAL 0 SHMVIRT_ALLOCSEG 0,3 SHMNOACCESS ################################################################### CKPTINTVL 300AUTO_CKPTS 1 RTO_SERVER_RESTART 0 BLOCKTIMEOUT 3600 ################################################################### TXTIMEOUT 300 DEADLOCK_TIMEOUT 60 HETERO_COMMIT 0 ################################################################### TAPEDEV /dev/null TAPEBLK 32 TAPESIZE 0 ################################################################### LTAPEDEV /dev/null LTAPEBLK 32 LTAPESIZE 0 ################################################################### BAR_ACT_LOG $INFORMIXDIR/tmp/asphaA1/bar_act.log BAR_DEBUG_LOG $INFORMIXDIR/tmp/asphaA1/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 ################################################################### 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_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 ONLIDX_MAXMEM 5120 ################################################################### MAX_PDQPRIORITY 100 DS_MAX_QUERIES DS_TOTAL_MEMORY DS_MAX_SCANS 1048576 DS_NONPDQ_QUERY_MEM 128 DATASKIP ################################################################### OPTCOMPIND 2 DIRECTIVES 1 EXT_DIRECTIVES 0 OPT_GOAL -1 IFX_FOLDVIEW 0AUTO_REPREPARE 1 ################################################################### RA_PAGES 64 RA_THRESHOLD 16 ################################################################### EXPLAIN_STAT 0 #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 SSL_KEYSTORE_LABEL ################################################################### PLCY_POOLSIZE 127 PLCY_HASHSIZE 31 USRC_POOLSIZE 127 USRC_HASHSIZE 31 ################################################################### STAGEBLOB OPCACHEMAX 0 ################################################################### 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_MAX_DYNAMIC_LOGS 0 CDR_SUPPRESS_ATSRISWARN ################################################################### DRAUTO 0 DRINTERVAL 30DRTIMEO

"Tom" <tlowrie@munis.com> wrote in message news:be07065d-7eec-4a15-9a44-99f571a1d2ee@p2g2000prf.googlegroups.com... > Greetings All, > > I am looking for advice on tuning this instance. The overview is: > > IBM P5 55A - dual quad core cpu's with 32G memory > Disk space is via two 2G fiber HBA's to an IBM 2105-800 (Shark) > Each database is in its own dbspace. Each dbspace is in its own LV. > O/S is AIX 5.3. IDS is 11.5.FC1 > No one ever accesses this instance other than the unload script, which > is run locally. > > This instance is a FlashCopy of the Live instance. The singular > purpose is to unload each database for archiving. > I started with the onconfig.std onconfig file. Using the largest > database as the point of reference, it took 3 hours and 45 minutes to > unload. I use a script that calls dbaccess and uses the unload command > to unload each table, one after the other, to a dedicated file system. Well, a few thoughts. Firstly if you're just running a load of sequential ASCII unloads you could have saved yourself the bother of writing the script and just used dbexport. It's not entirely clear what your objective in asking the question is. Is it that the 1h 20m. or whatever it takes, is too long? Why is it too long? If the server no other purpose do you really care how long it takes to unload? A few things that you might consider if you do need to speed it up: 1. Experiment with much larger settings for Read Ahead 2. Use a binary onunload of each db. 3. Script HPL rather than dbaccess unloads for the larger tables at least. rgds Neil

On Jan 5, 12:21 pm, "Neil Truby" <neil.tr...@ardenta.com> wrote: > "Tom" <tlow...@munis.com> wrote in message > > news:be07065d-7eec-4a15-9a44-99f571a1d2ee@p2g2000prf.googlegroups.com... > > > > > > > Greetings All, > > > I am looking for advice on tuning this instance. The overview is: > > > IBM P5 55A - dual quad core cpu's with 32G memory > > Disk space is via two 2G fiber HBA's to an IBM 2105-800 (Shark) > > Each database is in its own dbspace. Each dbspace is in its own LV. > > O/S is AIX 5.3. IDS is 11.5.FC1 > > No one ever accesses this instance other than the unload script, which > > is run locally. > > > This instance is a FlashCopy of the Live instance. The singular > > purpose is to unload each database for archiving. > > I started with the onconfig.std onconfig file. Using the largest > > database as the point of reference, it took 3 hours and 45 minutes to > > unload. I use a script that calls dbaccess and uses the unload command > > to unload each table, one after the other, to a dedicated file system. > > Well, a few thoughts.  Firstly if you're just running a load of sequential > ASCII unloads you could have saved yourself the bother of writing the script > and just used dbexport. > > It's not entirely clear what your objective in asking the question is.  Is > it that the 1h 20m. or whatever it takes, is too long?  Why is it too long? > If the server no other purpose do you really care how long it takes to > unload? > > A few things that you might consider if you do need to speed it up: > 1. Experiment with much larger settings for Read Ahead > 2. Use a binary onunload of each db. > 3. Script HPL rather than dbaccess unloads for the larger tables at least. > > rgds > Neil- Hide quoted text - > > - Show quoted text - Neil, I will revisit dbexport. I have found in the past that my unload script unloads a DB much faster, but perhaps that has changed in 11.5. The server also houses the live instance. Our client usage is primarily from 8:00 AM to 9:00 PM. My nightly archives need to be done by 5:15 AM because they are used for two purposes: refreshing test and training DB's and the contract required text-formatted archive file. The archive file cannot be engine-dependant. My objective is to get the best speed possible. I now have 90 DB's on the server, with 35 more waiting to be brought over from older servers. Here is my current RA related stats: Bufwait Ratio: 0.330752 Buffer Turnover: 0.176696 Ovbuff: 0 Read Ahead: 99.9441 Read Cache: 85.08 Write Cache: 99.84 FG Writes: 0 LRU Writes: 0 Chunk Writes: 1914 Thank you for the response, Tom

"Tom" <tlowrie@munis.com> wrote in message news:50e0bdb9-d0af-4901-8bd0-e5397bf31d9a@z6g2000pre.googlegroups.com... On Jan 5, 12:21 pm, "Neil Truby" <neil.tr...@ardenta.com> wrote: > "Tom" <tlow...@munis.com> wrote in message > I might also have said that you could lauch multipe unloads - say 10 at a time - within your script.

Neil is right. You could also try "export LIGHT_SCANS=FORCE", "SET PDQPRIORITY 0;" and "SET ISOLATION TO DIRTY READ;". The DIO stuff is worth looking at, too. -L.S.

On Jan 5, 1:49 pm, "Neil Truby" <neil.tr...@ardenta.com> wrote: > "Tom" <tlow...@munis.com> wrote in message > > news:50e0bdb9-d0af-4901-8bd0-e5397bf31d9a@z6g2000pre.googlegroups.com... > On Jan 5, 12:21 pm, "Neil Truby" <neil.tr...@ardenta.com> wrote: > > > "Tom" <tlow...@munis.com> wrote in message > > I might also have said that you could lauch multipe unloads - say 10 at a > time - within your script. Neil, I run two at a time now. At one time, I noticed running 3 slowed everything down, but I have changed quite a few parameters since then. I should revisit this area. Thanks, Tom

On Jan 5, 2:31 pm, LIGHT SCANS <light_sc...@yahoo.com> wrote: > Neil is right.  You could also try "export LIGHT_SCANS=FORCE", "SET > PDQPRIORITY 0;" and "SET ISOLATION TO DIRTY READ;".  The DIO stuff is > worth looking at, too. > > -L.S. L.S., The DIO on my configuration clobbered performance. I have all raw spaces, and as far as I can tell this option is for cooked file systems. I have never used the LIGHT_SCANS. I will read up on this and see how to test it out. Thanks, Tom

Hi, You need to use HPL instead of either unload or dbexport. It is almost 2-3 times faster than the normal unload command even without doing any tuning. Thanks & Regards, Pravin Tom <tlowrie@munis.com> Sent by: informix-list-bounces@iiug.org 01/05/2009 10:02 PM To informix-list@iiug.org cc Subject Tuning advice needed - IDS 11.5.FC1 Greetings All, I am looking for advice on tuning this instance. The overview is: IBM P5 55A - dual quad core cpu's with 32G memory Disk space is via two 2G fiber HBA's to an IBM 2105-800 (Shark) Each database is in its own dbspace. Each dbspace is in its own LV. O/S is AIX 5.3. IDS is 11.5.FC1 No one ever accesses this instance other than the unload script, which is run locally. This instance is a FlashCopy of the Live instance. The singular purpose is to unload each database for archiving. I started with the onconfig.std onconfig file. Using the largest database as the point of reference, it took 3 hours and 45 minutes to unload. I use a script that calls dbaccess and uses the unload command to unload each table, one after the other, to a dedicated file system. Using tips and suggestions found here in the CDI I now have the unload time down to 1 hour and 23 minutes. The edited onconfig file is listed below. If you want any additional info, or would like some onstat outputs, let me know. I've saved several onstat -a outputs at various times, and could run other utilities as requested. I appreciate everyone's input, and will post any results I get due to recommended changes. Thanks in advance, Tom ################################################################### ROOTNAME rootdbs ROOTPATH rrdbs ROOTOFFSET 4 ROOTSIZE 1999996MIRROR 0 MIRRORPATH MIRROROFFSET 0 ################################################################### PHYSFILE 2239784 PLOG_OVERFLOW_PATH $INFORMIXDIR/tmp/asphaL1 PHYSBUFF 128 ################################################################### LOGFILES 8 LOGSIZE 999000 DYNAMIC_LOGS 2 LOGBUFF 128 ################################################################### LTXHWM 70 LTXEHWM 80 ################################################################### MSGPATH $INFORMIXDIR/tmp/on_asphaA1.log CONSOLE $INFORMIXDIR/tmp/on_asphaA1.con ################################################################### TBLTBLFIRST 0 TBLTBLNEXT 0 TBLSPACE_STATS 1 ################################################################### DBSPACETEMP tmp1_dbs:tmp2_dbs:tmp3_dbs:tmp4_dbs SBSPACETEMP ################################################################### SBSPACENAME SYSSBSPACENAME ONDBSPACEDOWN 0 ################################################################### SERVERNUM 7 DBSERVERNAME asphaA1 DBSERVERALIASES ################################################################### NETTYPE ipcshm,2,50,CPU LISTEN_TIMEOUT 60 MAX_INCOMPLETE_CONNECTIONS 1024 FASTPOLL 1 ################################################################### MULTIPROCESSOR 1 VPCLASS cpu,num=2,noage VP_MEMORY_CACHE_KB 0 SINGLE_CPU_VP 0 ################################################################### VPCLASS aio,num=1 CLEANERS 64AUTO_AIOVPS 1 DIRECT_IO 0 ################################################################### LOCKS 20000 DEF_TABLE_LOCKMODE page ################################################################### RESIDENT 1 SHMBASE 0x700000000000000L SHMVIRTSIZE 4096000 SHMADD 8192 EXTSHMADD 8192 SHMTOTAL 0 SHMVIRT_ALLOCSEG 0,3 SHMNOACCESS ################################################################### CKPTINTVL 300AUTO_CKPTS 1 RTO_SERVER_RESTART 0 BLOCKTIMEOUT 3600 ################################################################### TXTIMEOUT 300 DEADLOCK_TIMEOUT 60 HETERO_COMMIT 0 ################################################################### TAPEDEV /dev/null TAPEBLK 32 TAPESIZE 0 ################################################################### LTAPEDEV /dev/null LTAPEBLK 32 LTAPESIZE 0 ################################################################### BAR_ACT_LOG $INFORMIXDIR/tmp/asphaA1/bar_act.log BAR_DEBUG_LOG $INFORMIXDIR/tmp/asphaA1/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 ################################################################### 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_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 ONLIDX_MAXMEM 5120 ################################################################### MAX_PDQPRIORITY 100 DS_MAX_QUERIES DS_TOTAL_MEMORY DS_MAX_SCANS 1048576 DS_NONPDQ_QUERY_MEM 128 DATASKIP ################################################################### OPTCOMPIND 2 DIRECTIVES 1 EXT_DIRECTIVES 0 OPT_GOAL -1 IFX_FOLDVIEW 0AUTO_REPREPARE 1 ################################################################### RA_PAGES 64 RA_THRESHOLD 16 ################################################################### EXPLAIN_STAT 0 #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 SSL_KEYSTORE_LABEL ################################################################### PLCY_POOLSIZE 127 PLCY_HASHSIZE 31 USRC_POOLSIZE 127 USRC_HASHSIZE 31 ################################################################### STAGEBLOB OPCACHEMAX 0 ################################################################### ENCRYPT_HDR ENCRYPT_SMX ENCRYPT_CDR 0 ENCRYPT_CIPHERS ENCRYPT_MAC ENCRYPT_MACFILE ENCRYPT_SWITCH @@N

"Pravin Kedia" <prakedia@in.ibm.com> wrote in message news:mailman.123.1231214802.1831.informix-list@iiug.org... >>You need to use HPL instead of either unload or dbexport. It is almost 2-3 >>times faster than the normal unload command ... Ah! Such clarity of thought and precision in arithmetic is so refreshing ...!

Hello Tom, I agree with Neil, Pravin, and Captain Pedantic. For any load or unload job that is done more than a few times, hyperloader is probably the best way to go. I still don't know how to use it because I remember when HPL was GUI only, hard to set up and very buggy. I am told that it is much better now, and, in your case, I would finally be willing to give it a try. That being said, I have been very creative in avoiding HPL. If you want to do "light scans" then read my post "How to do Light Scans . . ." posted on Jan. 5, 2009: http://groups.google.com/group/comp.databases.informix/browse_thread/thread/253d37708a013731?hl=en Another idea might be to somehow pipe your unload to "compress". Doing a "compress" before it gets to the network and/or disk usually speed things up dramatically. And lastly, you could write a bunch of C programs (but that is a lot more work than HPL). -L.S.

> From: light_scans@yahoo.com > Subject: Re: Tuning advice needed - IDS 11.5.FC1 > Date: Tue, 6 Jan 2009 12:13:02 -0800 > To: informix-list@iiug.org > [SNIP] > Another idea might be to somehow pipe your unload to "compress". > Doing a "compress" before it gets to the network and/or disk usually > speed things up dramatically. And lastly, you could write a bunch of > C programs (but that is a lot more work than HPL). > > -L.S. And what do you mean that you would have to write a bunch of C programs? If you want something that is fast, you need to write only *one* intelligent C program that understands how to read the databases meta data information. ;-) BTW, in theory, there are faster ways to dump a database depending on what technology you have available. It all depends on what APIs and data structure information that are made public and the format of your storage files. ;-) _________________________________________________________________ Life on your PC is safer, easier, and more enjoyable with Windows Vista®. http://clk.atdmt.com/MRT/go/127032870/direct/01/