CODEWITHSUNDEEP
mysqlinnodb
Bogdan
Bogdan

Reputation: 2042

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?

Upvotes: 0

Views: 800

Answers (1)

Rick James
Rick James

Reputation: 142316

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

Upvotes: 2

Related Questions