How to Recover Disk Space from MySQL Fragmentation: A Step‑by‑Step Guide

Problem description

The MySQL server reported 100% disk usage with only a few megabytes remaining. A query on information_schema.tables showed that the data_free column for a table named datainfo had grown to roughly 20 GB.

Diagnosing fragmentation

Run the following SQL to view the total data size, maximum allocated size, free space and index size for a specific table:

SELECT CONCAT(TRUNCATE(SUM(data_length)/1024/1024,2),'MB') AS data_size,
       CONCAT(TRUNCATE(SUM(max_data_length)/1024/1024,2),'MB') AS max_data_size,
       CONCAT(TRUNCATE(SUM(data_free)/1024/1024,2),'MB') AS data_free,
       CONCAT(TRUNCATE(SUM(index_length)/1024/1024,2),'MB') AS index_size
FROM information_schema.tables
WHERE TABLE_NAME = 'datainfo';

The result confirmed that data_free was about 20 GB, indicating heavy internal fragmentation.

Attempts that did not work

Backing up the database, dropping the instance, dropping tables, and restarting MySQL did not release any space.

Running ALTER TABLE datainfo ENGINE=InnoDB;, ANALYZE TABLE datainfo; and OPTIMIZE TABLE datainfo; failed because the MySQL version (5.5.62) lacked InnoDB support.

Effective solution to reclaim disk space

Identify the MySQL binary and its configuration parameters. An example of the running process is:

mysql 1118 945 0 14:28 ? 00:00:00 /usr/sbin/mysqld \
    --basedir=/usr \
    --datadir=/var/lib/mysql \
    --plugin-dir=/usr/lib64/mysql/plugin \
    --log-error=/var/log/mysqld.log \
    --pid-file=/var/run/mysqld/mysqld.pid \
    --socket=/var/lib/mysql/mysql.sock

The --datadir flag shows where the InnoDB system files are stored.

Stop the MySQL service to ensure no file handles are open: service mysql stop (or systemctl stop mysqld on systemd‑based systems).

Delete the InnoDB system tablespace files from the data directory. These files are typically named ibdata1, ib_logfile0 and ib_logfile1:

rm /var/lib/mysql/ibdata1
rm /var/lib/mysql/ib_logfile0
rm /var/lib/mysql/ib_logfile1

Warning: This permanently removes all InnoDB data. Ensure you have a logical backup before proceeding.

Rename or move the MySQL configuration file so that any InnoDB‑specific options are not re‑applied on restart: mv /etc/my.cnf /etc/my.cnf.bak Start MySQL again: service mysql start After the service starts, the data directory is recreated without the large InnoDB system files, and the previously occupied disk space is freed.

Database restoration

Because the InnoDB files were removed, the logical data must be re‑imported from a backup. The typical steps are:

Export the database using a tool such as Navicat, which creates a .psc (SQL dump) file (e.g., 200409141055.psc).

Create a new database instance with the desired name and character set.

Import the dump file. If the import stops on errors, adjust the restore settings to ignore errors and run the import again.

Root causes of MySQL fragmentation

Row deletions leave empty pages; a large number of deletions can make the amount of free space larger than the space actually used.

During inserts MySQL may be unable to reuse small gaps, creating fragmented storage.

When scanning tables MySQL reads up to the maximum allocated capacity, which can leave unused space at the end of the file.

Benefits of cleaning fragments

Reduced I/O during table reads.

Improved query performance.

Lower overall disk‑usage percentage.

Precautions

MySQL recommends running fragmentation cleanup (e.g., OPTIMIZE TABLE) only weekly or monthly, not hourly or daily.

The operation locks the table, so schedule it during low‑traffic periods.

Example: optimizing a student table with 1.05 million rows took about 37 seconds on a local test machine.

Self‑test

Run the following command to inspect the Data_free column for any table in your own MySQL instance:

SHOW TABLE STATUS FROM your_database LIKE 'your_table';

If the Data_free value is large relative to Data_length, consider applying the cleanup steps described above.