Geek Answers Handbook

How to calculate the required disk space for InnoDB databases?

I have several databases in MySQL with the InnoDB engine. Together, they are about 30 GB in size on the file system. A couple of days ago I deleted a lot of data from these databases (~ 10-15 GB), but the used space in the file system is the same, as well as reading data_length and index_length from information_schema.TABLES gives an almost old size.

I dumped a 3.3 GB database and imported it to my workstation, where it takes up only 1.1 GB (yes, this is a 1: 1 copy). So, how can I calculate the size of an InnoDB database if I were reimport on a new system?

+6
mysql innodb
source share
2 answers

Optimize your tables after deleting a lot of data. http://dev.mysql.com/doc/refman/5.1/en/optimize-table.html

+3
source share

InnoDB does not free up disk space; this is PITA. Basically, you can free it by dropping the database and restoring it from the backup (as you accidentally noticed) - see here for an example .

So, you cannot calculate how big the database will be after restoring the backup. But it will never be more than a backup (because the unused version still has space from any deleted data, and the restored backup will not have such space).

To some extent, this can be used using the file parameter for the table; in more detail in the first link from this publication.

+3
source share

More articles:


All Articles