Informix

 View Only

  • 1.  Warning in log file

    Posted Fri April 30, 2021 12:28 PM
    We just upgraded a system from 11.70 to 14.10 Informix on Redhat 7.  We are getting a new warning in the log file:
     10:56:17  Warning: update statistics low using sampling may generate inaccurate index statistics for index informix.x0prv08u due to data skew
     10:56:41  Warning: update statistics low using sampling may generate inaccurate index statistics for index informix.x0prfaou due to data skew

    I was looking in the log file because the application team was experiencing extremely slow inserting/updating to the tables. I opened an IBM PMR and the engineer assigned said to turn on Auto Update Statistics.  Will that fix this error?  I don't use update statistics low (exactly) I use a version of @Art Kagel dostats script that does special high/medium/low operations on the indexes etc, every night during maintenance windows.  If I switch to auto update statistics, will I lose that flexibility of statistics?  Or will the on demand identification of the sampling getting inaccurate help with this?  The indexes called out above are both the unique key for the tables.

    My team is skeptical about AUS since it caused us a lot of pain in older versions of Informix, but of course those issues have probably been resolved.

    Thanks any advance for any help.​

    ------------------------------
    Kate Tomchik
    ------------------------------

    #Informix


  • 2.  RE: Warning in log file

    IBM Champion
    Posted Fri April 30, 2021 12:49 PM
    This message is generated when you have USTLOW_SAMPLE enabled in the config, which tells update stats to do a sampling of the index pages when running low stats.  It should speed up updating stats for indexes, but if Informix determines that the data in the index is heavily skewed, then it will issue the warning.  It's not necessarily a problem, and I don't know if there's a whole lot you can do about it apart from turning off USTLOW_SAMPLE, or following dostats with some update stats for the individual tables with the environment set to disable USTLOW_SAMPLE (set environment USTLOW_SAMPLE "0";).  I don't see how AUS would change things, unless it changes sampling dynamically...but I doubt it.

    ------------------------------
    Mike Walker
    ------------------------------

    Original Message


  • 3.  RE: Warning in log file

    IBM Champion
    Posted Fri April 30, 2021 01:59 PM
    Hey Kate:

    See Mike's response for the bulk of what I need to say. Also, AUS will, if there are already stats there from a dostats run, just duplicate what's already there. Also AUS will obey the USTLOW_SAMPLE parameter and continue generating sampled basic stats so unless you turn off USTLOW_SAMPLE nothing will change. Indeed, disabling that for a dostats run will also fix the problem (if, as Mike indicated, one actually exists). Just because the index is skewed doesn't mean that the sampled stats are bad, just that the potential for less than ideal stats is there. Given that there is a performance issue, that may indeed be the case.

    But look carefully at the query plans for slow queries. Fixing the LOW stats may not be enough. Going from v11.70 to v14.10 includes two upgrades to the optimizer.



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

    Original Message