• 1.  need a point of view :)

    Posted 21 days ago
    Hello All
    here is (onstat -p) output, i noticed buwfaits and  (ixda-RA idx-RA da-RA logrec-RA RA-pgsused lchwaits) lines
    but need to know if i have to add buffers or do something on LRUs 

    thanks in advance

    dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached
    1849403017 2839793038 54105783272 96.58 2775958 5377481 115277147 97.59

    isamtot open start read write rewrite delete commit rollbk
    55434739891 141168653 502555846 38960713965 57990178 4827263 1037868 1111275 198376

    gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs
    10758 684 4248 0 0 0 0

    ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes
    0 0 0 85434.10 33211.77 15 78

    bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans
    32608128 2233 4832587678 0 0 150 811434 3440746

    ixda-RA idx-RA da-RA logrec-RA RA-pgsused lchwaits
    0 994604354 1292210364 0 1300935740 638429745

    John Smith

  • 2.  RE: need a point of view :)

    Posted 21 days ago

    Make a couple of quick calculations, I don't see anything on the onstat -p report that would indicate a need for more buffers, but I also notice the massive values for all of the counters so it may be that the system stats have not been zero'd for a long time so that quiet periods are averaged in. Two suggestions:

    1. Run onstat -z at least weekly preferrably shortly before your normal processing week starts (ie Sunday night for most businesses, but you know yours best).
    2. Go to my web site, download my ratios.shr_ak package and install the stored procedures in the ratios.sql file into sysmaster. Then you can either run the new_ratios.ksh script periodically or execute the ratios() stored procedure and process the results yourself. I would look at the metrics produced especially after a peak load period and at the end of the normal processing week, paying attention to the following metrics: Bufwaits Ratio, BTR#3, lock wait ratio, and sequential scan ratio. If you are running v11.50 or earlier the Readahead Utilization should be watched to see if your readahead settings are too aggressive or not aggressive enough. That ratio is useless starting with the new readahead algorithm in 11.70 and later (though there were changes in v14.10.FC7 that may allow me to create a new version of the RAU that will be useful - we'll see, I downloaded FC7 yesterday). 
    As noted above, if you are using v11.70 or later ignore the RA line values. Not all readahead is reported there by the new algorithm so the numbers are useless. 

    A BTR#3 over 6 is worrisome and over 10 is a clear indicator that you may benefit from more cache. But note that there are some sites whose processing causes high BTR3 numbers that are OK and not improved by more buffers. If you add buffers and the numbers don't improve, compare onstat -P output over time to see if much of the buffer exchanges are happening within the same table. (I have a script to do this that I can send you if you email me directly.) If that's what's happening then you will have discovered that your system's acceptable BTR#3 level is somewhere north of 10. I have a client whose system is fat and happy running with a BTR#3 around 40 and no quantity of additional buffer space will get it lower because they are constantly and heavily processing their newest data for a short period then ignoring it forever. That causes a very large number of new pages to be read into the cache constantly but not needed after a few minutes. It's almost like stream processing.



    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.