Informix

Informix

Connect with Db2, Informix, Netezza, open source, and other data experts to gain value from your data, share insights, and solve problems.

 View Only

  • 1.  Anyone aware of an auto update statistics bug in 12.10.xC4

    Posted Mon November 25, 2019 11:23 AM

    Hello everyone,

     

    Assessing a server that crashed three times out of five at/around 1:11 AM on a Saturday morning due to grabbing memory all the way up to 16 GB (regular Informix shmtotal footprint is 1 GB) and hitting an "operating system" limit (this is workgroup edition on Linux) and crashing.

     

    After some research, the *only* session that is running at that time is the auto update statistics refresh job!   I am reasonably certain this is the main culprit but I am not certain as to why and whether or not it is a problem on its own or only when this couples with some other activities/scenarios.

     

    I went through some of the bug lists but did not come across anything on this.

     

    Has anyone seen/is anyone aware of such a problem, possibly a bug?

     

    Thank you,

     

    Hal Maner

    M Systems International, Inc.

    Phone: +1 (919) 493-6012 ext.4009

    hmaner@msystemsintl.com

    www.msystemsintl.com

     


    #Informix


  • 2.  RE: Anyone aware of an auto update statistics bug in 12.10.xC4

    Posted Mon November 25, 2019 11:29 AM
    I'd suspect that VP_MEMORY_CACHE_KB is set to DYNAMIC mode and that was the cause.

    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.






    Original Message


  • 3.  RE: Anyone aware of an auto update statistics bug in 12.10.xC4

    Posted Mon November 25, 2019 11:34 AM

    VP_MEMORY_CACHE_KB is set to 0 (disabled). 

     

    AUTO_TUNE is set to 2 which to me is a strange value.

     

    Hal

     




    Original Message


  • 4.  RE: Anyone aware of an auto update statistics bug in 12.10.xC4

    Posted Mon November 25, 2019 11:46 AM
    Hmm. Not just strange, not a valid setting for AUTO_TUNE. It accepts either 0 or 1. Effectively off or on.

    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.






    Original Message


  • 5.  RE: Anyone aware of an auto update statistics bug in 12.10.xC4

    Posted Mon November 25, 2019 11:55 AM

    Well, not being an expert on nor a fan of these "AUTO..." variables (but I understand and accept why they are there), "AUTO_TUNE" value is the default value for the bunch of other "AUTO_..." variables, and *one* of them, AUTO_READAHEAD, *does* take a 2 as a valid value ("aggressive") according to the documentation (and it says "not recommended" :)     So, theoretically then, 2 might be valid albeit problematic value.   I also just checked and found that they do have a AUTO_READAHEAD 1,128 in the onconfig, so that should override the default of 2...

     

    Hal

     




    Original Message


  • 6.  RE: Anyone aware of an auto update statistics bug in 12.10.xC4

    Posted Mon November 25, 2019 12:00 PM
    Yea, Scott Lashley wrote that readahead code and STRONGLY recommended not setting AUTO_READAHEAD to 2. But, as you say, even if setting AUTO_TUNE to 2 might affect the setting of AUTO_READAHEAD, the specific setting of that parameter overrides the setting of AUTO_TUNE. But the docs are very specific, only 0 and 1 are valid settings.

    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.






    Original Message


  • 7.  RE: Anyone aware of an auto update statistics bug in 12.10.xC4

    Posted Wed November 27, 2019 03:59 AM
    Hi Hal,

    Do you use fragment level statistics anywhere? If you do there is a bug which could be relevant:
    idsdb00096393 Large amount of memory used by fragment level statistics. I think this is fixed in 12.10.xC11.

    Have you considered simply upgrading to 12.10.xC13? There are a lot of defect fixes over xC4, which was released over five years ago.

    To pick up on what Art has suggested, I don't know of anything related to Auto Update Statistics which would stress the VP cache. It is straightforward to tell if it's on or not without understanding AUTO_TUNE: run 'onstat -g vpcache'.

    For more detail on how to monitor it and how much memory it is consuming:
    https://informixdba.wordpress.com/2013/11/11/monitoring-virtual-segment-usage-and-the-cpu-vp-caches/

    Ben.

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

    Original Message


  • 8.  RE: Anyone aware of an auto update statistics bug in 12.10.xC4

    Posted Wed November 27, 2019 07:13 AM

    Ben, thank you for your feedback.

     

    I do not think there are any fragmented tables here – this is a workgroup edition instance that I was asked to look into briefly.  It is not a server that I look after and I am not sure about the likelihood or plans about an upgrade.

     

    VP cache is disabled based on the onconfig setting of 0 for VP_MEMORY_CACHE_KB.

     

    Hal Maner

    M Systems International, Inc.

     




    Original Message


Related Content