RE: Bufwaits and Foreground Writes

Rudy, In theory I agree with your assessment. However, I do not really believe some of the sequential scan information which is being reported. We only have 150,000 pages in our buffer pool. Yet Informix is reporting fewer "pagreads" than "seqscans" for tables which cannot be held in memory. How can this be true? I suspect that erroneous information is being reported, due to an Informix bug. One such software defect is: 150509 - SEQSCANS IN SYSPTPROF INCREASED BY 1 EVEN THOUGH QUERY PLAN SHOWED INDEX PATH HAS BEEN USED, NOTHING IS SEQUENTIAL. I plan to concentrate on the sequential scans against tables knvp and knvv. The "seqscans" numbers reported for these tables may be valid. Please let me know if you believe that I am misinterpreting these Informix statistics. Thanks for you input. Rick -----Original Message----- From: Rudy Fernandes [mailto:rferdy@americasm01.nt.com] Sent: Tuesday, January 04, 2000 6:19 AM To: informix-list@iiug.org Subject: Re: Bufwaits and Foreground Writes I think your first priority has got to be Application tuning - reducing the sequential scans on your large tables (high npused, high seqscans). For example, every sequential scan of table vbfa (npused = 390751) is going to attempt to clean out your BUFFERS twice over and some (assuming detached indexes). A table of this size (390751 * 4 = over 1.5 Gb ) should *never* be sequentially scanned as part of regular operations. Figure out why these scans are occurring and do something to stop them (update statistics, modify application to use existing indexes, modify existing indexes, add indexes). I would doing the following (a) Modify my instance for obvious discrepancies (I can't see anything really pressing) (b) Pick the top five tables in descending order of "sequential scans * npused" and work on reducing sequential scans. (c) Evaluate impact of such tuning (talk to users, examine instance statistics, examine SAP statistics) (d) Fine tune instance, if required (e) Go back to (b). You may well find dramatic improvements after the first iteration. Rudy "Bernstein, Rick" wrote: > Despite our tuning efforts, we are encountering a significant number of > bufwaits and foreground writeson our production SAP system. This system runs > IDS 7.30.UC7XK on a 4-way RS6000 R50 (AIX). > > 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 > ... > 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 > ...

"Bernstein, Rick" wrote: > > Rudy, > > In theory I agree with your assessment. > However, I do not really believe some of the sequential scan information > which is being reported. We only have 150,000 pages in our buffer pool. > Yet Informix is reporting fewer "pagreads" than "seqscans" for tables > which cannot be held in memory. How can this be true? > > I suspect that erroneous information is being reported, due to an > Informix bug. One such software defect is: > 150509 - SEQSCANS IN SYSPTPROF INCREASED BY 1 EVEN THOUGH QUERY > PLAN SHOWED INDEX PATH HAS BEEN USED, NOTHING IS SEQUENTIAL. > I plan to concentrate on the sequential scans against tables knvp and > knvv. The "seqscans" numbers reported for these tables may be valid. > > Please let me know if you believe that I am misinterpreting these > Informix statistics. That's what was told to me in the IDS 7.30.UC7 release. The first time I checked my largest table (9M rows at that time), I noticed tens of thousands of seqscans within a 36 hour timeframe. Since my box is only an HP K200 class box, I figure that my users would have been ready to lynch me if that statistic had been true. I also plan to check the validity of my indices as well as the necessity for indices. I'll have to wait until 7.30.UC10 or 7.31.UC4+ -- John Carlson Informix DBA WHSmith USA #include std_disclaimer.h /* These are my opinions, not my company's opinion */

Rick, Yep, you could be experiencing a bug which misreports seqscans. Your "onstat -p" pagreads figure is also fishy, considering that it is less than dskreads This could be another reporting bug, one that I have actually encountered at a Solaris-v7.30 uc3 site (seqscans reporting seemed OK there). To get a better handle on the statistics being reported, you could try initializing stats (onstat -z) and then correlating "onstat -p" results with those in sysptprof as they build from 0. (We noticed a 100% correlation to a point, after which some of the stats went their separate ways). While you have 100% correlation, rate a representative sample(s) against the 3 day stats that you posted. If the newer stats look more sensible, use them to determine which tables to examine in more detail (knvp and knvv appear to be excellent starting points). [Another method I use to rate tables is descending order of bufreads/npused] A history of daily statistics can also be extremely helpful. Ideally, separate OLTP stats (7am-6pm or so) from Batch stats (6pm-7am). All the best Rudy "Bernstein, Rick" wrote: > Rudy, > > In theory I agree with your assessment. > However, I do not really believe some of the sequential scan information > which is being reported. We only have 150,000 pages in our buffer pool. > Yet Informix is reporting fewer "pagreads" than "seqscans" for tables > which cannot be held in memory. How can this be true? > > I suspect that erroneous information is being reported, due to an > Informix bug. One such software defect is: > 150509 - SEQSCANS IN SYSPTPROF INCREASED BY 1 EVEN THOUGH QUERY > PLAN SHOWED INDEX PATH HAS BEEN USED, NOTHING IS SEQUENTIAL. > I plan to concentrate on the sequential scans against tables knvp and > knvv. The "seqscans" numbers reported for these tables may be valid. > > Please let me know if you believe that I am misinterpreting these > Informix statistics. > > Thanks for you input. > Rick