MDEV-7084: innodb index stats inadequate using constant

innodb_stats_sample_pages

Analysis: If you set the number of analyzed pages 
to very low number compared to actual pages on 
that table/index it randomly pics those pages 
(default 8 pages), this leads to fact that query 
after analyze table returns different results. If 
the index tree is small, smaller than 10 * 
n_sample_pages + total_external_size, then the 
estimate is ok. For bigger index trees it is 
common that we do not see any borders between 
key values in the few pages we pick. But still 
there may be n_sample_pages different key values, 
or even more. And it just tries to 
approximate to n_sample_pages (8).

Fix: (1) Introduced new dynamic configuration variable
innodb_stats_sample_traditional  that retains
the current design. Default false.

(2) If traditional sample is not used we use
n_sample_pages = max(min(srv_stats_sample_pages,
                         index->stat_index_size),
                     log2(index->stat_index_size)*
                          srv_stats_sample_pages);

(3) Introduced new dynamic configuration variable
stat_modified_counter (default = 0) if set
sets lower bound for row updates when statistics is re-estimated.

If user has provided upper bound for how many rows needs to be updated
before we calculate new statistics we use minimum of provided value
and 1/16 of table every 16th round. If no upper bound is provided
(srv_stats_modified_counter = 0, default) then calculate new statistics
if 1 / 16 of table has been modified
since the last time a statistics batch was run.
We calculate statistics at most every 16th round, since we may have
a counter table which is very small and updated very often.
@param t table
@return true if the table has changed too much and stats need to be
recalculated
*/
#define DICT_TABLE_CHANGED_TOO_MUCH(t) \
	((ib_int64_t) (t)->stat_modified_counter > (srv_stats_modified_counter ? \
	ut_min(srv_stats_modified_counter, (16 + (t)->stat_n_rows / 16)) : \
		16 + (t)->stat_n_rows / 16))

1 files changed, 2 insertions, 0 deletions

diff --git a/storage/innobase/include/srv0srv.h b/storage/innobase/include/srv0srv.h
index e48aca84317..2b1a640ae8b 100644
--- a/storage/innobase/include/srv0srv.h
+++ b/storage/innobase/include/srv0srv.h
@@ -211,6 +211,8 @@ extern ulint	srv_fast_shutdown;	 /* If this is 1, do not do a
 extern ibool	srv_innodb_status;
 
 extern unsigned long long	srv_stats_sample_pages;
+extern unsigned long long	srv_stats_modified_counter;
+extern my_bool			srv_stats_sample_traditional;
 
 extern ibool	srv_use_doublewrite_buf;
 extern ibool	srv_use_atomic_writes;