Informix

 View Only
Expand all | Collapse all

Clear BUFFERPOOL

  • 1.  Clear BUFFERPOOL

    Posted Thu July 07, 2022 05:51 PM
    Hi,

    We are tuning some queries on Informix 14.10.FC8.  Is there a command or a way to clear the BUFFERPOOL without restarting Informix?  The first run of the query takes about 30 seconds though since the data is in memory subsequent runs of the query take less than a second.

    Thank You,

    --Dave

    ------------------------------
    Dave Baligo
    ------------------------------

    #Informix


  • 2.  RE: Clear BUFFERPOOL

    Posted Thu July 07, 2022 06:46 PM

    Something like this might work:

    Once -

    create function zap_all_buffers() returns Integer external name '(zap_all_buffers)' language C variant;

    Then -

    execute function zap_all_buffers();

    Use at your own risk, and, preferably, not in production environment.



    ------------------------------
    Vladimir Kolobrodov
    ------------------------------



  • 3.  RE: Clear BUFFERPOOL

    Posted Fri July 08, 2022 03:44 AM
    Probably the greatest Informix trick since sliced onion, thank you Vladimir!

    07/08/2022 09:36:17   All dirty buffers flushed to disk. All cached buffers invalidated.
    
    IBM Informix Dynamic Server Version 14.10.FC5IE -- On-Line -- Up 2 days 01:35:22 -- 5307756 Kbytes
    2022-07-08 09:36:31
    
    Buffer pool page size: 2048
    partnum  total    btree    data     other    dirty   
    0        4        0        0        4        0       
    1048577  3        0        3        0        0       
    
    Totals:  7        0        3        4        0       
    
    Percentages:
    Data  42.86 
    Btree 0.00  
    Other 57.14 
    
    Buffer pool page size: 4096
    partnum  total    btree    data     other    dirty   
    12582913 13       0        13       0        0       
    
    Totals:  13       0        13       0        0       
    
    Percentages:
    Data  100.00
    Btree 0.00  
    Other 0.00  
    ​


    ------------------------------
    Davorin Kremenjas
    ------------------------------



  • 4.  RE: Clear BUFFERPOOL

    IBM Champion
    Posted Fri July 08, 2022 03:57 AM
    Wait until you see the non-sliced onion ;-)

    ------------------------------
    Andreas Legner
    ------------------------------



  • 5.  RE: Clear BUFFERPOOL

    Posted Fri July 08, 2022 04:36 AM
    Thanks but no thanks, it makes my eyes water too much every time I see/use it ;-)
    Still have it saved somewhere though, must have got it from a friend...

    ------------------------------
    Davorin Kremenjas
    ------------------------------



  • 6.  RE: Clear BUFFERPOOL

    IBM Champion
    Posted Fri July 08, 2022 07:08 AM
    Even better when they ship onion with the product :-)

    Cheers
    Paul

    Paul Watson
    Oninit LLC
    +1-913-387-7529
    www.oninit.com
    Oninit®️ is a registered trademark of Oninit LLC





  • 7.  RE: Clear BUFFERPOOL

    Posted Fri July 08, 2022 07:11 AM
    What, you guys are all too fancy for onmode -B reset? :)

    In your defense this is an undocumented 14.10.xC2 feature. But it is supported, and will be going forward.

    onmode -B            --       Flush all dirty buffers without a checkpoint. Leave the cache intact.
    onmode -B reset   --       Flush all dirty buffers and invalidate them so that the cache is essentially "empty".

    -jc

    ------------------------------
    John Lengyel
    ------------------------------



  • 8.  RE: Clear BUFFERPOOL

    IBM Champion
    Posted Fri July 08, 2022 07:13 AM
    Didn't want to snatch it from you ;-)

    ------------------------------
    Andreas Legner
    ------------------------------



  • 9.  RE: Clear BUFFERPOOL

    Posted Fri July 08, 2022 07:19 AM
    Oh great. Now I have to stop taking credit for onion.

    ------------------------------
    John Lengyel
    ------------------------------



  • 10.  RE: Clear BUFFERPOOL

    IBM Champion
    Posted Fri July 08, 2022 07:19 AM
    Use undocumented features ? Who would do such a thing

    Cheers
    Paul

    Paul Watson
    Oninit LLC
    +1-913-387-7529
    www.oninit.com
    Oninit®️ is a registered trademark of Oninit LLC





  • 11.  RE: Clear BUFFERPOOL

    Posted Fri July 08, 2022 09:21 AM

    Thank you John and Vladimir,

    I tried both "onmode -B reset" and the "zap_all_buffers" functions and I see they both give the same output in the message log:
    All dirty buffers flushed to disk. All cached buffers invalidated.

    So I'm guessing they are doing the same thing behind the scenes.  As far the query timing, after running the onmode/zap_all_buffers the query takes about 3 seconds instead of 1 second so it definitely seems to be doing something.  Though when I do the good old fashioned bounce of the DB then the query goes back to 30 seconds on the first run and then 1 second on subsequent runs.

    Thank you for the suggestions.

    --Dave



    ------------------------------
    Dave Baligo
    ------------------------------



  • 12.  RE: Clear BUFFERPOOL

    IBM Champion
    Posted Fri July 08, 2022 09:33 AM
    How would this be making sense, leaving 90% of those 30 seconds unexplained?

    Did you have 'explain' on, in all these cases?  What's the query run time there, in query statistics?
    Can you compare disk reads (and writes) between the three cases (e.g. onstat -D)?

    ------------------------------
    Andreas Legner
    ------------------------------



  • 13.  RE: Clear BUFFERPOOL

    IBM Champion
    Posted Fri July 08, 2022 11:02 AM
    Dave:

    I found the same thing years ago when this trick to clear the buffer cache was revealed to me and I tested it. Clearing/invalidating the buffers without restarting the engine is definitely different than bouncing the engine and so I never publicized it, nor do I use it when I'm performance testing something.

    What I'd REALLY like to see is the ability to save the cache state to disk somehow and to tell the engine to reload that saved state on startup so that we can avoid the cache misses after a server restart! Hmm, maybe I'll put an RFE in for this. Anyone like the idea?

    Art

    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------



  • 14.  RE: Clear BUFFERPOOL

    Posted Fri July 08, 2022 12:24 PM
    >> Anyone like the idea?

    Yes, very much so. Having repeatable test cases, not only from the functional point of view, but also the non-functional stuff like performance would certainly help a lot with creating high quality continuous integration pipelines for apps with Informix as an underlying data source.


    ------------------------------
    Davorin Kremenjas
    ------------------------------



  • 15.  RE: Clear BUFFERPOOL

    IBM Champion
    Posted Fri July 08, 2022 12:46 PM
    Davorin:

    I was actually thinking of restarting a production system and having the cache at least filled to the same state as it was on shutdown. The save file doesn't even have to be large, it can just contain the page addresses to be loaded at startup. A 10 million page cache would create a 40MB to perhaps 80MB save file (depending on what metadata needed to be save with the page address.

    I agree that it would be useful for consistent testing following a restart as well.

    Art

    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------



  • 16.  RE: Clear BUFFERPOOL

    Posted Sat July 09, 2022 02:43 AM
    Sounds good, perhaps something (conceptually) similar to adding the buffs option to onstat -o and then having an oninit option to load it back in at startup (akin to the -FILE option for env. vars).

    ------------------------------
    Davorin Kremenjas
    ------------------------------



  • 17.  RE: Clear BUFFERPOOL

    IBM Champion
    Posted Sun July 10, 2022 12:29 AM
    Davorin:

    Yup, something like that was what I was thinking as well.

    Art

    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------



  • 18.  RE: Clear BUFFERPOOL

    Posted Fri July 08, 2022 03:37 PM
    As Andreas said this result is not what we would have expected. It's hard to picture what would be taking the bulk of those 30 seconds if not I/O. There are many caches in shared memory that neither of these methods clears (such as the partition cache) but even if your query opened thousands of tables or prepared many SQL statements these numbers still wouldn't make sense.

    If you wouldn't mind working this mystery with us a little more we'd appreciate it. Your test is acting as if it's performing light scans in the 3-second case but no light scans in the 30-second case. Light scans can key off certain buffer pool stats and I wonder if onmode -B needs to reset something there as well.

    onstat -g ppf and onstat -g scn would be useful outputs to gather (somewhat rapidly) during your test. (Don't forget to run onstat -z right before each execution of the query.) The set explain output will also be helpful.

    Thanks.

    ------------------------------
    John Lengyel
    ------------------------------



  • 19.  RE: Clear BUFFERPOOL

    Posted Fri July 08, 2022 09:32 AM
    Very useful, thanks John.

    ------------------------------
    Davorin Kremenjas
    ------------------------------



  • 20.  RE: Clear BUFFERPOOL

    IBM Champion
    Posted Fri July 08, 2022 11:37 AM


    Hi,

    You did not tell us about that!!

    What other tricks are you are not tell us about? :->>

    Regards,

    David.



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



  • 21.  RE: Clear BUFFERPOOL

    IBM Champion
    Posted Fri July 08, 2022 11:45 AM

    Is that the documented definition of undocumented J