Re: Bufwaits and Foreground Writes

"Bernstein, Rick" wrote: > > 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. OK > > > 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? No I do not know of these. > (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. Sounds like a plan. > > > 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. OK. > > > 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. I know the recommendation I just never feel comfortable with that much of my recovery information at risk. > > > > > 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. I had guessed that they were. My point was just to watch it when you increase BUFFERS. > (snip) > > > > > > # Read Ahead Variables > > > > > > RA_PAGES 32 # Number of p