InnoDB Statistics¶
This feature provides new startup options (control method and collection of index statistics estimation) and information schema views to confirm the 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¶
Contact Us
For free technical help, visit the Percona Community Forum.To report bugs or submit feature requests, open a JIRA ticket.
For paid support and managed or professional services, contact Percona Sales.