Parameter tuning?

Pls see the instance profile and shared memory parameters below. The problems I noticed are 1. low read-cache-hit 35% 2. low write-cache-hit 79% 3. a lots of bufwaits and seqscans What do you advice? Informix Dynamic Server Version 7.31.UD3 -- On-Line -- Up 158 days 02:30:01 -- 299808 Kbytes Profile dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached 709636519 2287473345 1101860441 35.60 40085133 254130402 194042736 79.34 isamtot open start read write rewrite delete commit rollbk 2242399144 105086802 1485406942 2487445966 80059226 3708851 26378446 1274819 1641 gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs 0 0 0 0 0 0 0 ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes 0 0 0 412217.32 221862.72 22376 45458 bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans 121163016 1289 1674500070 148 0 1827 671025 23792119 ixda-RA idx-RA da-RA RA-pgsused lchwaits 556415450 15605821 12133051 426502790 10069114 # Shared Memory Parameters LOCKS 500000 # Maximum number of locks BUFFERS 80000 # Maximum number of shared memory buffers NUMAIOVPS 16 # Number of IO vps PHYSBUFF 64 # Physical log buffer size (Kbytes) LOGBUFF 32 # Logical log buffer size (Kbytes)LOGSMAX 25 # Maximum number of logical log files CLEANERS 127 # Number of buffer cleaner processes SHMBASE 0x0 # Shared memory base address SHMVIRTSIZE 100000 # initial virtual shared memory segment size SHMADD 90000 # Size of new shared memory segments (Kbytes) SHMTOTAL 0 # Total shared memory (Kbytes). 0=>unlimited CKPTINTVL 600 # Check point interval (in sec) LRUS 127 # Number of LRU queues LRU_MAX_DIRTY 5 # LRU modified begin-cleaning limit (percent) LRU_MIN_DIRTY 3 # LRU modified end-cleaning limit (percent) LTXHWM 50 # Long TX high-water mark (percent) LTXEHWM 60 # Long TX exclusive high-water mark (percent) TXTIMEOUT 0x12c # Transaction timeout (in sec) STACKSIZE 32 # Stack size (Kbytes)

WH MAX wrote: > Pls see the instance profile and shared memory parameters below. > The problems I noticed are > 1. low read-cache-hit 35% > More BUFFERS. > 2. low write-cache-hit 79% > Doesn't matter. > 3. a lots of bufwaits and seqscans > Could be anything, but most likely an application issue. > What do you advice? > > Get a consultant in. > Informix Dynamic Server Version 7.31.UD3 -- On-Line -- Up 158 days 02:30:01 -- > 299808 Kbytes > > Profile > dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached > 709636519 2287473345 1101860441 35.60 40085133 254130402 194042736 79.34 > > isamtot open start read write rewrite delete commit rollbk > 2242399144 105086802 1485406942 2487445966 80059226 3708851 26378446 1274819 > 1641 > > gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs > 0 0 0 0 0 0 0 > > ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes > 0 0 0 412217.32 221862.72 22376 45458 > > bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans > 121163016 1289 1674500070 148 0 1827 671025 23792119 > > ixda-RA idx-RA da-RA RA-pgsused lchwaits > 556415450 15605821 12133051 426502790 10069114 > > # Shared Memory Parameters > LOCKS 500000 # Maximum number of locks > BUFFERS 80000 # Maximum number of shared memory buffers > NUMAIOVPS 16 # Number of IO vps > PHYSBUFF 64 # Physical log buffer size (Kbytes) > LOGBUFF 32 # Logical log buffer size (Kbytes)> LOGSMAX 25 # Maximum number of logical log files > CLEANERS 127 # Number of buffer cleaner processes > SHMBASE 0x0 # Shared memory base address > SHMVIRTSIZE 100000 # initial virtual shared memory segment size > SHMADD 90000 # Size of new shared memory segments (Kbytes) > SHMTOTAL 0 # Total shared memory (Kbytes). 0=>unlimited > CKPTINTVL 600 # Check point interval (in sec) > LRUS 127 # Number of LRU queues > LRU_MAX_DIRTY 5 # LRU modified begin-cleaning limit (percent) > LRU_MIN_DIRTY 3 # LRU modified end-cleaning limit (percent) > LTXHWM 50 # Long TX high-water mark (percent) > LTXEHWM 60 # Long TX exclusive high-water mark (percent) > TXTIMEOUT 0x12c # Transaction timeout (in sec) > STACKSIZE 32 # Stack size (Kbytes)> > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > >

