Informix

nested-group-icon.png

DB2

Expand all | Collapse all

New RFE for your review.

  • 1.  New RFE for your review.

    Posted Tue December 03, 2019 10:06 AM
    Please check out this RFE and vote on it if you think it would be a good thing:

    IBM Data & AI
    Aha remove preview
    IBM Data & AI
    With the ubiquity of intelligent storage, the value of readahead in the Informix server is reduced and excessive readahead can be detrimental to performance. However, since the introduction of the AUTO_READAHEAD parameter and the associated changes to the readahead algorithms in the engine that include readahead of data that was not included in earlier versions of the engine, not all readahead details are exposed for analysis making it impossible to tune the AUTO_READAHEAD setting accurately.
    View this on Aha >
    https://ibm-data-and-ai.ideas.aha.io/ideas/INFX-I-346



    ------------------------------
    Art Kagel
    ------------------------------


  • 2.  RE: New RFE for your review.

    Posted Sat June 06, 2020 08:31 PM


    Hi Art,

    What details are not included in the readahead output?


    Regards,

    David.



    ------------------------------
    David Williams
    ------------------------------



  • 3.  RE: New RFE for your review.

    Posted Sat June 06, 2020 11:36 PM
    David: 

    I wish I knew. One thing I know is that I get closer to an RAU value by adding up the per partnum numbers from onstat -g rah and the equivalent values from sysmaster. However, even those numbers are not complete.

    On a site that under the old algorithms reported an RAU of 99% using my formula, under the new algorithms is reporting only 40% utilization which makes no sense to me. 

    Art





  • 4.  RE: New RFE for your review.

    Posted Mon June 08, 2020 06:36 AM
    Hi Art,

    This subject is topical for me now as I spent some time at the weekend looking at this and could not make sense of it. Your post is somewhat cathartic for me.

    I currently use three ways of looking at read-ahead:

    1. System profile, either 'onstat -p' or sysprofile SMI view.

    2. Sysmaster query to find activity by the read-ahead threads.

    SELECT
      SUM(r.nreads) AS nreads,
      SUM(r.upf_bufreads) AS bufreads
    FROM
      systhreads t,
      sysrstcb r
    WHERE
      r.flags=524801 AND
      r.mttcb = t.th_addr;

    (This picks up a few other system threads as there seems to be no obvious way of identifying read-ahead threads via SMI, but as they do little I/O it does not affect the result much. You cannot use thread name reliably, even though they are usually named 'readahead_X', as one of our instances currently has a read-ahead thread named 'ustat_546328'!)

    3. And 'onstat -g rah': https://www.ibm.com/support/knowledgecenter/en/SSGU8G_14.1.0/com.ibm.adref.doc/ids_adr_1126.htm

    'onstat -g rah' is difficult to interpret so I wrote a few lines of Perl to parse the command, parse it again after a time and display the differences in nicely formatted columns and I can pipe the output through 'sort'. I can use 'partn' from the IIUG site to make the part numbers into friendly names.

    With 'onstat -g rah' it seems millions of buffer reads can be generated from a number of read-ahead operations (across all five types) in single figures.

    The results I get from these methods have different orders of magnitude although when graphed show similar patterns.

    I agree overall it is difficult/impossible to calculate the read-ahead utilisation ratio (pages read from disk then actually used) except at profile level as the efficiency metrics reported by 'onstat -g rah' are to do with fetching pages via read-ahead that were already buffered. I guess this is a different way of looking at things.

    There isn't really a question here, just comments really but I share your frustration with the metrics and from not having a deeper understanding of what the numbers actually represent.

    Ben.

    ------------------------------
    Benjamin Thompson
    ------------------------------



  • 5.  RE: New RFE for your review.

    Posted Mon June 08, 2020 08:12 AM
    Ben:

    Yup, did something similar. Here is my rah_report.ksh, as I noted to David, it gets me closer to a usable RAU, but not all the way:

    ## rah_report.ksh ###
    onstat -g rah|awk '
    BEGIN{  
           data=0; idx=0; idxdat=0; logs=0; lstcom=0;
           bufrds=0; dskrds=0;
           udata=0; uidx=0; uidxdat=0; ulogs=0; ulstcom=0;
           edata=0; eidx=0; eidxdat=0; elogs=0; elstcom=0;
    }
    /^0x/{
           bufrds = bufrds+$2;dskrds=dskrds+$3;
           data=data+$5; idx=idx+$7; idxdat=idxdat+$9; logs=logs+$11; lstcom=lstcom=$13;
           edata=$6;     eidx=$8;    eidxdat=$10;      elogs=$12;     elstcom=$14
           udata=udata + int(($5 * ($6 / 100)) + 0.5);
           uidx=uidx + int(($7 * ($8 / 100) + 0.5));
           uidxdat=uidxdat + int(($9 * ($10 / 100)) + 0.5);
           ulogs=ulogs + int(($11 * ($12 / 100)) + 0.5);
           ulstcom=ulstcom + int(($13 * ($14 / 100)) + 0.5);
    }
    END{  
           printf "data   = %10.1d  Used data   = %10.1d  Data Utilization:   %10.3f\n", data, udata, data ? (udata / data) * 100.00 : 0.00;
           printf "idx    = %10.1d  Used idx    = %10.1d  Idx Utilization:    %10.3f\n", idx, uidx, idx ? (uidx / idx) * 100.00 : 0.00;
           printf "idxdat = %10.1d  Used idxdat = %10.1d  Idxdat Utilization: %10.3f\n", idxdat, uidxdat, idxdat ? (uidxdat / idxdat) * 100.00 : 0.00;
           printf "logs   = %10.1d  Used logs   = %10.1d  Logs Utilization:   %10.3f\n", logs, ulogs, logs ? (ulogs / logs) * 100.00 : 0.00;
           printf "lstcom = %10.1d  Used lstcom = %10.1d  Lstcom Utilization: %10.3f\n", lstcom, ulstcom, lstcom ? (ulstcom / lstcom) * 100.00 : 0.00;
           printf "\tTot RA = %10.1d  Used RA     = %10.1d  \n", (data+idx+idxdat+logs+lstcom), (udata+uidx+uidxdat+ulogs+ulstcom)
           printf "Overall RAU:  %10.5f\n",(data+idx+idxdat+logs+lstcom) ? (udata+uidx+uidxdat+ulogs+ulstcom)/(data+idx+idxdat+logs+lstcom) * 100 : 0;
           printf "\nbufrds = %10.1d\ndskrds = %10.1d\n", bufrds, dskrds;
           printf "Cache Pct:      %7.3f\n\n", bufrds ? (100 - ((dskrds / bufrds) * 100)) : 0.00;
    }


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


    Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference.  Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.








  • 6.  RE: New RFE for your review.

    Posted Mon June 08, 2020 08:51 AM
    Thanks for that Art. What you are doing makes sense but the summary that comes out on our systems is a bit strange.

    This from one busy system which has a large working set, much of which is cached:

    data = 98501 Used data = 97226 Data Utilization: 98.706
    idx = 1258442 Used idx = 1105037 Idx Utilization: 87.810
    idxdat = 3878850 Used idxdat = 2 Idxdat Utilization: 0.000
    logs = 421296 Used logs = 1260 Logs Utilization: 0.299
    lstcom = 0 Used lstcom = 649686 Lstcom Utilization: 0.000
    Tot RA = 5657089 Used RA = 1853211
    Overall RAU: 32.75909

    bufrds = 28255272667
    dskrds = 83983473
    Cache Pct: 99.703

    A massive variation in read-ahead utilisation by type there. It is difficult to know if these figures are accurate.

    BTW I found this, which you may have already read:
    http://www.oninit.com/bench/autoreadahead.pdf

    For anyone not familiar with 'awk' there is a missing single quote at the end.

    Ben.

    ------------------------------
    Benjamin Thompson
    ------------------------------



  • 7.  RE: New RFE for your review.

    Posted Mon June 08, 2020 09:09 AM


    What I also find annoying is working out what has triggered excessive readahead!

    I will post the seperately but also note I add

    https://ibm-data-and-ai.ideas.aha.io/ideas/INFX-I-376 Add readahead per sessions/sql counters.

    Regards,
    David.

    ------------------------------
    David Williams
    ------------------------------



  • 8.  RE: New RFE for your review.

    Posted Mon June 08, 2020 09:34 AM
    David:

    I like your request, but would add RA pages accessed to the request. Then we can work out whether the RA is excessive. If we read 65 pages from a 128page RA block then another 128 pages are read. If those are never used we are wasting 74.8% of al RA pages and possibly pushing more useful data out of the cache. In that case I would want to change AUTO_READAHEAD to 1,64 which would trigger the second 64pages at 33 pages then use one of these new pages resulting in only 49.8% wasted and 128 fewer pages pushed out of cache.

    I had this argument with Scott at the time he implemented the new algorithm. He was insistent that his testing showed that it didn't matter. But he was never testing in a production-like environment. I have.

    What I want to know and cannot know, is this: Under the old algorithm if RAU was low I might decrease RA_PAGES from 128 all the way down to 16 or 32 and all would be better. Under the new algorithm since less RA is issued to begin with, since cached pages are considered, maybe 64 would be better, but I have no way to know that.

    Art

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


    Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference.  Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.