How to Resolve MySQL Disk Space Exhaustion and Fragmentation Issues

Problem

Disk usage reported 100% while only a few dozen megabytes remain.

Initial attempts

Backup database, delete instance, drop tables, restart MySQL service – none freed space.

What to do

Check data_free via SQL:

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';

Data_free reached about 20 GB.

Recommended actions: run ALTER TABLE ENGINE=InnoDB, ANALYZE TABLE, and OPTIMIZE TABLE.

ALTER TABLE datainfo ENGINE=InnoDB;
ANALYZE TABLE datainfo;
OPTIMIZE TABLE datainfo;

MySQL version 5.5.62 does not support InnoDB, requiring an upgrade. The table was later deleted, still no space freed.

Later successful steps

Locate MySQL binary and configuration (example ps aux | grep mysqld output shown).

Stop MySQL: service mysql stop Delete ibdata1, ib_logfile0, ib_logfile1 in the datadir.

Move the my.cnf file: mv /etc/my.cnf ./abc Start MySQL: service mysql start – disk space is released.

Database restoration

Use Navicat to backup, create a new instance, restore the generated .psc file, then re‑import tables. Adjust restore configuration to avoid transaction abort on errors.

Root cause of MySQL fragmentation

Deleted rows leave empty space; after many deletions the empty space can exceed the space used by remaining rows.

Insertions may not reuse small gaps, creating fragmentation.

Scanning uses the table’s capacity peak, contributing to wasted space.

Benefits of cleaning fragmentation

Reduces I/O, improves MySQL performance, and lowers disk usage.

Precautions

Do not run OPTIMIZE TABLE too frequently; weekly or monthly is sufficient.

The operation locks tables, so run during low‑traffic periods.

Example: optimizing a table with 1.05 million rows takes about 37 seconds locally.

Self‑test

Check your own tables with:

SHOW TABLE STATUS FROM <your_database>;