The persistent statistics feature relies on the internally managed tables in the mysql database, named innodb_table_stats and innodb_index_stats. These tables are set up automatically in all install, upgrade, and build-from-source procedures.
Table 15.6 Columns of innodb_table_stats
| Column name | Description |
|---|---|
database_name | Database name |
table_name | Table name, partition name, or subpartition name |
last_update | A timestamp indicating the last time that InnoDB
updated this row |
n_rows | The number of rows in the table |
clustered_index_size | The size of the primary index, in pages |
sum_of_other_index_sizes | The total size of other (non-primary) indexes, in pages |
Table 15.7 Columns of innodb_index_stats
| Column name | Description |
|---|---|
database_name | Database name |
table_name | Table name, partition name, or subpartition name |
index_name | Index name |
last_update | A timestamp indicating the last time the row was updated |
stat_name | The name of the statistic, whose value is reported in the
stat_value column |
stat_value | The value of the statistic that is named in stat_name
column |
sample_size | The number of pages sampled for the estimate provided in the
stat_value column |
stat_description | Description of the statistic that is named in the
stat_name column |
The innodb_table_stats and innodb_index_stats tables include a last_update column that shows when index statistics were last updated:
mysql> SELECT * FROM innodb_table_stats \G
*************************** 1. row ***************************
database_name: sakila
table_name: actor
last_update: 2014-05-28 16:16:44
n_rows: 200
clustered_index_size: 1
sum_of_other_index_sizes: 1
...
mysql> SELECT * FROM innodb_index_stats \G
*************************** 1. row ***************************
database_name: sakila
table_name: actor
index_name: PRIMARY
last_update: 2014-05-28 16:16:44
stat_name: n_diff_pfx01
stat_value: 200
sample_size: 1
...
The innodb_table_stats and innodb_index_stats tables can be updated manually, which makes it possible to force a specific query optimization plan or test alternative plans without modifying the database. If you manually update statistics, use the FLUSH TABLE statement to load the updated statistics.tbl_name
Persistent statistics are considered local information, because they relate to the server instance. The innodb_table_stats and innodb_index_stats tables are therefore not replicated when automatic statistics recalculation takes place. If you run ANALYZE TABLE to initiate a synchronous recalculation of statistics, the statement is replicated (unless you suppressed logging for it), and recalculation takes place on replicas.