Bug #121642 innodb_stats_auto_recalc threshold is based on estimated row count, not actual row count
Description:
The documentation states that when both innodb_stats_persistent and innodb_stats_auto_recalc are ON, statistics are recalculated automatically once more than 10% of the rows in a table have been changed.
8.4 documentation (the same text appears in 9.7):
https://dev.mysql.com/doc/refman/8.4/en/innodb-persistent-stats.html
> The innodb_stats_auto_recalc variable, which is enabled by default, controls whether statistics are calculated automatically when a table undergoes changes to more than 10% of its rows.
Strictly speaking, however, the recalculation is triggered when the number of changed rows exceeds 10% of the *estimated* number of rows in the table, not the actual row count.
How to repeat:
# Check the server version and system variables
mysql> select version();
+--------------+
| version() |
+--------------+
| 8.4.11-debug |
+--------------+
1 row in set (0.00 sec)
mysql> show variables like 'innodb_stats_%';
+--------------------------------------+-------------+
| Variable_name | Value |
+--------------------------------------+-------------+
| innodb_stats_auto_recalc | ON |
| innodb_stats_include_delete_marked | OFF |
| innodb_stats_method | nulls_equal |
| innodb_stats_on_metadata | OFF |
| innodb_stats_persistent | ON |
| innodb_stats_persistent_sample_pages | 20 |
| innodb_stats_transient_sample_pages | 8 |
+--------------------------------------+-------------+
7 rows in set (0.01 sec)
# Create an InnoDB table
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> CREATE TABLE stat_test (i int primary key) ENGINE=InnoDB, STATS_PERSISTENT=1, STATS_AUTO_RECALC=1;
Query OK, 0 rows affected (0.04 sec)
mysql> insert into stat_test values (1), (10), (100);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
# Update the rows in the table (to trigger automatic statistics recalculation)
mysql> update stat_test set i = i + 1;
At this point, I obtained the following stack trace with gdb:
row_update_statistics_if_needed(dict_table_t * table) (/home/ec2-user/mysql8411/mysql-server/storage/innobase/row/row0mysql.cc:1117)
row_update_for_mysql_using_upd_graph(const byte * mysql_rec, row_prebuilt_t * prebuilt) (/home/ec2-user/mysql8411/mysql-server/storage/innobase/row/row0mysql.cc:2427)
row_update_for_mysql(const byte * mysql_rec, row_prebuilt_t * prebuilt) (/home/ec2-user/mysql8411/mysql-server/storage/innobase/row/row0mysql.cc:2450)
ha_innobase::update_row(ha_innobase * const this, const uchar * old_row, uchar * new_row) (/home/ec2-user/mysql8411/mysql-server/storage/innobase/handler/ha_innodb.cc:10080)
handler::ha_update_row(handler * const this, const uchar * old_data, uchar * new_data) (/home/ec2-user/mysql8411/mysql-server/sql/handler.cc:8128)
Sql_cmd_update::update_single_table(Sql_cmd_update * const this, THD * thd) (/home/ec2-user/mysql8411/mysql-server/sql/sql_update.cc:1000)
Sql_cmd_update::execute_inner(Sql_cmd_update * const this, THD * thd) (/home/ec2-user/mysql8411/mysql-server/sql/sql_update.cc:1827)
Sql_cmd_dml::execute(Sql_cmd_dml * const this, THD * thd) (/home/ec2-user/mysql8411/mysql-server/sql/sql_select.cc:782)
mysql_execute_command(THD * thd, bool first_level) (/home/ec2-user/mysql8411/mysql-server/sql/sql_parse.cc:3672)
dispatch_sql_command(THD * thd, Parser_state * parser_state) (/home/ec2-user/mysql8411/mysql-server/sql/sql_parse.cc:5406)
dispatch_command(THD * thd, const COM_DATA * com_data, enum_server_command command) (/home/ec2-user/mysql8411/mysql-server/sql/sql_parse.cc:2136)
do_command(THD * thd) (/home/ec2-user/mysql8411/mysql-server/sql/sql_parse.cc:1465)
handle_connection(void * arg) (/home/ec2-user/mysql8411/mysql-server/sql/conn_handler/connection_handler_per_thread.cc:304)
pfs_spawn_thread(void * arg) (/home/ec2-user/mysql8411/mysql-server/storage/perfschema/pfs.cc:3067)
libc.so.6!start_thread
libc.so.6!clone3
row_update_statistics_if_needed() obtains the number of rows in the table by calling dict_table_get_n_rows():
https://github.com/mysql/mysql-server/blob/mysql-8.4.11/storage/innobase/row/row0mysql.cc#...
n_rows = dict_table_get_n_rows(table);
if (dict_stats_is_persistent_enabled(table)) {
if (counter > n_rows / 10 /* 10% */
&& dict_stats_auto_recalc_is_enabled(table)) {
dict_stats_recalc_pool_add(table);
table->stat_modified_counter = 0;
}
return;
}
dict_table_get_n_rows() is defined as follows, and it returns an estimate of the number of rows in the table:
https://github.com/mysql/mysql-server/blob/mysql-8.4.11/storage/innobase/include/dict0dict...
/** Gets the approximately estimated number of rows in the table.
@return estimated number of rows */
static inline uint64_t dict_table_get_n_rows(
const dict_table_t *table) /*!< in: table */
{
ut_ad(table->stat_initialized);
return (table->stat_n_rows);
}
Therefore, the threshold that triggers automatic statistics recalculation is based on the estimated number of rows in the table, not on the exact row count.
Suggested fix:
Please update the documentation to make it clear that automatic recalculation is triggered when changes exceed 10% of the **estimated** number of rows in the table.
For example:
The innodb_stats_auto_recalc variable, which is enabled by default, controls whether statistics are calculated automatically when a table undergoes changes to more than 10% of its rows, based on the estimated number of rows in the table.
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.038 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.