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)
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

XtraDB changed page tracking

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.2.2.
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.
]]>