Resolving MySQL Disk Usage 100% Issue by Removing InnoDB Fragmentation

Problem : The MySQL server reports 100% disk usage with only a few megabytes left.

Initial attempts : Backed up the database, deleted instances and tables, and restarted the MySQL service, but none of these actions freed disk space.

Investigation : A query against information_schema.tables showed that the data_free column for the table datainfo occupied about 20 GB, indicating severe fragmentation.

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 MySQL version (5.5.62) did not support InnoDB, so the usual ALTER TABLE ... ENGINE=InnoDB approach was not viable.

Effective solution (the "later miracle operation") :

1. Locate MySQL binary and configuration

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

2. Stop MySQL

service mysql stop

3. Delete InnoDB system files

Remove ibdata1 , ib_logfile0 , and ib_logfile1 from the datadir directory.

4. Move the old configuration file

mv /etc/my.cnf ./abc

5. Restart MySQL

service mysql start

After restarting, the previously occupied disk space was released.

Next steps – database restoration :

Use Navicat to export a backup file (e.g., 200409141055.psc ).

Create a new database instance with appropriate name and character set.

Import the backup; adjust restore settings to avoid transaction‑stop on errors, then re‑run the restore until data appears correctly.

Root cause of MySQL fragmentation :

Deleted rows leave empty space; massive deletions can make the empty area larger than the occupied data.

Insert operations may not perfectly reuse these gaps, creating fragmented storage.

During scans, MySQL reads up to the table’s capacity limit, which includes fragmented regions.

Benefits of defragmentation : Reduces I/O when accessing tables, improves MySQL performance, and lowers overall disk usage.

MySQL recommends performing OPTIMIZE TABLE no more than weekly or monthly, preferably during low‑traffic periods, because the operation locks the table.

To check your own data_free size, run:

SHOW TABLE STATUS FROM your_database LIKE 'your_table';