Once in a while I agree with Obnoxio >Get a consultant in. Use it as a learning tool. I would expect the application(s) is doing something the database was not designed to do. On Thu, Jun 5, 2008 at 9:12 AM, Obnoxio The Clown <obnoxio@serendipita.com> wrote: > WH MAX wrote: > > Pls see the instance profile and shared memory parameters below. > > The problems I noticed are > > 1. low read-cache-hit 35% > > > More BUFFERS. > > > 2. low write-cache-hit 79% > > > > Doesn't matter. > > 3. a lots of bufwaits and seqscans > > > Could be anything, but most likely an application issue. > > > What do you advice? > > > > > Get a consultant in. > > > Informix Dynamic Server Version 7.31.UD3 -- On-Line -- Up 158 days > 02:30:01 > -- > > 299808 Kbytes > > > > Profile > > dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached > > 709636519 2287473345 1101860441 35.60 40085133 254130402 194042736 79.34 > > > > isamtot open start read write rewrite delete commit rollbk > > 2242399144 105086802 1485406942 2487445966 80059226 3708851 26378446 > 1274819 > > 1641 > > > > gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs > > 0 0 0 0 0 0 0 > > > > ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes > > 0 0 0 412217.32 221862.72 22376 45458 > > > > bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans > > 121163016 1289 1674500070 148 0 1827 671025 23792119 > > > > ixda-RA idx-RA da-RA RA-pgsused lchwaits > > 556415450 15605821 12133051 426502790 10069114 > > > > # Shared Memory Parameters > > LOCKS 500000 # Maximum number of locks > > BUFFERS 80000 # Maximum number of shared memory buffers > > NUMAIOVPS 16 # Number of IO vps > > PHYSBUFF 64 # Physical log buffer size (Kbytes) > > LOGBUFF 32 # Logical log buffer size (Kbytes)> > LOGSMAX 25 # Maximum number of logical log files > > CLEANERS 127 # Number of buffer cleaner processes > > SHMBASE 0x0 # Shared memory base address > > SHMVIRTSIZE 100000 # initial virtual shared memory segment size > > SHMADD 90000 # Size of new shared memory segments (Kbytes) > > SHMTOTAL 0 # Total shared memory (Kbytes). 0=>unlimited > > CKPTINTVL 600 # Check point interval (in sec) > > LRUS 127 # Number of LRU queues > > LRU_MAX_DIRTY 5 # LRU modified begin-cleaning limit (percent) > > LRU_MIN_DIRTY 3 # LRU modified end-cleaning limit (percent) > > LTXHWM 50 # Long TX high-water mark (percent) > > LTXEHWM 60 # Long TX exclusive high-water mark (percent) > > TXTIMEOUT 0x12c # Transaction timeout (in sec) > > STACKSIZE 32 # Stack size (Kbytes)> > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >

