Determining MySQL Table Disk Usage with INFORMATION_SCHEMA and INNODB_SYS_TABLESPACES

Author: Peter Zaitsev

I want to know how much disk space a MySQL table occupies, but it seems trivial. Shouldn't INFORMATION_SCHEMA.TABLES provide this information? Not that simple!

This seemingly simple question is actually very complex in MySQL because the server supports many storage engines—some of which do not store data on disk at all—and different data formats. For example, the InnoDB engine in MySQL 5.7 offers three basic row_formats and two compressible types.

In short, how can we find the size of an InnoDB table that stores its data in its own tablespace (assuming innodb_file_per_table=1 )?

Before answering, the article shows a sysbench‑generated chart of bulk‑inserted data. The chart displays the data_length and index_length values obtained from INFORMATION_SCHEMA.TABLES , which grow in large jumps as the data set increases (sometimes by 10 GB or more).

However, the chart does not match the gradual growth observed on disk. MySQL does not maintain real‑time data_length and index_length values; they are refreshed periodically and irregularly. The second half of the chart shows more regular refreshes, while the first half appears to update only when roughly 10 % of rows change, affecting table_rows , data_free , and update_time as well.

To obtain more accurate real‑time information in MySQL 5.7 you would need to:

Disable innodb_stats_persistent

Enable innodb_stats_on_metadata

Both actions have a heavy cost: disabling persistent statistics forces InnoDB to recompute stats on every server start, which can be expensive and lead to unstable query plans between restarts.

A better solution is to query the INNODB_SYS_TABLESPACES table, which provides real‑time file‑size information without special configuration. Unlike index_length and data_length , the values from INNODB_SYS_TABLESPACES are updated continuously.

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES WHERE name='sbinnodb/sbtest1' \G
*************************** 1. row ***************************
        SPACE: 42
         NAME: sbinnodb/sbtest1
         FLAG: 33
  FILE_FORMAT: Barracuda
   ROW_FORMAT: Dynamic
    PAGE_SIZE: 16384
ZIP_PAGE_SIZE: 0
   SPACE_TYPE: Single
FS_BLOCK_SIZE: 4096
    FILE_SIZE: 245937209344
ALLOCATED_SIZE: 245937266688
1 row in set (0.00 sec)

This table also reports the new InnoDB page‑compression fields file_size (logical file size on disk) and allocated_size (space allocated for the file), which can differ significantly when compression is used.

Finally, the article compares how old InnoDB compression (table‑compression) and the newer page‑compression affect the values shown in information_schema . With old compression, data_length and index_length display the compressed size, while with page compression the values correspond to the actual file size rather than the allocated size.

Conclusion : Answering the seemingly trivial question “how much space does this table occupy on disk?” is not simple in MySQL; the apparent data can be misleading. Query INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES to obtain the true file‑size values for InnoDB tables.