RE: Bufwaits and Foreground Writes

Art, Thank you for your recommendations. I do have some follow-up questions and comments. Rick > -----Original Message----- > From: Art S. Kagel [mailto:kagel@bloomberg.net] > Sent: Friday, December 31, 1999 8:54 AM > To: informix-list@iiug.org > Subject: Re: Bufwaits and Foreground Writes > > > Got to toss in my $0.02US. Everyone is giving you basically good > advice, except those Informix guys (maybe I should give a Performance > Tuning class for Informix consultants and trainers!). I just > wanted to > add the weight of my opinion so you take this good advice. Read on. > > "Bernstein, Rick" wrote: > > > > 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). (snip) > > > 3) Large number of seqscans. > > Add indexes where needed. Update stats using the recommended > suite of > UPDATE STATISTICS commands as stated in the Performance Guide and> implemented in my dostats utility (and others). SAP provides a sapdba tool, which I use for updating statistics. It does use the Informix recommended approach. It also stores some related information within SAP tables. > > Should I be concerned about these statistics? Or are they > > possibly caused by product defects 108509 and 115741? Are you familiar with these defects? If so, do you know how to determine if the statistics are being skewed by these defects? (snip) > > 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 > > If these stats are for the entire 3 days you buffer turnover rate is > about every 45 minutes which is not great but not terrible > either. I'd > still recommend increasing BUFFERS by AT LEAST 50% maybe doubling it. I recently increased the BUFFERS by 40% to their current level. Any further increases will need to wait for an upcoming hardware upgrade. At that time I should be able to increase buffers by 750-900 Mb. > > > 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 > Your bufwaits ratio is about 20% (quick calc) which is killing > performance. Go for 128 LRUS and CLEANERS (CLEANERS should > be >= LRUS). I had tried LRUS = 127 and CLEANERS = 127. (After attending the Informix class, I lowered them). Neither settings seemed to make a noticeable difference. However, I plan to set them back to 127. > > ixda-RA idx-RA da-RA RA-pgsused lchwaits > > 23508356 451550 100557 23854808 466298 > > RA utilization is good at about 99.5%. > (snip) > > PHYSBUFF 1024 # Physical log buffer size (Kbytes)> > Too large it puts too much of the physical log at risk and takes too > long to flush at checkpoint time when it will hold up transactions. > A buffer of 64 -> 128 should be plenty. This value is based on SAP's published recommendations. Do you disagree with their criteria for setting this value? (A similar recommendation was also provided for LOGBUFF). o PHYSBUFF Installed value: 1024 This parameter determines the size of the two buffers in which the physical logs will be stored temporarily. With the command `onstat -l`, you can check whether the set size is sufficient for your system. The value "pages/io" should be at least 75% of the value "bufsize". Oherwise, you should change the parameter PHYSBUFF and watch the change of the "pages/io" output. Example: Physical logging Buffer bufused bufsize numpages numwrits pages/io P-2 759 768 276464 371 745.19 phybegin physize phypos phyused %used 200035 220000 117989 47149 21.43 If the ratio between "pages/io" and "bufsize" is less than 75%, you should decrease the value for PHYSBUFF. If the ratio between "pages/io" and "bufsize" is more than 75%, you should increase the value for PHYSBUFF. > > > LOGBUFF 32 # Logical log buffer size (Kbytes)> > LOGSMAX 100 # Maximum number of logical > log files > > CLEANERS 61 # Number of buffer cleaner > CLEANERS 128> > processes > > 122699rb > > SHMBASE 0x30000000 # Shared memory base address > > SHMADD 262144 # Size of new shared mem > segments (Kb)> > 112199rb > > SHMTOTAL 0 # Total shared memory > (Kbytes). 0=>unlimited > > CKPTINTVL 1800 # Check point interval (in sec)> > 090498rb > > LRUS 61 # Number of LRU queues > > LRUS 128> > > 122699rb > > > > # Changed for database reorganizations > > #BUFFERS 114600 # Maximum number of shared buffers > > 121199rb > > BUFFERS 150000 # Maximum number of shared buffers > > BUFFERS 300000> > > 122699rb > > SHMVIRTSIZE 786432 # Initial virtual shared> memory seg sz > > 112199rb > > LRU_MAX_DIRTY 2 # LRU percent dirty begn> cleaning limit > > 060199rb > > LRU_MIN_DIRTY 1 # LRU percent dirty end cleaning> When you increase BUFFERS watch the checkpoint duration. You > may have > to reduce these to 1 & 0 to maintain shorter checkpoints. > Our current checkpoint duration times are acceptable. They are usually under 6 seconds with peaks in the 6-9 sec range. (snip) > > > > # Read Ahead Variables > > > > RA_PAGES 32 # Number of pages to> attempt to read ahead > > RA_THRESHOLD 28 # Number of pages left> before nxt group > > You might reduce the next a bit to delay the readahead. It might > improve your write cache % a bit (nothing dramatic). Try 32 & 16 > unless your disk farm is very slow. Interesting. I had this value set to 16 before I attended the Informix Performance and Tuning class. The instructor recommended that the values be closer together. > > > WSTATS 1 # Enable wait statistics for SAP> > This is expensive if you can live without it, do. > (snip) > > TBLSPACE_STATS 1 >@@NL@