InnoDB Statistics¶

System Variables¶

Four new system variables were introduced by this feature.

variable innodb_stats_method¶

Command Line:	YES
Config File:	YES
Scope:	GLOBAL
Dynamic:	YES
Type:	STRING
Default Value:	nulls_equal
Allowed Values:	nulls_equal, nulls_unequal, nulls_ignored

The values and meanings are almost same to myisam_stats_method option of native MySQL (nulls_equal, nulls_unequal, nulls_ignored). But InnoDB doesn’t have several patterns of statistics currently. Even though this option can be changed dynamically, statistics needs to be re-calculated to change the method for the table.

(reference: MyISAM Index Statistics Collection)

Note: Beginning in release 5.1.56-12.7, a variable with the same and functionality was implemented in the upstream InnoDB.

variable innodb_stats_auto_update¶

Type:	BOOLEAN
Default Value:	1

InnoDB updates the each index statistics automatically (many updates were done, some information_schema is accessed, table monitor, etc.). Setting this option 0 can stop these automatic recalculation of the statistics except for “first open” and “ANALYZE TABLE command”.

variable innodb_stats_update_need_lock¶

Type:	BOOLEAN
Default Value:	1

If you meet contention of &dict_operation_lock, setting 0 reduces the contention. But 0 disables to update Data_free: of SHOW TABLE STATUS.

variable innodb_use_sys_stats_table¶

Version:	5.1.49-11.3 Variable introduced
Type:	BOOLEAN
Default Value:	0

If this option is enabled, XtraDB uses the SYS_STATS system table to store statistics of table indexes. Also, when InnoDB opens a table for the first time, it loads the statistics from SYS_STATS instead of sampling index pages. If you use a high stats_sample_pages value, the first open of a table is expensive. In such a case, this option will help. Intended behavior is to never update statistics unless an explicit ANALYZE TABLE is issued.

INFORMATION_SCHEMA Tables¶

table INFORMATION_SCHEMA.INNODB_SYS_STATS¶

Columns:	INDEX_ID – Index ID KEY_COLS – Number of Key Columns DIFF_VALS – Number of Different Values. NON_NULL_VALS – Number of Non NULL Values.

table INFORMATION_SCHEMA.INNODB_SYS_TABLES¶

Shows the information about InnoDB tables

Columns:	SCHEMA – Schema (database) name NAME – Table name ID – Table ID N_COLS – Number of Columns TYPE – MIX_ID – This value is obsolete, value is always 0 MIX_LEN – Contains 0 for regular tables and 1 for temporary tables CLUSTER_NAME – This value isn’t supported anymore, value is always NULL SPACE – Tablespace ID

table INFORMATION_SCHEMA.INNODB_SYS_INDEXES¶

Shows the information about InnoDB indexes

Columns:	TABLE_ID – Table ID ID – Index ID NAME – Index Name N_FIELDS – Number of fields TYPE – SPACE – Tablespace ID PAGE_NO – The page offset within its tablespace

table INFORMATION_SCHEMA.INNODB_TABLE_STATS¶

Shows table statistics information of dictionary cached.

Columns:	table_schema – Database name of the table. table_name – Table name. rows – estimated number of all rows. clust_size – cluster index (table/primary key) size in number of pages. other_size – Other index (non primary key) size in number of pages. modified – Internal counter to judge whether statistics recalculation should be done.

If the value of modified column exceeds “rows / 16” or 2000000000, the statistics recalculation is done when innodb_stats_auto_update == 1. We can estimate the oldness of the statistics by this value.

table INFORMATION_SCHEMA.INNODB_INDEX_STATS¶

Shows index statistics information of dictionary cached.

Columns:	table_schema – Database name of the table. table_name – Table name. index_name – Index name. fields – How many fields the index key has. (it is internal structure of InnoDB, it may be larger than the CREATE TABLE). rows_per_keys – Estimate rows per 1 key value. ([1 column value], [2 columns value], [3 columns value], ...). index_size – Number of index pages. index_pages – Number of leaf pages.