Bufwaits is not your problem: Bufwaits Ratio (BR) = 4.88 ( <7.0 is OK) Buffer Turnover Rate (BTR) = 8.17/hour (< 10.0 is usually OK) Readahead Utilization (RAU) = 73.01% (Should be > 99.0!) Your biggest problem is that you are thrashing the buffer pool with vastly excessive read ahead! You didn't post them, but either you have a very high setting for RA_PAGES or RA_THRESHOLD is very close to RA_PAGES. My recommended setting for most OLTP type installations (don't know your system load type, but based on the onstat -p output I'd say it's mostly OLTP-like) would be: RA_PAGES 8 RA_THRESHOLD 2 This is different from the manuals, but impirical experience puts it about there. Most modern systems do so much readahead for you at the drive, controller, disk array, and OS levels that Informix's readhead is redundant and more harmful than helpful. Minimize it. I don't see any evidence of it, but I suspect that 80,000 buffers is not enough. Monitor onstat -P and see if a small number of tables are dominating the buffer cache with many smaller table and index presences swapping in and out. In that case increasing the cache to say 100,000 will also help. Other factors: about 2% of your queries involve sequential scans, this is a bit high. You have over a million rollbacks versus 26 million commited transactions or about 5%, this is also rather high. The last indicates that your applications may be experiencing clashes caused by multiple users trying to update the same data and having to rollback when the app discovers that the data has been changed by someone else. This is a work-flow problem rather than a database one, but it affects server performance. Art On Thu, Jun 5, 2008 at 8:16 AM, WH MAX <wen-hong.hua@st.com> wrote: > Pls see the instance profile and shared memory parameters below. > The problems I noticed are > 1. low read-cache-hit 35% > 2. low write-cache-hit 79% > 3. a lots of bufwaits and seqscans > > What do you advice? > > Informix Dynamic Server Version 7.31.UD3 -- On-Line -- Up 158 days 02:30:01 > -- > 299808 Kbytes > > Profile > dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached > 709636519 2287473345 1101860441 35.60 40085133 254130402 194042736 79.34 > > isamtot open start read write rewrite delete commit rollbk > 2242399144 105086802 1485406942 2487445966 80059226 3708851 26378446 > 1274819 > 1641 > > gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs > 0 0 0 0 0 0 0 > > ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes > 0 0 0 412217.32 221862.72 22376 45458 > > bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans > 121163016 1289 1674500070 148 0 1827 671025 23792119 > > ixda-RA idx-RA da-RA RA-pgsused lchwaits > 556415450 15605821 12133051 426502790 10069114 > > # Shared Memory Parameters > LOCKS 500000 # Maximum number of locks > BUFFERS 80000 # Maximum number of shared memory buffers > NUMAIOVPS 16 # Number of IO vps > PHYSBUFF 64 # Physical log buffer size (Kbytes) > LOGBUFF 32 # Logical log buffer size (Kbytes)> LOGSMAX 25 # Maximum number of logical log files > CLEANERS 127 # Number of buffer cleaner processes > SHMBASE 0x0 # Shared memory base address > SHMVIRTSIZE 100000 # initial virtual shared memory segment size > SHMADD 90000 # Size of new shared memory segments (Kbytes) > SHMTOTAL 0 # Total shared memory (Kbytes). 0=>unlimited > CKPTINTVL 600 # Check point interval (in sec) > LRUS 127 # Number of LRU queues > LRU_MAX_DIRTY 5 # LRU modified begin-cleaning limit (percent) > LRU_MIN_DIRTY 3 # LRU modified end-cleaning limit (percent) > LTXHWM 50 # Long TX high-water mark (percent) > LTXEHWM 60 # Long TX exclusive high-water mark (percent) > TXTIMEOUT 0x12c # Transaction timeout (in sec) > STACKSIZE 32 # Stack size (Kbytes)> -- Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.

Good point Art. I also wonder whether statistics have been collected. Awfully high sequential scans. j.

Great Mr Kagel! i guess now working with IDS 11.* we get performance advisory in online.log but ur calculations and adivisory are of great help. Pls could you give us the formule for calculating Bufwaits Ratio (BR) Buffer Turnover Rate (BTR) Readahead Utilization (RAU) especially RAU is very imp as all the instances on which i worked/working have RA_THRESHOLD = RA_PAGES/2 and RA_PAGES is < = 32. calculating RAU and comparing it agains ( > 99 %)will help me to reduce the RA_PAGES and RA_THRESHOLD to smaller values (OLTP optimized). Regards, vikas

