MySQL Tutorials - Herong's Tutorial Examples - v4.46, by Herong Yang
Statistic Information on InnoDB Tables
This section provides a tutorial example on how to collect and review statistic information on InnoDB tables in MySQL server.
If you want to optimize InnoDB tables, the first thing you want to do is to gather statistic information from the MySQL server as shown below:
1. Get information from system variables:
mysql> show variables like 'innodb%'; Variable_name Value -------------------- ----- innodb_page_size 16384
2. Get information from "information_schema.tables" table and "SELECT COUNT(*) FROM table_name" statement:
mysql> select * from information_schema.tables where table_name='body'; TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH INDEX_LENGTH DATA_FREE ---------- -------------- ----------- ------------ --------- 2000000 10616 21233518436 25873408 0 mysql> select count(*) from body; +----------+ | count(*) | +----------+ | 2000000 | +----------+ 1 row in set (3 min 32.24 sec)
The output columns are defined below by the MySQL documentation for InnoDB tables:
3. Get information from mysql.innodb_table_stats and mysql.innodb_index_stats:
mysql> select * from mysql.innodb_table_stats where table_name='body'; n_rows clustered_index_size sum_of_other_index_sizes ------- -------------------- ------------------------ 1639501 1960255 0 mysql> select * from mysql.innodb_index_stats where table_name='body'; index_name stat_name stat_value sample_size stat_description ---------- ------------ ---------- ----------- -------------------- PRIMARY n_diff_pfx01 1639501 20 ID PRIMARY n_leaf_pages 1959869 NULL Number of leaf pages PRIMARY size 1960255 NULL Number of pages
The output columns are defined below by the MySQL documentation for InnoDB tables:
By the way, stats provided above are rough estimates and used by the query optimizer to help a better execution plan.
4. Get information from the InnoDB table storage file:
mysql> select * from INNODB_TABLESPACES where name rlike 'body'; ROW_FORMAT PAGE_SIZE SPACE_TYPE FS_BLOCK_SIZE FILE_SIZE ALLOCATED_SIZE ---------- --------- ---------- ------------- ----------- -------------- Dynamic 16384 Single 4096 32119980032 32119984128 herong$ sudo ls -l /var/lib/mysql/herong/body* -rw-r-----. 1 mysql mysql 32119980032 Nov 13 15:08 body.ibd
5. Review information gathered so far for my "body" InnoDB table:
Number of Rows - Actual: 2,000,000 rows Number of Rows - Estimated: 1,639,501 rows Average Row Length: 10,616 bytes Data Size - Calculated: 21,232,000,000 = 0616*2000000 bytes InnoDB Page Size: 16,384 bytes Rows per Page - Calculated: 1.54333082140166 = 16384/10616 rows Rows per Page - Estimated: 1 row Number of Data Pages: 1,959,869 pages Number of Total Pages: 1,960,255 pages Number of Index Pages: 386 pages Storage Size - Calculated: 32,116,817,920 = 1960255*16384 bytes Storage Size - Actual: 32,119,980,032 bytes Cardinality of Index - Estimated: 1,639,501 Pages Sampled for Estimation: 20 pages
6. System tables related to InnoDB engine:
mysql> use sys; mysql> show tables like '%INNODB%'; Empty set (0.00 sec) mysql> use mysql; mysql> show tables like '%INNODB%'; Empty set (0.00 sec) mysql> use information_schema; mysql> show tables like '%INNODB%'; INNODB_BUFFER_PAGE INNODB_BUFFER_PAGE_LRU INNODB_BUFFER_POOL_STATS INNODB_CACHED_INDEXES INNODB_CMP INNODB_CMPMEM INNODB_CMPMEM_RESET INNODB_CMP_PER_INDEX INNODB_CMP_PER_INDEX_RESET INNODB_CMP_RESET INNODB_COLUMNS INNODB_DATAFILES INNODB_FIELDS INNODB_FOREIGN INNODB_FOREIGN_COLS INNODB_FT_BEING_DELETED INNODB_FT_CONFIG INNODB_FT_DEFAULT_STOPWORD INNODB_FT_DELETED INNODB_FT_INDEX_CACHE INNODB_FT_INDEX_TABLE INNODB_INDEXES INNODB_METRICS INNODB_SESSION_TEMP_TABLESPACES INNODB_TABLES INNODB_TABLESPACES INNODB_TABLESPACES_BRIEF INNODB_TABLESTATS INNODB_TEMP_TABLE_INFO INNODB_TRX INNODB_VIRTUAL mysql> use performance_schema; mysql> show tables like '%INNODB%'; Empty set (0.00 sec)
Table of Contents
MySQL Introduction and Installation
Introduction of MySQL Programs
Perl Programs and MySQL Servers
Java Programs and MySQL Servers
Character Strings and Bit Strings
Table Column Types for Different Types of Values
Using DDL to Create Tables and Indexes
Using DML to Insert, Update and Delete Records
Using SELECT to Query Database
Window Functions for Statistical Analysis
Use Index for Better Performance
Transaction Management and Isolation Levels
Defining and Calling Stored Procedures
Variables, Loops and Cursors Used in Stored Procedures
System, User-Defined and Stored Procedure Variables
►Storage Engines in MySQL Server
Convert Table to InnoDB Storage Engine
Clustered Index Used by InnoDB Engine
►Statistic Information on InnoDB Tables
MySQL Status Variables for InnoDB Engine
MySQL System Variables for InnoDB Engine
InnoDB Storage Engine - Primary and Secondary Indexes
Performance Tuning and Optimization
Installing MySQL Server on Linux