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

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)
Percona Server
Call Us
+1-888-316-9775 (USA - Sales)
+1-208-473-2904 (USA - Sales)
+44-208-133-0309 (UK - Sales)
0-800-051-8984 (UK - Sales)
0-800-181-0665 (GER - Sales)
+1-877-862-4316 (Emergency)
+1-855-55TRAIN (Training)
+1-925-271-5054 (Training)

Table Of Contents

Previous topic

Audit Log Plugin

Next topic

User Statistics

This Page



© Copyright Percona LLC and/or its affiliates 2009-2014.
Except where otherwise noted, this documentation is licensed under the following license:
CC Attribution-ShareAlike 2.0 Generic
Created using Sphinx 1.1.3.
This documentation is developed in Launchpad as part of the Percona Server source code.
If you spotted innacuracies, errors, don't understood it or you think something is missing or should be improved, please file a bug.
]]>