A few days ago I wrote about how grossly outdated statistics returned through MySQL’s Information_Schema can be. In that post, Øystein Grøvlen suggested taking a look at mysql.innodb_table_stats and mysql.innodb_index_stats as a better source of information. Let’s do just that!
Let’s start with the good news. Unlike MySQL Data Dictionary Tables (mysql.table_stats, etc), mysql.innodb_table_stats and mysql.innodb_index_stats can be queried by the user. They also contain a lot of interesting statistics. Before we get to that though, let’s examine where those tables come from in more detail.
As the manual tells us, these tables are storing InnoDB Persistent Statistics, but only if those statistics are enabled. While in MySQL 8 the default is to use Persistent Statistics and update them automatically, the user has an option to disable persistent statistics by setting innodb_stats_persistent=0 variable or disable automatic updates by setting innodb_stats_auto_recalc=0, which means the data in these tables may be missing or maybe very stale.
Furthermore, in their quest for ultimate flexibility, the MySQL Team allows you to enable/disable these options on a table by table basis using STATS_PERSISTENT, STATS_AUTO_RECALC, and STATS_SAMPLE_PAGES CREATE TABLE clauses. This means information may be present for some tables but not others.
So while this data is very valuable when it presents, you should not count on its being present or on its accuracy.
OK, enough of that. Let’s take a look at what we have in those tables:
1 2 3 4 5 6 7 8 9 | mysql> select * from mysql.innodb_table_stats where database_name='tpcc' and table_name='orders1' G *************************** 1. row *************************** database_name: tpcc table_name: orders1 last_update: 2020-01-04 23:59:27 n_rows: 380197 clustered_index_size: 1185 sum_of_other_index_sizes: 672 1 row in set (0.00 sec) |
The table information contains an estimated number of rows as well as clustered index size and size of all other indexes combined in pages. Multiply it by innodb_page_size variable and you will get the size in bytes.
Also, note the last_update column which specifies when stats were last updated. Very old last_update may mean two things – either the table is not getting many writes for stats update to be triggered, or automatic stats update for this table is disabled.
Now let’s take a look at innodb_index_stats table:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | mysql> select * from mysql.innodb_index_stats where database_name='tpcc' and table_name='orders1'; +---------------+------------+-------------+---------------------+--------------+------------+-------------+-----------------------------------+ | database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description | +---------------+------------+-------------+---------------------+--------------+------------+-------------+-----------------------------------+ | tpcc | orders1 | PRIMARY | 2020-01-04 23:59:27 | n_diff_pfx01 | 9 | 11 | o_w_id | | tpcc | orders1 | PRIMARY | 2020-01-04 23:59:27 | n_diff_pfx02 | 101 | 20 | o_w_id,o_d_id | | tpcc | orders1 | PRIMARY | 2020-01-04 23:59:27 | n_diff_pfx03 | 380197 | 20 | o_w_id,o_d_id,o_id | | tpcc | orders1 | PRIMARY | 2020-01-04 23:59:27 | n_leaf_pages | 991 | NULL | Number of leaf pages in the index | | tpcc | orders1 | PRIMARY | 2020-01-04 23:59:27 | size | 1185 | NULL | Number of pages in the index | | tpcc | orders1 | idx_orders1 | 2020-01-04 23:59:27 | n_diff_pfx01 | 9 | 11 | o_w_id | | tpcc | orders1 | idx_orders1 | 2020-01-04 23:59:27 | n_diff_pfx02 | 101 | 20 | o_w_id,o_d_id | | tpcc | orders1 | idx_orders1 | 2020-01-04 23:59:27 | n_diff_pfx03 | 306329 | 20 | o_w_id,o_d_id,o_c_id | | tpcc | orders1 | idx_orders1 | 2020-01-04 23:59:27 | n_diff_pfx04 | 378338 | 20 | o_w_id,o_d_id,o_c_id,o_id | | tpcc | orders1 | idx_orders1 | 2020-01-04 23:59:27 | n_leaf_pages | 577 | NULL | Number of leaf pages in the index | | tpcc | orders1 | idx_orders1 | 2020-01-04 23:59:27 | size | 672 | NULL | Number of pages in the index | +---------------+------------+-------------+---------------------+--------------+------------+-------------+-----------------------------------+ 11 rows in set (0.00 sec) |
This table is designed to be extensible and able to hold many different statistics for the same index.
We see several statistics here. First, there are n_diff_pfxXX statistics which show cardinality for different index prefixes – the specific columns which are part of that prefix are specified in stat_description value. These are the same cardinality values you would see if you execute the SHOW INDEX FROM tpcc.orders1 command.
The other stats are n_leaf_pages and size which for each index specifies the full size (in pages) as well as the number of leaf pages. The difference between these two is the number of non-leaf pages the table has.
In this example, the orders1 table has 95 non-leaf pages and 577 leaf pages, or 672 pages in total.
This information can help you to identify how much space is needed in the InnoDB buffer pool if you want a particular index fully cached, or at least have non-leaf pages cached (which ensures index lookup will not take more than one IO operation).
Summary
mysql.innodb_table_stats and mysql.innodb_index_stats can be great sources of detailed information about your InnoDB tables and Indexes, and they are enabled and available by default in modern MySQL versions. Keep in mind, though, under certain configurations the information in these tables is unavailable or severely outdated. Also because the statistics are computed through sampling, they are not exact – and can be grossly inaccurate in some conditions.
