So you want to know how much space a given MySQL table takes on disk. Looks trivial, right? Shouldn’t this information be readily available in the
INFORMATION_SCHEMA.TABLES
? Not so fast!
This simple question actually is quite complicated in MySQL. MySQL supports many storage engines (some of which don’t store data on disk at all) and these storage engines often each store data in different layouts. For example, there are three “basic” layouts that the InnoDB storage engine supports for MySQL 5.7, with multiple variations for
row_formats
and two types of available compression.
So let’s simplify the situation: instead of a general question, let’s ask how to find the table size on disk for an InnoDB table stored in its own tablespace (as the parameter
innodb_file_per_table=1
provides).
Before we get to the answer, let me show you the table size graph that I get by running
sysbench
prepare (basically populating tables with multi-value inserts):
Click graphic to enlarge
This graphs shows the table size defined by
data_length
plus
index_length
captured from
INFORMATION_SCHEMA.TABLES
. You would expect gradual table growth as data is inserted into it, rather than a flat table size followed by jumps (sometimes by 10GB or more).
The graph does not match how data is changing on disk, where it is growing gradually (as expected):
-rw-r----- 1 mysql mysql 220293234688 Jan 25 17:03 sbtest1.ibd -rw-r----- 1 mysql mysql 220310011904 Jan 25 17:03 sbtest1.ibd -rw-r----- 1 mysql mysql 222499438592 Jan 25 17:07 sbtest1.ibd
As we see from this experiment, MySQL does not really maintain live
data_length
and
index_length
values, but rather refreshes them periodically – and rather irregularly. The later part of the graph is especially surprising, where we see a couple of data refreshes becoming more regular. This is different from the first part of the graph which seems to be in line with statistics being updated when when 10 percent of the rows are changed. (manual)
What makes it especially confusing is that there are other values such as
table_rows
,
data_free
or
update_time
that are updated in the real time (even though I can’t imagine why table size related values would be any more difficult to maintain in real time!).
Is there way to get real time
data_length
and
index_length
updates as we query
information_schema
? There is, but it is costly.
To get
information_schema
to provide accurate information in MySQL 5.7, you need to do two things: disable
innodb_stats_persistent
and enable
innodb_stats_on_metadata
– both of which come with significant side effects.
Disabling persistent statistics means InnoDB has to refresh the statistics each time the server starts, which is expensive and can produce volatile query plans between restarts. Enabling
innodb_stats_on_metadata
makes access to
information_schema
slower, much slower, as I wrote few years ago.
Is there a better way? It turns out there is. You can look into the tablespaces information table using
INNODB_SYS_TABLESPACES
to see the actual file size. Unlike
index_length
and
data_length
,
INNODB_SYS_TABLESPACES
is updated in real time with no special configuration required:
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)
The great thing about using this table is that it also handles new “Innodb Page Compression” properly showing the difference between
file_size
(which is the logical file size on disk) and
allocated_size
(which is space allocated for this file and can be significantly smaller):
mysql> select * from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES where name='sbinnodb/testcomp' G *************************** 1. row *************************** SPACE: 48 NAME: sbinnodb/testcomp FLAG: 33 FILE_FORMAT: Barracuda ROW_FORMAT: Dynamic PAGE_SIZE: 16384 ZIP_PAGE_SIZE: 0 SPACE_TYPE: Single FS_BLOCK_SIZE: 4096 FILE_SIZE: 285212672 ALLOCATED_SIZE: 113004544 1 row in set (0.00 sec)
Finally, let’s look into how different InnoDB compression variants impact the information provided in
information_schema
.
If you use the old Innodb compression (Innodb Table Compression) you will see the compressed data size shown in
data_length
and
index_length
as result. For example,
avg_row_length
will be much lower than you would expect.
If you use the new InnoDB compression in MySQL 5.7 (Innodb Page Compression) you will see the values corresponding to file size, not allocated size as shown in
information_schema
.
Conclusion
Answering the trivial question “How much space does this table take on disk?” is really not a simple request in MySQL – look at the obvious place and you’re likely to get the wrong value. Look at
INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES
to get the actual file size value for InnoDB tables.