Search code examples
mysqlinnodb

How to compute the disk space usage of a row in a mysql innodb table

I have an application that inserts many rows in a specific innodb table and I don't know how to compute the disk space needed in the future.

So given a mysql innodb table structure, how can I compute the disk space cost of a row?

Solution
    1. TINYINT consumes 1 byte, INT: 4 bytes, BIGINT: 8 bytes, TEXT/BLOB/VARCHAR/VARBINARY: 2+ average number of bytes, DATETIME and TIMESTAMP: 5 bytes, CHAR takes the full length, etc.
    2. Add those up, based on the columns in the table.
    3. Multiple by 3 (for overhead, indexes, BTree structure, wasted space, etc, etc). This will give you a crude estimate of the disk space for an InnoDB row.

    Caveats:

    • The result could be high or low, but it is a simple first guess.
    • A table will take a minimum of 16KB.
    • An excessive number of secondary indexes will lead to more space consumed.
    • Lots of TEXTs/BLOBs can lead to off-record storage that is likely to exceed the forumla.
    • At some point during the growth of a table, the allocation unit switches from 16KB to 8MB, thereby leading to disturbing jumps in the size.
    • ROW_FORMAT makes some difference. (But COMPRESSED is unlikely to shrink the table by more than 2x.)
    • Look for ways to shrink UUIDs, IP addresses, etc, to save space.
    • Don't use utf8 for ascii-only CHAR() strings (country_code, hex, etc).