Question

How to reclaim disk space in MySQL managed database 

I just noticed that my disk space is full in mysql managed database. I have deleted unwanted significant amount of data from mysql database. However, I do not have my disk space back at all.

Is there a way to reclaim disk space from mysql database?


Submit an answer

This textbox defaults to using Markdown to format your answer.

You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!

Sign In or Sign Up to Answer

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

Want to learn more? Join the DigitalOcean Community!

Join our DigitalOcean community of over a million developers for free! Get help and share knowledge in Q&A, subscribe to topics of interest, and get courses and tools that will help you grow as a developer and scale your project or business.

Hi,

MySQL managed database is configured to use inodb_file_per_table=ON which means that for every table its data and indexes are stored together in a separate .idb file. Over time, when a table receives a lot of inserts and deletes, the amount of space it occupies on disk can grow significantly larger than the current data in the table. A classic example of this would be a table containing jobs for a work queue in which rows are repeatedly added to the end of the table and removed from the beginning. InnoDB does not release this allocated space back to the operating system automatically (in case the table grows larger in the future) but this can cause problems. Since every other table exists in its own .idb file, the allocated but unused space is unavailable for them to grow into.

Identify problem tables

First run the following query to identify tables with significant allocated but unused space. SELECT TABLES.TABLE_SCHEMA, TABLES.TABLE_NAME, (TABLES.DATA_LENGTH + TABLES.INDEX_LENGTH) / 1024 / 1024 AS “MB used (estimate)”, TABLES.DATA_FREE / 1024 / 1024 AS “MB allocated but unused (estimate)”, INNODB_TABLESPACES.FILE_SIZE / 1024 / 1024 AS “MB on disk” FROM information_schema.TABLES JOIN information_schema.INNODB_TABLESPACES ON (INNODB_TABLESPACES.NAME = TABLES.TABLE_SCHEMA || ‘/’ || TABLES.TABLE_NAME) WHERE INNODB_TABLESPACES.FILE_SIZE > 10 * 1024 * 1024 ORDER BY TABLES.DATA_FREE DESC; You can see from the query results that if a table has significantly more allocated but unused space than used space then that increases the size of the table on disk. Note that, by default, statistics in information_schema.TABLES are only updated every 24 hours or whenever the ANALYZE_TABLE command is run.

Reclaim disk space

For tables with a high ratio of allocated but unused space to used space, you can ask InnoDB to release disk space back to the operating system by running the OPTIMIZE TABLE command. Beware that under certain conditions(e.g. including the presence of a FULLTEXT index) this command will copy the data to a new table containing just the current data and then drop and rename the new table to match the old one. During this process data modification will be blocked and it will require enough free space to store two copies of the current data at once.

Regards Priyanka