Patch to Disable Innodb’s Dynamic Stat Generation

Alright my stats patch appears to be working ( its a first step anyways ), eventually this is going to make it into waffle grid… but I thought it would be useful as a standalone patch.  Basically what this patch does is turn off the dynamic collection of stats by disabling the calls to dict_update_statistics_low & btr_estimate_number_of_different_key_vals for everything but the analyze statement. At this point its not really refined, but it does appear to work.  i.e. It will need more refining in the future and may not 100% work in every little case.  The short of it, this means that once you set your stats you should not have to worry about the cardinality of your tables changing dramatically.

Other databases ( even other storage engines ) allow you to control when and how your stats are gathered.  Lots of shops analyze their tables once a week using a large sample set with the idea that their execution plans will stay consistent until they next run analyze.  By default Innodb is going to refresh stats whenever it hits certain events, it could be a show table status is called, show index, when a certain amount of data has changed, etc.   A real world example of this dynamic stat generation can be seen when running DBT2.  I kicked off a test that ran for 20 minutes and tracked all the calls to the update statistics function, in that 20 minute it issued a refresh over 5,000 times.  Now the total time of this stat collection was minimal, so your not killing performance but the changes can drastically change your execution plans. See my rant here.

My goal is to try and have greater control over the stats innodb uses, I think I succeed With this patch.  You can turn off dynamic stat generation, run analyze with a depth of 10000 pages if you like and not worry about the stats refreshing and your execution plans going wonky until you tell it to refresh or you restart MySQL.  Stats will still be run at least one time at startup, but they will only get updated after this initial run if you issue an analyze table.  To acheive this effect, the patch adds 3 variables to the innodb configuration.    The first is innodb_dyn_stats_enabled, by default this is turned on (which means innodb will update stats exactly like they do today).   The second is  innodb_stats_page_depth, this dictates how deep an anlyze table should dig when generating table statistics.  The default value for innodb_state_page_depth is 8.  The final new parameter is innodb_print_detailed_stats is really their for debugging purposes, setting it higher will output more data to the error log.

In addition to these variables I am also logging some extra data in show innodb status.

Special Stats
Number of times Estimating Keys 24
Avg Time Spent Estimating Keys 1117368 (us)
Number of Update Statistics 18
Avg Time Spent updateing stats 1489844 (us)
Number of calls to flush batch : 3095
Number of calls to flush batch from srv: 172
Avg Time spent flushing batch: 173445 (us)
Number of calls to flush via free margin : 2194
Avg Time spent flushing free margin: 187994 (us)

Basically you have the number of calls to the estimating keys function, along with the number of calls to the dict_update_statistics_low function and how long each took. The flush stats have nothing to do with the stats, but I am testing adding these to waffle grid, so I left them in. Here you can see the number of calls to the buf_flush_batch and how long each call took.  The calls to flush batch from srv are the calls from the main server thread (srv0srv.c)  to buf_flush_batch.  These calls in the main thread are effected in the google/percona patches by io_capacity.  The second set of calls to buf_flush_batch are the calls to the free_margin function ( which are not effected by the patches ) , these flushes happen before you read new data into the buffer pool.    I did this for two reasons, the first I want to know how long my flushes are taking, and secondly  I wanted to check the impact of io_capacity.  Setting the io_capacity is not nearly as useful at lower settings as I had hoped in my initial testing as most of the BP pool is flushed by calling free_margin, but this is a topic for another blog post.

With the extra logging to the error log I now display the following for statistics collection:

–>key_estimate name: PRIMARY time:33
upd_stats time name: dbt2/warehouse :51
–>key_estimate name: PRIMARY time:8706
upd_stats time name: dbt2/district :8718
–>key_estimate name: PRIMARY time:76912
–>key_estimate name: c_w_id time:58712
upd_stats time name: dbt2/customer :135647
–>key_estimate name: PRIMARY time:40818
–>key_estimate name: myidx1 time:38766
upd_stats time name: dbt2/new_order :79605
–>key_estimate name: PRIMARY time:99731
upd_stats time name: dbt2/order_line :99742
–>key_estimate name: PRIMARY time:82571
upd_stats time name: dbt2/stock :82583
–>key_estimate name: GEN_CLUST_INDEX time:82093
upd_stats time name: dbt2/history :82104
–>key_estimate name: PRIMARY time:76142
–>key_estimate name: o_w_id time:55786
upd_stats time name: dbt2/orders :131952
–>key_estimate name: PRIMARY time:94710
upd_stats time name: dbt2/item :94723

And for Flush statistics I added the following:

Batch Flush page count: 33 time :126440
–>Batch Flush <70 flush 10 :126447
Batch Flush page count: 139 time :247270
–>Batch Flush free margin pages: 139 Time:247276
Batch Flush page count: 130 time :256011
–>Batch Flush free margin pages: 130 Time:256016
Batch Flush page count: 123 time :178015
–>Batch Flush free margin pages: 123 Time:178020
Batch Flush page count: 118 time :163377
–>Batch Flush free margin pages: 118 Time:163381

Calling the analyze table function will result in:

Calling the ha_innobase::analyze function
We are updating stats via info!!!
–>key_estimate name: PRIMARY time:135853
–>key_estimate name: myidx1 time:581897
upd_stats time name: dbt2/new_order :717785

You can grab this here:

A note, I make no guarantees with this, I wrote it over the last couple of days in my spare time.   While I have run sysbench & dbt2 with this on and off  I have not fully tested this, so use at your own risk…  but so far it looks pretty good. You will still see in the processlist command “updating statistics”, its just a few function are no longer being called.   While innodb_dyn_stats_enabled can be set dynamically, its best right now to set this in the my.cnf,  otherwise you will still dynamically gather stats until you issue the analyze statement.

This is my first crack at this, hopefully a first step towards a more streamlined process of gathering stats.  As others mentioned in the comments of my previous rant, there are a lot of extra features  people want here ( things like loading the stats from one server to another, and modifying the stats on your own ), hopefully this will serve as a gateway to those changes.

This entry was posted in mysql, performance, Waffle Grid. Bookmark the permalink.

4 Responses to Patch to Disable Innodb’s Dynamic Stat Generation

  1. Xaprb says:

    I don’t see a license on the patch… this type of functionality is the kind of thing I like to see in the Percona patches. How would you feel about that? (Vadim has the final say, not me.)

  2. matt says:

    I put this up on launchpad under the GPL.

  3. Mark Leith says:

    Hi Matt,

    I’ve already added something like this at the request of a customer – innodb_stats_on_metadata:

    Unfortunately, during a re-work of plugin variables, it was converted to only a command line variable (not visible in SHOW VARIABLES), this has been fixed and should be in the next release hopefully:

    I do like some of the rest of the functionality in your patch however, so I would encourage you to try and clean it up by just building on innodb_stats_on_metadata instead. No sense in duplication.. :)

  4. matt says:


    I noticed that in their… but did not see it exposed. The real question is do the innodb_stats_on_metadata & innodb_dyn_stats_enabled provide similar enough functionality to be merged as one variable….

    My Goal is a little different then what yours was, I want to lock stats down and only allow them to be changed by the analyze command. Part of that was hacking info(). Lots of things end up calling info, including analyze… I have to allow analyze to run, otherwise my patch is usless. I could make stats_on_meta allow for analyze to be run… I will have to think on that. But if I did this I would either have to make stats on meta disable all the other statistics gathering ( including in the row functions ) or would have to have people set both stats_on_meta & dyn_stats to get the desired functionality.

    Thoughts are welcome.