Freeing space with shrinking MySQL database files

So, I am maintaining customers VPS server with PHP application and MySQL as a database. The application is hosted in AWS cloud and is using as little resources as possible.

This one day the filesystem space was tight, so I had to delete old data (from old archive tables) and make some space available on the ext4 filesystem.

At the beginning we decided that MySQL should store data in separate files, which is more convenient, faster and easyer to manage. We added this to /etc/mysql/my.cnf configuration file and restarted the databse (years ago):

[mysqld]
innodb_file_per_table=1

There are several databases, but one has application log tables, which we store, but are not really important, so I could delete the data:

mysql> DELETE FROM log_table;
mysql> DELETE FROM log_table_2021;

The files log_table.ibd and log_table_2021.ibd did not shrink until I ran:

mysql> OPTIMIZE TABLE log_table;
mysql> OPTIMIZE TABLE log_table_2021;

The result should be something like:

+---------------------------------+----------+----------+-------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+---------------------------------+----------+----------+-------------------------------------------------------------------+
| database_logs.log_table | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| database_logs.log_table | optimize | status | OK |
+---------------------------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (3.87 sec)
+---------------------------------+----------+----------+-------------------------------------------------------------------+ | Table                           | Op       | Msg_type | Msg_text                                                          | +---------------------------------+----------+----------+-------------------------------------------------------------------+ | database_logs.log_table_2021 | optimize | note     | Table does not support optimize, doing recreate + analyze instead | | database_logs.log_table_2021 | optimize | status   | OK                                                                | +---------------------------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (3.22 sec)

…and the files log_table.ibd and log_table_2021.ibd both shrank from 9.4GB each to about 176kB.

Now we have 18.8GB available on the filesystem. Yeey!

Written by