Finding MySQL Table Size on Disk

MySQL table sizeSo 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):

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:

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

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.

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.  

Share this post

Comments (10)

  • SuperQ Reply

    I was planning to implement a crappy filesystem scanning metrics collector to deal with the table sizes, but this is way better. 😀

    January 26, 2016 at 5:52 pm
  • Peter Zaitsev Reply

    Thanks! I’m glad this is helpful.

    January 26, 2016 at 5:57 pm
  • Alexey Reply

    The text refers to a table size graph, but I can’t see it in the post.

    January 27, 2016 at 2:46 am
  • Francesco Cuomo Reply

    System is falling down, offsetting

    January 27, 2016 at 3:08 am
  • Peter Zaitsev Reply

    Hi Alexey,

    Sorry graph was removed for some reason during minor edit. I have readded it now.

    January 27, 2016 at 7:44 am
  • Francesco Cuomo Reply

    Dynamic flux was shown like a visible arrows. Arco was growing in a red circular ways.
    Thank u’ for the contributing.
    Black horse

    January 27, 2016 at 10:23 am
  • Randy Diffenderfer Reply

    is the display for information_schema.innodb_sys_tablespaces version dependent? i have “Antelope” file format, and don’t see any of the file_size or allocated_file_size attributes?!

    December 16, 2016 at 3:25 pm
  • SL Reply

    Is there any way to check the same on older versions?
    Thank you.

    March 27, 2017 at 4:23 pm
  • joncomputing Reply

    Heads up for Shared Hosting users and anyone sharing a Database Server with another customer: accessing INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES requires the PROCESS privilege, which you won’t likely have. This was tested on ICDSoft and Hostgator shared hosting, and both returned an Error #1227 – Access denied; you need (at least one of) the PROCESS privilege(s) for this operation.

    When I checked into this with ICDSoft Support, I was told: “This privilege cannot be granted to end-users for security reasons.”

    November 9, 2017 at 12:53 pm
  • Avi Vainshtein Reply

    Is there something similar available in 5.6 version – to able to calculate the file size / allocate size ?

    June 21, 2018 at 3:26 am

Leave a Reply