InnoDB Statistics

This feature provides new startup options (control method and collection of index statistics estimation) and information schema views to confirm the statistics.

Version Specific Information

• 5.5.8-20.0: Renamed three fields in INNODB_INDEX_STATS table.

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)

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

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

Shows statistics of table indexes.

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:

• TABLE_ID – Table ID
• SCHEMA – Database (schema) name
• NAME – Table name
• FLAG – Contains 0 if it is a InnoDB system table or 1 it is a user table
• N_COLS – Number of columns in the table
• SPACE – Tablespace ID

table INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS

Shows the information about the performance statistics of InnoDB tables.

Columns:

• TABLE_ID – Table ID
• SCHEMA – Database (schema) Name
• NAME – Table Name
• STATS_INITIALIZED – Contains Initialized value if the statistics are collected or Uninitialized if they are not collected.
• NUM_ROWS – Estimated number of rows in the table.
• CLUST_INDEX_SIZE – Number of pages on disk that store the clustered index.
• OTHER_INDEX_SIZE – Number of pages on disk that store all secondary indexes.
• MODIFIED_COUNTER – Number of rows modified by DML operations.
• AUTOINC –
• MYSQL_HANDLES_OPENED –

table INFORMATION_SCHEMA.INNODB_SYS_INDEXES

Shows the information about InnoDB indexes

Columns:

• INDEX_ID – Index ID
• NAME – Index Name
• TABLE_ID – Table ID
• TYPE – Numeric identifier signifying the index type
• N_FIELDS – Number of columns in the index
• PAGE_NO – Page offset within its tablespace
• SPACE – Tablespace ID

table INFORMATION_SCHEMA.INNODB_SYS_COLUMNS

Shows the information about the InnoDB table columns

Columns:

• TABLE_ID – Table ID
• NAME – Column Name
• POS – Position of the column inside the table.
• MTYPE – Numeric identifier for the column type.
• PRTYPE – Binary value with bits representing data type, character set code and nullability.
• LEN – Column length.

table INFORMATION_SCHEMA.INNODB_SYS_FIELDS

Shows the information about the InnoDB index key fields.

Columns:

• INDEX_ID – Index ID
• NAME – Index Name
• POS – Position of the field inside the index.

table INFORMATION_SCHEMA.INNODB_SYS_FOREIGN

Shows the information about the InnoDB foreign keys.

Columns:

• ID – Foreign Key ID
• FOR_NAME – Database/Table which contains the Foreign Key
• FOR_REF – Database/Table being referenced by the Foreign Key
• N_COLS – Number of columns in the foreign key.
• TYPE – Type of foreign key, represented by the bit flags.

table INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS

Shows the information about the columns of the InnoDB foreign keys.

Columns:

• ID – Foreign Key ID
• FOR_COL_NAME – Foreign Key Column Name
• FOR_REF – Referenced Column Name
• POS – Position of the field inside the index.

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_key – Estimate rows per 1 key value. ([1 column value], [2 columns value], [3 columns value], ...).
• index_total_pages – Number of index pages.
• index_leaf_pages – Number of leaf pages.

In releases before 5.5.8-20.0, these fields had different names:

• rows_per_key was row_per_keys
• index_total_pages was index_size
• index_leaf_pages was 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