From 9095456e3a3267eef5495296e309a9c7249f4c87 Mon Sep 17 00:00:00 2001 From: hcduan Date: Tue, 25 Jan 2022 00:04:19 +0800 Subject: [PATCH] [bugfix] Prevent badly stale Key::rec_per_key for query optimization 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. 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. --- .../r/innodb_stats_notify_change.result | 204 ++++++++++++++++++ .../innodb/t/innodb_stats_notify_change.test | 92 ++++++++ sql/table.cc | 41 +++- storage/innobase/dict/dict0stats.cc | 17 ++ storage/innobase/handler/ha_innodb.cc | 16 ++ storage/innobase/handler/ha_innopart.cc | 9 + storage/innobase/include/dict0mem.h | 4 + storage/innobase/include/srv0srv.h | 1 + storage/innobase/srv/srv0srv.cc | 1 + 9 files changed, 384 insertions(+), 1 deletion(-) create mode 100644 mysql-test/suite/innodb/r/innodb_stats_notify_change.result create mode 100644 mysql-test/suite/innodb/t/innodb_stats_notify_change.test diff --git a/mysql-test/suite/innodb/r/innodb_stats_notify_change.result b/mysql-test/suite/innodb/r/innodb_stats_notify_change.result new file mode 100644 index 000000000..0d950c733 --- /dev/null +++ b/mysql-test/suite/innodb/r/innodb_stats_notify_change.result @@ -0,0 +1,204 @@ +set @saved_value= @@global.innodb_stats_notify_change; +# with innodb_stats_notify_change being OFF, records_per_key could be stale. +create table t1 (a int, b int, key (a), key (b)); +set global innodb_stats_notify_change = off; +insert t1 values (1, 1), (1, 2); +set debug='-d,print_records_per_key'; +select database_name,table_name,index_name,stat_name,stat_value from mysql.innodb_index_stats where table_name = 't1' and index_name in ('a', 'b') and stat_name = 'n_diff_pfx01'; +database_name table_name index_name stat_name stat_value +test t1 a n_diff_pfx01 1 +test t1 b n_diff_pfx01 2 +set debug='+d,print_records_per_key'; +select count(*) from t1; +count(*) +2 +Warnings: +Warning 168 print_records_per_key: test.t1 t1 a(a) = (1.000000) +Warning 168 print_records_per_key: test.t1 t1 b(b) = (1.000000) +Warning 168 print_records_per_key: test.t1 t1 a(a) = (1.000000) +Warning 168 print_records_per_key: test.t1 t1 b(b) = (1.000000) +analyze table t1; +Table Op Msg_type Msg_text +test.t1 analyze status OK +set debug='-d,print_records_per_key'; +select database_name,table_name,index_name,stat_name,stat_value from mysql.innodb_index_stats where table_name = 't1' and index_name in ('a', 'b') and stat_name = 'n_diff_pfx01'; +database_name table_name index_name stat_name stat_value +test t1 a n_diff_pfx01 1 +test t1 b n_diff_pfx01 2 +set debug='+d,print_records_per_key'; +select count(*) from t1; +count(*) +2 +Warnings: +Warning 168 print_records_per_key: test.t1 t1 a(a) = (2.000000) +Warning 168 print_records_per_key: test.t1 t1 b(b) = (1.000000) +Warning 168 print_records_per_key: test.t1 t1 a(a) = (2.000000) +Warning 168 print_records_per_key: test.t1 t1 b(b) = (1.000000) +truncate t1; +set debug='-d,print_records_per_key'; +select database_name,table_name,index_name,stat_name,stat_value from mysql.innodb_index_stats where table_name = 't1' and index_name in ('a', 'b') and stat_name = 'n_diff_pfx01'; +database_name table_name index_name stat_name stat_value +test t1 a n_diff_pfx01 0 +test t1 b n_diff_pfx01 0 +set debug='+d,print_records_per_key'; +select count(*) from t1; +count(*) +0 +Warnings: +Warning 168 print_records_per_key: test.t1 t1 a(a) = (1.000000) +Warning 168 print_records_per_key: test.t1 t1 b(b) = (1.000000) +Warning 168 print_records_per_key: test.t1 t1 a(a) = (1.000000) +Warning 168 print_records_per_key: test.t1 t1 b(b) = (1.000000) +drop table t1; +# with innodb_stats_notify_change being ON, records_per_key is up to update. +create table t1 (a int, b int, key (a), key (b)); +set global innodb_stats_notify_change = on; +insert t1 values (1, 1), (1, 2); +set debug='-d,print_records_per_key'; +select database_name,table_name,index_name,stat_name,stat_value from mysql.innodb_index_stats where table_name = 't1' and index_name in ('a', 'b') and stat_name = 'n_diff_pfx01'; +database_name table_name index_name stat_name stat_value +test t1 a n_diff_pfx01 1 +test t1 b n_diff_pfx01 2 +set debug='+d,print_records_per_key'; +select count(*) from t1; +count(*) +2 +Warnings: +Warning 168 print_records_per_key: test.t1 t1 a(a) = (2.000000) +Warning 168 print_records_per_key: test.t1 t1 b(b) = (1.000000) +Warning 168 print_records_per_key: test.t1 t1 a(a) = (2.000000) +Warning 168 print_records_per_key: test.t1 t1 b(b) = (1.000000) +analyze table t1; +Table Op Msg_type Msg_text +test.t1 analyze status OK +set debug='-d,print_records_per_key'; +select database_name,table_name,index_name,stat_name,stat_value from mysql.innodb_index_stats where table_name = 't1' and index_name in ('a', 'b') and stat_name = 'n_diff_pfx01'; +database_name table_name index_name stat_name stat_value +test t1 a n_diff_pfx01 1 +test t1 b n_diff_pfx01 2 +set debug='+d,print_records_per_key'; +select count(*) from t1; +count(*) +2 +Warnings: +Warning 168 print_records_per_key: test.t1 t1 a(a) = (2.000000) +Warning 168 print_records_per_key: test.t1 t1 b(b) = (1.000000) +Warning 168 print_records_per_key: test.t1 t1 a(a) = (2.000000) +Warning 168 print_records_per_key: test.t1 t1 b(b) = (1.000000) +truncate t1; +set debug='-d,print_records_per_key'; +select database_name,table_name,index_name,stat_name,stat_value from mysql.innodb_index_stats where table_name = 't1' and index_name in ('a', 'b') and stat_name = 'n_diff_pfx01'; +database_name table_name index_name stat_name stat_value +test t1 a n_diff_pfx01 0 +test t1 b n_diff_pfx01 0 +set debug='+d,print_records_per_key'; +select count(*) from t1; +count(*) +0 +Warnings: +Warning 168 print_records_per_key: test.t1 t1 a(a) = (1.000000) +Warning 168 print_records_per_key: test.t1 t1 b(b) = (1.000000) +Warning 168 print_records_per_key: test.t1 t1 a(a) = (1.000000) +Warning 168 print_records_per_key: test.t1 t1 b(b) = (1.000000) +drop table t1; +# with innodb_stats_notify_change being OFF, records_per_key could be stale (for partition table). +create table t2 (a int, b int, key (a), key (b)) +partition by range (a) ( +partition p0 values less than (100), +partition p1 values less than MAXVALUE +); +set global innodb_stats_notify_change = off; +insert t2 values (1, 1), (1, 2); +set debug='-d,print_records_per_key'; +select database_name,table_name,index_name,stat_name,stat_value from mysql.innodb_index_stats where table_name = 't2' and index_name in ('a', 'b') and stat_name = 'n_diff_pfx01'; +database_name table_name index_name stat_name stat_value +set debug='+d,print_records_per_key'; +select count(*) from t2; +count(*) +2 +Warnings: +Warning 168 print_records_per_key: test.t2 t2 a(a) = (1.000000) +Warning 168 print_records_per_key: test.t2 t2 b(b) = (1.000000) +Warning 168 print_records_per_key: test.t2 t2 a(a) = (1.000000) +Warning 168 print_records_per_key: test.t2 t2 b(b) = (1.000000) +analyze table t2; +Table Op Msg_type Msg_text +test.t2 analyze status OK +set debug='-d,print_records_per_key'; +select database_name,table_name,index_name,stat_name,stat_value from mysql.innodb_index_stats where table_name = 't2' and index_name in ('a', 'b') and stat_name = 'n_diff_pfx01'; +database_name table_name index_name stat_name stat_value +set debug='+d,print_records_per_key'; +select count(*) from t2; +count(*) +2 +Warnings: +Warning 168 print_records_per_key: test.t2 t2 a(a) = (2.000000) +Warning 168 print_records_per_key: test.t2 t2 b(b) = (1.000000) +Warning 168 print_records_per_key: test.t2 t2 a(a) = (2.000000) +Warning 168 print_records_per_key: test.t2 t2 b(b) = (1.000000) +truncate t2; +set debug='-d,print_records_per_key'; +select database_name,table_name,index_name,stat_name,stat_value from mysql.innodb_index_stats where table_name = 't2' and index_name in ('a', 'b') and stat_name = 'n_diff_pfx01'; +database_name table_name index_name stat_name stat_value +set debug='+d,print_records_per_key'; +select count(*) from t2; +count(*) +0 +Warnings: +Warning 168 print_records_per_key: test.t2 t2 a(a) = (1.000000) +Warning 168 print_records_per_key: test.t2 t2 b(b) = (1.000000) +Warning 168 print_records_per_key: test.t2 t2 a(a) = (1.000000) +Warning 168 print_records_per_key: test.t2 t2 b(b) = (1.000000) +drop table t2; +# with innodb_stats_notify_change being ON, records_per_key is up to update (for partition table). +create table t2 (a int, b int, key (a), key (b)) +partition by range (a) ( +partition p0 values less than (100), +partition p1 values less than MAXVALUE +); +set global innodb_stats_notify_change = on; +insert t2 values (1, 1), (1, 2); +set debug='-d,print_records_per_key'; +select database_name,table_name,index_name,stat_name,stat_value from mysql.innodb_index_stats where table_name = 't2' and index_name in ('a', 'b') and stat_name = 'n_diff_pfx01'; +database_name table_name index_name stat_name stat_value +set debug='+d,print_records_per_key'; +select count(*) from t2; +count(*) +2 +Warnings: +Warning 168 print_records_per_key: test.t2 t2 a(a) = (2.000000) +Warning 168 print_records_per_key: test.t2 t2 b(b) = (1.000000) +Warning 168 print_records_per_key: test.t2 t2 a(a) = (2.000000) +Warning 168 print_records_per_key: test.t2 t2 b(b) = (1.000000) +analyze table t2; +Table Op Msg_type Msg_text +test.t2 analyze status OK +set debug='-d,print_records_per_key'; +select database_name,table_name,index_name,stat_name,stat_value from mysql.innodb_index_stats where table_name = 't2' and index_name in ('a', 'b') and stat_name = 'n_diff_pfx01'; +database_name table_name index_name stat_name stat_value +set debug='+d,print_records_per_key'; +select count(*) from t2; +count(*) +2 +Warnings: +Warning 168 print_records_per_key: test.t2 t2 a(a) = (2.000000) +Warning 168 print_records_per_key: test.t2 t2 b(b) = (1.000000) +Warning 168 print_records_per_key: test.t2 t2 a(a) = (2.000000) +Warning 168 print_records_per_key: test.t2 t2 b(b) = (1.000000) +truncate t2; +set debug='-d,print_records_per_key'; +select database_name,table_name,index_name,stat_name,stat_value from mysql.innodb_index_stats where table_name = 't2' and index_name in ('a', 'b') and stat_name = 'n_diff_pfx01'; +database_name table_name index_name stat_name stat_value +set debug='+d,print_records_per_key'; +select count(*) from t2; +count(*) +0 +Warnings: +Warning 168 print_records_per_key: test.t2 t2 a(a) = (1.000000) +Warning 168 print_records_per_key: test.t2 t2 b(b) = (1.000000) +Warning 168 print_records_per_key: test.t2 t2 a(a) = (1.000000) +Warning 168 print_records_per_key: test.t2 t2 b(b) = (1.000000) +drop table t2; +# clean up +set debug='-d,print_records_per_key'; +set global innodb_stats_notify_change = @saved_value; diff --git a/mysql-test/suite/innodb/t/innodb_stats_notify_change.test b/mysql-test/suite/innodb/t/innodb_stats_notify_change.test new file mode 100644 index 000000000..c73e45ace --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb_stats_notify_change.test @@ -0,0 +1,92 @@ +--source include/have_debug.inc + +set @saved_value= @@global.innodb_stats_notify_change; + +--echo # with innodb_stats_notify_change being OFF, records_per_key could be stale. + +create table t1 (a int, b int, key (a), key (b)); + +set global innodb_stats_notify_change = off; +insert t1 values (1, 1), (1, 2); +# wait for persistent storage update +sleep 1; +set debug='-d,print_records_per_key'; select database_name,table_name,index_name,stat_name,stat_value from mysql.innodb_index_stats where table_name = 't1' and index_name in ('a', 'b') and stat_name = 'n_diff_pfx01'; +set debug='+d,print_records_per_key'; select count(*) from t1; +analyze table t1; +set debug='-d,print_records_per_key'; select database_name,table_name,index_name,stat_name,stat_value from mysql.innodb_index_stats where table_name = 't1' and index_name in ('a', 'b') and stat_name = 'n_diff_pfx01'; +set debug='+d,print_records_per_key'; select count(*) from t1; +truncate t1; +set debug='-d,print_records_per_key'; select database_name,table_name,index_name,stat_name,stat_value from mysql.innodb_index_stats where table_name = 't1' and index_name in ('a', 'b') and stat_name = 'n_diff_pfx01'; +set debug='+d,print_records_per_key'; select count(*) from t1; + +drop table t1; + +--echo # with innodb_stats_notify_change being ON, records_per_key is up to update. + +create table t1 (a int, b int, key (a), key (b)); + +set global innodb_stats_notify_change = on; +insert t1 values (1, 1), (1, 2); +# wait for persistent storage update +sleep 1; +set debug='-d,print_records_per_key'; select database_name,table_name,index_name,stat_name,stat_value from mysql.innodb_index_stats where table_name = 't1' and index_name in ('a', 'b') and stat_name = 'n_diff_pfx01'; +set debug='+d,print_records_per_key'; select count(*) from t1; +analyze table t1; +set debug='-d,print_records_per_key'; select database_name,table_name,index_name,stat_name,stat_value from mysql.innodb_index_stats where table_name = 't1' and index_name in ('a', 'b') and stat_name = 'n_diff_pfx01'; +set debug='+d,print_records_per_key'; select count(*) from t1; +truncate t1; +set debug='-d,print_records_per_key'; select database_name,table_name,index_name,stat_name,stat_value from mysql.innodb_index_stats where table_name = 't1' and index_name in ('a', 'b') and stat_name = 'n_diff_pfx01'; +set debug='+d,print_records_per_key'; select count(*) from t1; + +drop table t1; + +--echo # with innodb_stats_notify_change being OFF, records_per_key could be stale (for partition table). + +create table t2 (a int, b int, key (a), key (b)) +partition by range (a) ( + partition p0 values less than (100), + partition p1 values less than MAXVALUE +); + +set global innodb_stats_notify_change = off; +insert t2 values (1, 1), (1, 2); +# wait for persistent storage update +sleep 1; +set debug='-d,print_records_per_key'; select database_name,table_name,index_name,stat_name,stat_value from mysql.innodb_index_stats where table_name = 't2' and index_name in ('a', 'b') and stat_name = 'n_diff_pfx01'; +set debug='+d,print_records_per_key'; select count(*) from t2; +analyze table t2; +set debug='-d,print_records_per_key'; select database_name,table_name,index_name,stat_name,stat_value from mysql.innodb_index_stats where table_name = 't2' and index_name in ('a', 'b') and stat_name = 'n_diff_pfx01'; +set debug='+d,print_records_per_key'; select count(*) from t2; +truncate t2; +set debug='-d,print_records_per_key'; select database_name,table_name,index_name,stat_name,stat_value from mysql.innodb_index_stats where table_name = 't2' and index_name in ('a', 'b') and stat_name = 'n_diff_pfx01'; +set debug='+d,print_records_per_key'; select count(*) from t2; + +drop table t2; + +--echo # with innodb_stats_notify_change being ON, records_per_key is up to update (for partition table). + +create table t2 (a int, b int, key (a), key (b)) +partition by range (a) ( + partition p0 values less than (100), + partition p1 values less than MAXVALUE +); + +set global innodb_stats_notify_change = on; +insert t2 values (1, 1), (1, 2); +# wait for persistent storage update +sleep 1; +set debug='-d,print_records_per_key'; select database_name,table_name,index_name,stat_name,stat_value from mysql.innodb_index_stats where table_name = 't2' and index_name in ('a', 'b') and stat_name = 'n_diff_pfx01'; +set debug='+d,print_records_per_key'; select count(*) from t2; +analyze table t2; +set debug='-d,print_records_per_key'; select database_name,table_name,index_name,stat_name,stat_value from mysql.innodb_index_stats where table_name = 't2' and index_name in ('a', 'b') and stat_name = 'n_diff_pfx01'; +set debug='+d,print_records_per_key'; select count(*) from t2; +truncate t2; +set debug='-d,print_records_per_key'; select database_name,table_name,index_name,stat_name,stat_value from mysql.innodb_index_stats where table_name = 't2' and index_name in ('a', 'b') and stat_name = 'n_diff_pfx01'; +set debug='+d,print_records_per_key'; select count(*) from t2; + +drop table t2; + +--echo # clean up + +set debug='-d,print_records_per_key'; +set global innodb_stats_notify_change = @saved_value; diff --git a/sql/table.cc b/sql/table.cc index e3ef50f0a..103622e68 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -8560,7 +8560,46 @@ int TABLE_LIST::fetch_number_of_rows() table->file->stats.records= derived->query_result()->estimated_rowcount; } else - error= table->file->info(HA_STATUS_VARIABLE | HA_STATUS_NO_LOCK); + { + uint flag = HA_STATUS_VARIABLE | HA_STATUS_NO_LOCK; + error = table->file->info(flag); + + DBUG_EXECUTE_IF("print_records_per_key", { + for (uint nr=0; nr < table->s->keys; nr++) { + String out; + out.append(table->s->db); + out.append("."); + out.append(table->s->table_name); + out.append(" "); + out.append(table->alias); + out.append(" "); + KEY *keyinfo = table->key_info + nr; + if (keyinfo->supports_records_per_key()) { + out.append(keyinfo->name); + out.append("("); + for (uint part = 0; part < keyinfo->actual_key_parts; part++) { + if (keyinfo->has_records_per_key(part)) { + if (part > 0) out.append(","); + out.append(keyinfo->key_part[part].field->field_name); + } + } + out.append(") = ("); + for (uint part = 0; part < keyinfo->actual_key_parts; part++) { + if (keyinfo->has_records_per_key(part)) { + rec_per_key_t rec_per_key = keyinfo->records_per_key(0); + if (part > 0) out.append(","); + std::string num_str = std::to_string(rec_per_key); + out.append(num_str.c_str()); + } + } + out.append(")"); + } + push_warning_printf(table->in_use, Sql_condition::SL_WARNING, + HA_ERR_GENERIC, "print_records_per_key: %s", + out.c_ptr_safe()); + } + }); + } return error; } diff --git a/storage/innobase/dict/dict0stats.cc b/storage/innobase/dict/dict0stats.cc index 68fcc9b27..d5b0aafde 100644 --- a/storage/innobase/dict/dict0stats.cc +++ b/storage/innobase/dict/dict0stats.cc @@ -566,6 +566,9 @@ dict_stats_empty_table( dict_stats_empty_index(index); } + /* Just ensure initialized for exceptional cases. */ + table->stats_updated = FALSE; + table->stat_initialized = TRUE; dict_table_stats_unlock(table, RW_X_LATCH); @@ -664,6 +667,8 @@ dict_stats_copy( dict_table_t* dst, /*!< in/out: destination table */ const dict_table_t* src) /*!< in: source table */ { + int tmp_updated = src->stats_updated; + dst->stats_updated = tmp_updated; dst->stats_last_recalc = src->stats_last_recalc; dst->stat_n_rows = src->stat_n_rows; dst->stat_clustered_index_size = src->stat_clustered_index_size; @@ -935,6 +940,8 @@ dict_stats_update_transient( table->stat_initialized = TRUE; + table->stats_updated = TRUE; + dict_table_stats_unlock(table, RW_X_LATCH); dict_table_analyze_index_unlock(table); @@ -2287,6 +2294,8 @@ dict_stats_update_persistent( table->stat_modified_counter = 0; + table->stats_updated = TRUE; + table->stat_initialized = TRUE; dict_stats_assert_initialized(table); @@ -3180,6 +3189,13 @@ dict_stats_update( dict_stats_empty_table(table); + /* DICT_STATS_EMPTY_TABLE is invoked only when truncating intrinsic + tables or creating new tables. Non-intrinsic tables are truncated by + recreation and the table definition cache is invalidated with + TDC_RT_REMOVE_ALL. In all these cases the server never gets stale stats, + so here just sets to a meaningful state. */ + table->stats_updated = TRUE; + /* If table is using persistent stats, then save the stats on disk */ @@ -3232,6 +3248,7 @@ dict_stats_update( dberr_t err = dict_stats_fetch_from_ps(t); + t->stats_updated = TRUE; t->stats_last_recalc = table->stats_last_recalc; t->stat_modified_counter = 0; diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc index 6a8099741..4d6f23b49 100644 --- a/storage/innobase/handler/ha_innodb.cc +++ b/storage/innobase/handler/ha_innodb.cc @@ -14162,6 +14162,8 @@ ha_innobase::info_low( ib_uint64_t n_rows; char path[FN_REFLEN]; os_file_stat_t stat_info; + int tst_val = TRUE; + int new_val = FALSE; DBUG_ENTER("info"); @@ -14243,6 +14245,13 @@ ha_innobase::info_low( stat_sum_of_other_index_sizes = ib_table->stat_sum_of_other_index_sizes; + /* Piggyback fetching constant statistics when it has been updated. + Note that the ANALYZE command has explicitly requested the fetch. */ + if (srv_stats_notify_change && !is_analyze && + ib_table->stats_updated.compare_exchange_weak(tst_val, new_val)) { + flag |= HA_STATUS_CONST; + } + if (!(flag & HA_STATUS_NO_LOCK)) { dict_table_stats_unlock(ib_table, RW_S_LATCH); } @@ -20172,6 +20181,12 @@ static MYSQL_SYSVAR_BOOL(stats_on_metadata, innobase_stats_on_metadata, " SHOW TABLE STATUS for tables that use transient statistics (off by default)", NULL, NULL, FALSE); +static MYSQL_SYSVAR_BOOL( + stats_notify_change, srv_stats_notify_change, PLUGIN_VAR_OPCMDARG, + "When InnoDB statistics are updated, tell the server about the change " + "to fetch the new statistics.", + NULL, NULL, FALSE); + static MYSQL_SYSVAR_ULONGLONG(stats_sample_pages, srv_stats_transient_sample_pages, PLUGIN_VAR_RQCMDARG, "Deprecated, use innodb_stats_transient_sample_pages instead", @@ -21100,6 +21115,7 @@ static struct st_mysql_sys_var* innobase_system_variables[]= { MYSQL_SYSVAR(stats_persistent), MYSQL_SYSVAR(stats_persistent_sample_pages), MYSQL_SYSVAR(stats_auto_recalc), + MYSQL_SYSVAR(stats_notify_change), MYSQL_SYSVAR(adaptive_hash_index), MYSQL_SYSVAR(adaptive_hash_index_parts), MYSQL_SYSVAR(fast_ahi_cleanup_for_drop_table), diff --git a/storage/innobase/handler/ha_innopart.cc b/storage/innobase/handler/ha_innopart.cc index 7612ccd7f..12887274e 100644 --- a/storage/innobase/handler/ha_innopart.cc +++ b/storage/innobase/handler/ha_innopart.cc @@ -3418,6 +3418,8 @@ ha_innopart::info_low( ib_uint64_t max_rows = 0; uint biggest_partition = 0; int error = 0; + int tst_val = TRUE; + int new_val = FALSE; DBUG_ENTER("ha_innopart::info_low"); @@ -3529,6 +3531,13 @@ ha_innopart::info_low( stat_sum_of_other_index_sizes += ib_table->stat_sum_of_other_index_sizes; + /* Piggyback fetching constant statistics when it has been updated. + Note that the ANALYZE command has explicitly requested the fetch. */ + if (srv_stats_notify_change && !is_analyze && + ib_table->stats_updated.compare_exchange_weak(tst_val, new_val)) { + flag |= HA_STATUS_CONST; + } + if ((flag & HA_STATUS_NO_LOCK) == 0) { dict_table_stats_unlock(ib_table, RW_S_LATCH); } diff --git a/storage/innobase/include/dict0mem.h b/storage/innobase/include/dict0mem.h index a3e05ed1c..797750039 100644 --- a/storage/innobase/include/dict0mem.h +++ b/storage/innobase/include/dict0mem.h @@ -53,6 +53,7 @@ Created 1/8/1996 Heikki Tuuri #include #include #include +#include /* Forward declaration. */ struct ib_rbt_t; @@ -1663,6 +1664,9 @@ struct dict_table_t { database startup or table creation. */ unsigned stat_initialized:1; + /** TRUE if statistics have been updated in the background. */ + std::atomic stats_updated; + /** Timestamp of last recalc of the stats. */ ib_time_t stats_last_recalc; diff --git a/storage/innobase/include/srv0srv.h b/storage/innobase/include/srv0srv.h index fc15baf27..4ce5d776b 100644 --- a/storage/innobase/include/srv0srv.h +++ b/storage/innobase/include/srv0srv.h @@ -420,6 +420,7 @@ extern ulong srv_max_purge_lag; extern ulong srv_max_purge_lag_delay; extern my_bool srv_stats_skip_adjustment_for_primary_key; extern ulong srv_replication_delay; +extern my_bool srv_stats_notify_change; /*-------------------------------------------*/ extern my_bool srv_print_innodb_monitor; diff --git a/storage/innobase/srv/srv0srv.cc b/storage/innobase/srv/srv0srv.cc index e5ddbf9cf..31e92a700 100644 --- a/storage/innobase/srv/srv0srv.cc +++ b/storage/innobase/srv/srv0srv.cc @@ -401,6 +401,7 @@ my_bool srv_stats_include_delete_marked = FALSE; unsigned long long srv_stats_persistent_sample_pages = 20; my_bool srv_stats_auto_recalc = TRUE; my_bool srv_stats_skip_adjustment_for_primary_key = FALSE; +my_bool srv_stats_notify_change = FALSE; ibool srv_use_doublewrite_buf = TRUE; -- 2.32.0