Optimizing MySQL InnoDB Table Fragmentation: Using OPTIMIZE TABLE to Reclaim Disk Space - ITFROMZERO - Share tobe shared! Why does deleting millions of records fail to free up MySQL disk space? Here is how to detect InnoDB table fragmentation and safely use OPTIMIZE TABLE to reclaim disk space on production.

Table of Contents

Deleted Millions of Rows but Server Disk Space Didn’t Decrease?

Six months ago, our database partition hit a critical red alert: 95% disk space utilization. I confidently ran a DELETE statement to purge 15 million old log records, estimating we would reclaim at least 40GB of storage. But once the query finished, running df -h on the server revealed a harsh reality: not even 1MB of disk space had been freed.

If you’ve ever experienced this shock, you’re definitely not alone. When you execute a DELETE statement or update variable-length columns (VARCHAR, TEXT, BLOB), InnoDB does not immediately return disk space back to the operating system (OS). Instead, it merely marks those blocks as free for future reuse. This behavior leaves empty “holes” within data pages, a phenomenon known as Table Fragmentation.

The fallout isn’t just the financial cost of purchasing extra NVMe SSD storage. More critically, it degrades query performance. Instead of reading 1,000 records from 10 data pages, MySQL might have to scan up to 50 pages because half of their content consists of wasted blank space, directly choking the Buffer Pool.

How Does MySQL InnoDB Fragmentation Work?

To fix this issue thoroughly without taking down your production environment, you first need to understand how InnoDB organizes physical files.

1. The Prerequisite: innodb_file_per_table Configuration

InnoDB manages data using tablespaces. Enabled by default since MySQL 5.6, the innodb_file_per_table setting ensures each table is stored as an independent .ibd file on disk (typically located in /var/lib/mysql/ten_database/).

SHOW VARIABLES LIKE 'innodb_file_per_table';

If this variable is set to OFF, all table data and indexes are lumped together into a shared ibdata1 file (system tablespace). Once ibdata1 grows, no SQL command can ever shrink it. Your only recourse at that point is dumping the entire database, deleting the file, restarting MySQL, and re-importing all data from scratch.

2. The Page Fragmentation Mechanism

Data in InnoDB is organized into fixed 16KB pages. When a record is deleted, InnoDB flags it as deleted (known as a delete-mark). This space is placed on a free list waiting to be reused by future INSERT operations.

The physical .ibd file at the OS level remains exactly the same size. If the application does not write enough new data to fill those gaps, the .ibd file will stay bloated indefinitely compared to the actual data stored inside.

Step-by-Step Guide to Measuring and Defragmenting Tables

Step 1: Scan for Fragmentation Ratios and Wasted Space

Never run optimization blindly across your entire database. Instead, query the information_schema.TABLES metadata table to identify which tables hold the largest amounts of free space (DATA_FREE).

SELECT 
    TABLE_SCHEMA AS `Database`,
    TABLE_NAME AS `Table`,
    ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS `Total_Size_MB`,
    ROUND(DATA_FREE / 1024 / 1024, 2) AS `Free_Space_MB`,
    ROUND((DATA_FREE / (DATA_LENGTH + INDEX_LENGTH + DATA_FREE)) * 100, 2) AS `Fragment_Percent`
FROM 
    information_schema.TABLES
WHERE 
    TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
    AND DATA_FREE > 0
ORDER BY 
    DATA_FREE DESC;

The value in the Free_Space_MB column represents the amount of storage space you can potentially reclaim and return to the OS after optimization.

Step 2: Reclaim Disk Space with OPTIMIZE TABLE

When you discover a table where Fragment_Percent exceeds 20% and Free_Space_MB is several gigabytes or more, you can proceed with table optimization:

OPTIMIZE TABLE ten_database.ten_bang;

After executing this on an InnoDB table, MySQL will output the following result:

+-----------------------+----------+----------+-------------------------------------------------------------------+
| Table                 | Op       | Msg_type | Msg_text                                                          |
+-----------------------+----------+----------+-------------------------------------------------------------------+
| ten_database.ten_bang | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| ten_database.ten_bang | optimize | status   | OK                                                                |
+-----------------------+----------+----------+-------------------------------------------------------------------+

The message “Table does not support optimize…” might look suspicious, but this is completely normal. In reality, InnoDB does not support the legacy in-place optimization mechanism used by MyISAM. Under the hood, it executes the following command instead:

ALTER TABLE ten_database.ten_bang ENGINE=InnoDB;

The storage engine creates a temporary .ibd file, copies all live data into it (completely stripping out empty pages), rebuilds and reorganizes indexes, and then swaps the new file in place of the old one.

Step 3: Best Safety Practices for Production Environments

Available Disk Headroom: The server must have at least 1.5x to 2x the current table size in free disk space. For example, if a table is 80GB, you need at least 120GB of free space. Running out of disk space midway will cause the transaction to roll back and may hang the system.
Verify innodb_online_alter_log_max_size: MySQL supports Online DDL (allowing concurrent INSERT, UPDATE, and DELETE operations while rebuilding the table). However, ongoing writes are buffered in temporary memory. If write traffic exceeds this configured limit (defaulting to 128MB), the operation will fail immediately with an error.
Strategy for Large Tables (>50GB): Running OPTIMIZE TABLE directly on huge tables causes major I/O bottlenecks and severe replication lag on replicas. In such cases, use tools like pt-online-schema-change (from Percona Toolkit) or gh-ost to copy data in small, manageable chunks.
Always Take a Backup First: While rare, I/O interruptions or server panics during a rebuild can corrupt the tablespace. Always capture a fresh snapshot or backup before you begin.

# Safely optimize a large table using pt-online-schema-change
pt-online-schema-change \
  --alter "ENGINE=InnoDB" \
  --chunk-size=1000 \
  --max-load="Threads_running=25" \
  --critical-load="Threads_running=50" \
  --execute D=ten_database,t=ten_bang

Automating Monitoring and Cleanups Responsibly

Never throw OPTIMIZE TABLE into a cron job to run indiscriminately across your entire database. Doing so wastes tremendous I/O bandwidth and introduces unwanted resource contention.

A better, standardized approach: write a weekly monitoring script that targets only tables with fragmentation exceeding 30% and free space over 5GB. Have it send alerts or execute sequentially during off-peak maintenance windows (such as 2:00 AM – 4:00 AM). Mastering tablespace internals gives you proactive and sustainable control over your server storage capacity.