Menu
I have several databases in MySQL with InnoDB engine. All together they have around 30 GB size on filesystem. A couple of days ago, I removed a lot of data from those databases (~10-15 GB) but the used space on filesystem is the same and also reading data_length and index_length from information_schema.TABLES give almost the old size.
I dumped a 3,3 GB database and imported it on my workstation where it takes only 1,1 GB (yes, it is a 1:1 copy). So, how can I calculate the size a InnoDB database needs if I would reimport it in a new system?
228
To check the sizes of all of your databases, at the mysql> prompt type the following command: Copy SELECT table_schema AS "Database", ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)" FROM information_schema.
Optimize your tables after deleting large amouts of data. http://dev.mysql.com/doc/refman/5.1/en/optimize-table.html
90
InnoDB doesn't free disk space; it's a PITA. Basically, you can free it by dropping the database and restoring it from a backup (as you've noticed by chance) - see here for examples.
So, you can't calculate how big a database will be after you restore a backup. But it will never be bigger than the un-backed up one (because the unbacked up version still has space from any deleted data and the restored backup will not have that space).
This can be worked around to some extent using the file per table option; more details in the first link from this post.
39
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
