Re: Bufwaits and Foreground Writes

From: "Bernstein, Rick" <rbernste@alarismed.com> > >Despite our tuning efforts, we are encountering a significant number of >bufwaits and foreground writes >on our production SAP system. This system runs IDS 7.30.UC7XK on a 4-way >RS6000 R50 (AIX). >I attended Informix's Performance Tuning class last week in Menlo Park. >Their suggestions >included:. >1) Use different ONCONFIG at different times of the day. > Our system must be available to online users 24 hours a day, 6 days a >week. > Therefore, this suggestion is not realistic for us. >2) Reduce the number of LRUs to 4 * number of CPU VPs. > Will this really help? Or will it make matters worse? Utter bollocks. >3) Increase the LRU_MIN_DIRTY and LRU_MAX_DIRTY values to improve write >cache %. This seems equally dumb -- your checkpoints will probably just become longer. >Below are statistics from onstat and SMI queries. This represents less >than >50% of our >normal workload due to the holidays. Of concern are: >1) "ovbuff" value in the "onstat -p" and foreground writes in the "onstat >-F". >2) Poor write cache %. >3) Large number of seqscans. > >Should I be concerned about these statistics? Or are they possibly caused >by product defects >108509 and 115741? The "seqscans" numbers from the "sysmaster:sysptprof" >table do not seem >realistic. If that many sequential scans were occurring for some of the >large tables, the number >of pages read would be much larger because the entire table could not held >in memory. >Yet we are also seeing a large number of read-aheads which give some >validity to the seqscans >numbers. Does anyone have system-level tuning recommendations? With an >SAP >system >upgrade planned for later this year, it is unlikely that I can drum up >support for application-level tuning. > >The classroom material from the Performance Tuning class recommend use of >the 'onstat -g spi' >to monitor LRU usage and ensure that adequate queues are allocated. >However, it does not >tell how to interpret the output from this command. The class instructor >suggested that I >monitor the lines containing "vproc" for excessive "Avg Loop/Wait" values. >Is this right? >If so, what values are considered excessive? > >Assistance from Informix experts, who participate in this group, would be >greatly appreciated! > >=>> 'onstat -p' command output follows: > >Informix Dynamic Server Version 7.30.UC7XK -- On-Line -- Up 3 days 19:05:31 >-- 1457264 Kbytes > >Profile >dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached >34027290 17882518 1257830526 97.29 4359252 3897016 16744531 73.97 > >isamtot open start read write rewrite delete commit >rollbk >933381413 1817089 75970820 561674437 2554533 1518679 1560434 607349 >13 > >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 2309 150491.52 67514.29 191 382 > >bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans >7848325 788 560896897 0 0 247 235256 12909197 > >ixda-RA idx-RA da-RA RA-pgsused lchwaits >23508356 451550 100557 23854808 466298 > > >=>> Tables with the most seqscans per sysmaster:systprof > >table npused bufreads bufwrites seqscans pagreads >pagwrites >knvp 12214 54583788 1177 3751498 17110 >523 >knvv 4597 25010688 67 3736198 13672 >56 >vbfa 390751 93265427 238630 2058429 1570756 >47803 >vbpa 259503 22307877 95060 1657259 425133 >3824 >vbap 263172 36592659 33988 1072444 835062 >9195 >knvh 603 13625603 41 263849 11420 >24 >bsad 198499 1138932 16165 175169 34217 >3749 >dbsta 22 88887 4 25380 178 >4 >s066 2108 366205 5406 18109 43414 >3754 >vapma 62725 8912986 24680 17659 67894 >8076 >dd04t 13486 834373 0 15397 11890 >0 >dd04l 2214 1111433 0 15397 8752 >0 >qals 18760 1944429 4313 14243 8270 >1844 >dd07t 2084 1790276 0 10436 806 >0 >dd07l 330 1994037 0 10254 588 >0 >qpct 101 2076623 0 10216 687 >0 >hrp10 25 42136 0 8516 44 >0 >mkol 2 12341 0 6151 15 >0 > > >=>> 'onstat -F' command output follows: > >Fg Writes LRU Writes Chunk Writes >2309 1121507 374479 > > >=>> 'onstat -P' command output follows: > >Informix Dynamic Server Version 7.30.UC7XK -- On-Line -- Up 3 days 19:05:32 >-- 1457264 Kbytes >partnum total btree data other resident dirty > >Totals: 150000 20661 128688 651 0 2436 > >Percentages: >Data 85.79 >Btree 13.77 >Other 0.43 > > >=>> 'onstat -c' command output follows: > ># Root Dbspace Configuration > >ROOTNAME rootdbs # Root dbspace name>ROOTPATH /informix/PRD/sapdata/physdev0/data02 > # Path for device containing root dbspace >ROOTOFFSET 16 # Offset of root dbspace into device >(Kbytes) >ROOTSIZE 200000 # Size of root dbspace (Kbytes)> ># Disk Mirroring Configuration Parameters > >MIRROR 1 # Mirroring flag (Yes = 1, No = 0) >MIRRORPATH /informix/PRD/sapdata/physdev21/data212 > # Path for device containing mirrored root >MIRROROFFSET 16 # Offset into mirrored device (Kbytes)> ># Physical Log Configuration > >PHYSDBS physdbs # Location (dbspace) of physical log >PHYSFILE 229148 # Physical log file size (Kbytes)>092098rb > ># Logical Log Configuration > >LOGFILES 98 # Number of logical log files >LOGSIZE 500 # initial (!) Logical log size (Kbytes)> ># Diagnostics > >MSGPATH /informix/PRD/online.wrax39210.prd.log > # System message log file path ># CONSOLE /informix/PRD/console.wrax39210.prd.log >CONSOLE /dev/null > # System console message path >#ALARMPROGRAM /informix/PRD/etc/log_full.sh # Alarm program path>120497rb >ALARMPROGRAM /scripts/adsm/log_full.sh # Alarm program path >120497rb > ># System Archive Tape Device > >#TAPEBLK 256 # Tape block size (Kbytes) >TAPESIZE 1950000 # Maximum amount of data to put on tape >(Kbytes) >TAPEDEV /dev/rmt1 # Tape device path >TAPEBLK 1024 # Tape block size (Kbytes)> ># Log Archive