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

 About This Book

 Introduction of SQL

 MySQL Introduction and Installation

 Introduction of MySQL Programs

 PHP Programs and MySQL Server

 Perl Programs and MySQL Servers

 Java Programs and MySQL Servers

 Datatypes and Data Literals

 Operations and Expressions

 Character Strings and Bit Strings

 Commonly Used Functions

 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

 Locks Used in MySQL

 Defining and Calling Stored Procedures

 Variables, Loops and Cursors Used in Stored Procedures

 System, User-Defined and Stored Procedure Variables

 MySQL Server Administration

Storage Engines in MySQL Server

 What Are Storage Engines

 What Is InnoDB Storage Engine

 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

 Bulk Changes on Large Tables

 MySQL Server on macOS

 Installing MySQL Server on Linux

 Connection, Performance and Second Instance on Linux

 Archived Tutorials

 References

 Full Version in PDF/EPUB