BR = ((bufwaits / (dskreads + bufwrites)) * 100.00) BR <= 7.0 -- All's well BR > 7.0 & BR < 10.00 -- Things are slow BR > 10.00 -- Death - server is dragging and everyone notices. BTR = (((dskreads + bufwrites) / BUFFERS) / <# Fractional hours since restart or stats reset>) < 10 -- All is probably well, but if there are performance problems or low cache check onstat -P anyway > 10 -- Buffer cache is thrashing Interpret as the number of times the buffer cache contents are replaced per hour. BTR==10 means once every 6 minutes! Or some subset is being thrashed even more aggressively. Monitoring onstat -P over time will tell you which. RAU = ((RA-pgsused / (ixda-RA + idx-RA + da-RA )) * 100.00) Interpret as the % of readahead that's actually used. Should be as close to 100%, 99.9 is good, even 99.8 is questionably low. You describe a classic configuration having set the RA_THRESHOLD according to the manuals, however, I did state that I disagree with the manuals on the RA_ parameters. Setting RA_THRESHOLD to RA_PAGES/2 is WAY too high unless RA_PAGES is 4. It means that if you use only half of your readahead (16 pages) IDS will read in another 32pages for you. In the days of singleton spindles using unintelligent drives for chunks this made some sense. Today, you are likely using a RAID array, and probably a multi-spindle RAID10 array, that is performing readahead for you into its own extensive cache using an intelligent disk controller that's also performing readhahead into its own not insignificant cache from an intelligent SCSI or other intelligent drive with on-board cache that's also performing readahead. I guarantee (without liability my attorney says) that such an IO setup can deliver more readahead pages to IDS before they are needed directly from pre-fetched cache even if you set RA_THRESHOLD to 4 or even 2. This analysis is bourne out by your low RAU metric value. Art On Thu, Jun 5, 2008 at 10:47 AM, VIKAS HIVARKAR <vikashivarkar3@gmail.com> wrote: > Great Mr Kagel! > > i guess now working with IDS 11.* we get performance advisory in online.log > but ur calculations and adivisory are of great help. > > Pls could you give us the formule for calculating > > Bufwaits Ratio (BR) > Buffer Turnover Rate (BTR) > Readahead Utilization (RAU) > > especially RAU is very imp as all the instances on which i worked/working > have > RA_THRESHOLD = RA_PAGES/2 and RA_PAGES is < = 32. > > calculating RAU and comparing it agains ( > 99 %)will help me to reduce the > RA_PAGES and RA_THRESHOLD to smaller values (OLTP optimized). > > Regards, > vikas > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.

Here's a query that Eric Herber published to track sequential scans by partition: unload to scans.MONTH.txt delimiter ';'SELECT FIRST 50 -- Added by Doug stn.dbsname as Database, -- [1,15], Subscripts removed to show stn.tabname as Table, -- [1,18], full names as run in AGS sti.ti_nptotal * ( select pagesize from sysmaster:sysdbspaces where name = dbinfo('dbspace', sti.ti_partnum) )/1024 as Table_Size_KB, sti.ti_nrows as Number_Of_Rows, spp.seqscans as Number_Of_Sequential_Scans, (sti.ti_nrows * spp.seqscans) AS Total_Rows_Scanned FROM sysmaster:systabnames stn, sysmaster:systabinfo sti, sysmaster:sysptprof spp WHERE stn.partnum = sti.ti_partnum AND stn.partnum = spp.partnum AND spp.seqscans > 0 ORDER BY 6 DESC, 5 DESC; For server versions earlier than 10.00 that have a fixed pagesize, replace the subquery with the pagesize in bytes - so 2048 for all platforms except AIX and Windows where it's 4096. Art On Thu, Jun 5, 2008 at 10:44 AM, vze2qjg5@verizon.net <vze2qjg5@verizon.net> wrote: > Good point Art. I also wonder whether statistics have been collected. > Awfully > high sequential scans. > > j. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.