Description:
Problem:
InnoDB statistics are represented in dict objects in the InnoDB
cache, and updated by InnoDB background statistics thread
automatically. The server code fetches these statistics by info()
calls, using HA_STATUS_VARIABLE and HA_STATUS_CONST for variable
and constant statistics, respectively.
Most constant statistics never change in the lifecycle of a table
thus is safe to be cached for any long time; however, there are a
few exceptions such as rec_per_key. Note that KEY::rec_per_key is
actually the server counterpart of dict_index_t::n_diff_key_vals
in InnoDB dict objects.
During query processing, variable statistics are fetched when the
optimizer is initialized as well some other code paths, with
TABLE_LIST::fetch_number_of_rows(). However, constant statistics
are only fetched by open_table_from_share() when a table is opened
with a table cache miss. In comparison, the ANALYZE command does
statistics gathering directly then fetches both.
There should be some reliable mechanism in addition to the rarely
scheduled ANALYZE commands, to notify the server code that some
critical constant statistics, n_diff_key_vals in particular, have
been changed in the InnoDB layer.
How to repeat:
Please see the testcase in the reported patch
Suggested fix:
Suggested fix:
In practice, variable statistics requests are always issued during
query processing, so constant statistics requests could be
performed in a piggyback manner for any underlying change.
There are actually three kinds of cases with respect to update:
1) reset right after emptying the table,
2) recalculation suggested by table modification counter,
3) reload event from physical replication (redo log).
Each case sets a dedicated flag in the dict object. The flag can
then be examined when handling variable statistics requests. When
it is set, constant statistics are additionally fetched, so that
the optimizer gets an up-to-date copy.
Added sysvar:
innodb_stats_notify_change - To enable the additional fetch.
Notes:
dict_table_t::stat_n_rows is updated by dict_table_n_rows_inc() or
dict_table_n_rows_dec() immediately in DML code path, besides by
analyze task when dict_table_t::stat_modified_counter exceeds a
certain threshold. In the former case, there will be a kind of
inconsistency between stat_n_rows and n_diff_key_vals.
TABLE objects share the same rec_per_key instance in the
TABLE_SHARE object. Update to the shared copy is immediately
visible to any other query using the table. So the optimizer might
get an inconsistent view of statistics during optimization.
Inconsistency issues are to be handled separately.
We decided not to treat rec_per_key as a variable statistic, because
of potential performance degression in the case of too many indexes
and the chance of reading it partially without lock when the update
operation is performed concurrently.