Example¶

[innodb_stats_method = nulls_equal (default behavior of InnoDB)]

mysql> explain SELECT COUNT(*), 0 FROM orgs2 orgs LEFT JOIN sa_opportunities2 sa_opportunities ON orgs.org_id=sa_opportunities.org_id LEFT JOIN contacts2 contacts ON orgs.org_id=contacts.org_id;
+----+-------------+------------------+-------+-----------------+-----------------+---------+-------------------+-------+-------------+
| id | select_type | table            | type  | possible_keys   | key             | key_len | ref               | rows  | Extra       |
+----+-------------+------------------+-------+-----------------+-----------------+---------+-------------------+-------+-------------+
|  1 | SIMPLE      | orgs             | index | NULL            | orgs$org_id     | 4       | NULL              |   128 | Using index |
|  1 | SIMPLE      | sa_opportunities | ref   | sa_opp$org_id   | sa_opp$org_id   | 5       | test2.orgs.org_id |  5751 | Using index |
|  1 | SIMPLE      | contacts         | ref   | contacts$org_id | contacts$org_id | 5       | test2.orgs.org_id | 23756 | Using index |
+----+-------------+------------------+-------+-----------------+-----------------+---------+-------------------+-------+-------------+
3 rows in set (0.00 sec)

[innodb_stats_method = nulls_unequal or nulls_ignored]

mysql> explain SELECT COUNT(*), 0 FROM orgs2 orgs LEFT JOIN sa_opportunities2 sa_opportunities ON orgs.org_id=sa_opportunities.org_id LEFT JOIN contacts2 contacts ON orgs.org_id=contacts.org_id;
+----+-------------+------------------+-------+-----------------+-----------------+---------+-------------------+------+-------------+
| id | select_type | table            | type  | possible_keys   | key             | key_len | ref               | rows | Extra       |
+----+-------------+------------------+-------+-----------------+-----------------+---------+-------------------+------+-------------+
|  1 | SIMPLE      | orgs             | index | NULL            | orgs$org_id     | 4       | NULL              |  128 | Using index |
|  1 | SIMPLE      | sa_opportunities | ref   | sa_opp$org_id   | sa_opp$org_id   | 5       | test2.orgs.org_id |    1 | Using index |
|  1 | SIMPLE      | contacts         | ref   | contacts$org_id | contacts$org_id | 5       | test2.orgs.org_id |    1 | Using index |
+----+-------------+------------------+-------+-----------------+-----------------+---------+-------------------+------+-------------+
3 rows in set (0.00 sec)
<example of information_schema>

mysql> select * from information_schema.innodb_table_stats;
+------------------------+-------+------------+------------+----------+
| table_name             | rows  | clust_size | other_size | modified |
+------------------------+-------+------------+------------+----------+
| test/sa_opportunities2 | 11175 |         21 |         11 |        0 |
| test/orgs2             |   128 |          1 |          0 |        0 |
| test/contacts2         | 47021 |         97 |         97 |        0 |
+------------------------+-------+------------+------------+----------+
3 rows in set (0.00 sec)

mysql> select * from information_schema.innodb_index_stats;
+------------------------+-----------------+--------+--------------+------------+------------+
| table_name             | index_name      | fields | row_per_keys | index_size | leaf_pages |
+------------------------+-----------------+--------+--------------+------------+------------+
| test/sa_opportunities2 | GEN_CLUST_INDEX |      1 | 1            |         21 |         20 |
| test/sa_opportunities2 | sa_opp$org_id   |      2 | 338, 1       |          11|         10 |
| test/orgs2             | orgs$org_id     |      1 | 1            |          1 |          1 |
| test/contacts2         | GEN_CLUST_INDEX |      1 | 1            |       97   |         80 |
| test/contacts2         | contacts$org_id |      2 | 516, 0       |       97   |         37 |
+------------------------+-----------------+--------+--------------+------------+------------+
5 rows in set (0.00 sec)

Other reading¶

• InnoDB Table/Index stats