Tales From A Lazy Fat DBA

Large MySQL table defragmentation looks easy on paper. You see a 500 GB or 1 TB .ibd file, notice that the table has gone through years of INSERTs, UPDATEs and DELETEs, and the obvious thought is: rebuild it and get the space back. That was exactly where I started …

What I learned very quickly is that once a table reaches a few hundred gigabytes, defragmentation stops being a simple maintenance command and becomes an operational engineering exercise. You have to think about stale statistics, how InnoDB actually uses pages, temporary space requirements, metadata locks, binary log generation, replica lag, recovery from interruption and, perhaps most importantly, whether the rebuild will reclaim any meaningful space at all. During my testing, I used native MySQL rebuild methods, looked closely at gh-ost, and eventually standardized on Percona Toolkit’s pt-online-schema-change. But the real solution was not just choosing pt-osc. The bigger win came from building a restartable and monitorable automation layer around it.

The examples below use synthetic table and schema names, and some sizes are rounded, but the behaviour and lessons come directly from real large-table testing.

The first surprise: a large .ibd does not automatically mean a large reclaim opportunity.

-- One of the first discovery queries I used was fairly standard:
SET SESSION information_schema_stats_expiry = 0;
SELECT
    table_schema,
    table_name,
    table_rows,
    ROUND(data_length/1024/1024/1024,2)  AS data_gib,
    ROUND(index_length/1024/1024/1024,2) AS index_gib,
    ROUND(data_free/1024/1024/1024,2)    AS data_free_gib,
    ROUND((data_length+index_length)/1024/1024/1024,2) AS used_gib
FROM information_schema.tables
WHERE table_schema='LAB'
  AND table_name='position_history_2023';

-- At the OS level I checked the actual tablespace:
ls -lh /var/lib/mysql/LAB/position_history_2023.ibd

-- One table gave me something that initially looked fantastic:
Physical .ibd        : 356.64 GiB
DATA_LENGTH          : 19.22 GiB
INDEX_LENGTH         : 32.44 GiB
Reported used        : 51.66 GiB
DATA_FREE            : 0.01 GiB

-- The first calculation looked obvious:
356.64 - 51.66 = ~305 GiB potential reclaim

-- If I had trusted that number, I would have scheduled a long rebuild immediately. Instead, I checked the InnoDB persistent statistics:

SELECT
    database_name,
    table_name,
    last_update,
    n_rows,
    clustered_index_size,
    sum_of_other_index_sizes
FROM mysql.innodb_table_stats
WHERE database_name='LAB'
  AND table_name='position_history_2023';

-- The statistics were years old and MySQL estimated only around 42 million rows. I refreshed them:
ANALYZE TABLE LAB.position_history_2023;

-- Then ran the size query again. This time the result looked completely different:
Estimated rows       : ~297 million
DATA                 : 134.55 GiB
INDEX                : 217.89 GiB
Used                 : 352.44 GiB
Physical .ibd        : 356.64 GiB
Difference           : ~4.20 GiB

-- The apparent 305 GiB reclaim opportunity had disappeared simply because the statistics were refreshed.

So, the first lesson learned for anyone … Never call physical .ibd - DATA_LENGTH - INDEX_LENGTH “reclaimable space” unless you first know the statistics are current. At best, treat it as a signal.

Now, Why I became uncomfortable with native MySQL rebuilds ??

The most obvious native approach is something like:

OPTIMIZE TABLE LAB.big_table; OR ... ALTER TABLE LAB.big_table ENGINE=InnoDB, ALGORITHM=INPLACE, LOCK=NONE;

For small tables, or where a maintenance window is available, I still use native methods. There is nothing inherently wrong with them. The problem appears when the table is hundreds of gigabytes or larger. …. The OPTIMIZE TABLE for InnoDB is essentially mapped to an ALTER TABLE operation that rebuilds the table and updates index statistics. MySQL also notes that secondary indexes may not be created as efficiently during this process because the secondary-key entries are inserted according to primary-key order.

That immediately leads to one assumption that I had to unlearn —> A rebuild does not guarantee that the .ibd file will become smaller.

The command itself was perfectly valid: ALTER TABLE LAB.native_test ENGINE=InnoDB, ALGORITHM=INPLACE, LOCK=NONE;

Before and after I checked: ls -lh /var/lib/mysql/LAB/native_test.ibd … The test result was essentially:
Before rebuild : X GiB
After rebuild : X + several GiB

The exact number was less important than the behaviour. Nothing had failed. InnoDB had simply produced a different page and index layout during reconstruction. Secondary index layout, page occupancy and record distribution all influence the final physical size. That is why I no longer equate the word rebuild with the word shrink. The other thing that bothered me was the word ONLINE. Many people read –> LOCK=NONE and mentally translate it into: low-risk background operation .. That is not what MySQL promises.

For large online DDL operations that rebuild tables, MySQL explicitly documents several limitations: there is no native pause mechanism, no native CPU or I/O throttling mechanism, rollback can be expensive if the operation fails, and long-running online DDL can create replication lag. The operation also still needs an exclusive metadata lock during its final phase. That matters a lot when your table is 1 TB.

Imagine this: ALTER TABLE LAB.one_terabyte_table ENGINE=InnoDB, ALGORITHM=INPLACE, LOCK=NONE;

It starts at 8:00 AM and everything looks fine. Six hours later application workload increases sharply. Now I want to say “Pause the rebuild for one hour” .. Sorry, there is no simple native control for that. Or maybe the filesystem begins filling faster than expected and I want to reduce the copy rate. Again, there is no convenient built-in throttle control for the running operation. And if that large operation fails after many hours, rollback itself can be expensive. MySQL specifically calls that out as one of the limitations of large online DDL operations.

Disk space is another concern. Online DDL that rebuilds indexes can need substantial temporary space, and the operation can fail if tmpdir or innodb_tmpdir runs out of space. Concurrent DML is also buffered in the online DDL log, and if that log becomes too large relative to innodb_online_alter_log_max_size, MySQL can fail the operation with an online-log-too-big condition.

Then comes the final cutover. Even an online operation must eventually update the table definition. MySQL therefore needs an exclusive metadata lock during that final phase. A long-running or forgotten transaction holding a metadata lock can block completion. For a 15-minute rebuild, I can live with some of that. For a 20-hour rebuild, I want more control.

Where gh-ost fits, and why I still did not choose it here ??
I spent time looking at gh-ost because technically it has a very attractive architecture. Unlike pt-online-schema-change, gh-ost is designed around a triggerless migration model. It reads changes through the binary log instead of creating three synchronization triggers on the original table. That is a significant design advantage in some environments.

The gh-ost project explains that trigger-based migration tools add synchronous work to application DML, and that even if copying is throttled, the triggers themselves must continue running because removing them would break synchronization. That is a fair criticism of trigger-based online schema change.

gh-ost also gives excellent throttling controls. It can throttle based on replication lag, server load, flag files, control replicas and custom queries. Its cutover design is also carefully engineered around an atomic swap so clients either see the original or the new table rather than an intermediate period where neither table is available.

So why did I not use gh-ost? Because choosing an online schema-change tool should not become a religious argument. gh-ost has its own requirements and limitations. It depends heavily on binary-log behaviour, expects a suitable primary or unique key, and has limitations around foreign keys and triggers. For an environment already standardized on row-based replication and gh-ost operational patterns, it may absolutely be the better option. In my case, pt-online-schema-change fitted the existing DBA workflow better, and more importantly I could build the restart, recovery, validation and monitoring model I wanted around it. That became the deciding factor.

Why pt-online-schema-change finally won ?

pt-online-schema-change works on a shadow copy of the table. It creates the new table, modifies it, copies the rows in chunks, and keeps the new table synchronized with ongoing DML through triggers on the original table. Once copying completes, the original table is moved aside and the new one takes its place. The trigger approach does add overhead, and I do not pretend otherwise. What pt-osc gave me, however, was very good control of the actual copy process. For example, my wrapper uses settings such as:

–chunk-index=PRIMARY
–chunk-size=500
–chunk-time=0.5
–chunk-size-limit=4

–chunk-time allows pt-osc to dynamically adjust chunk size so copy queries target approximately the configured execution time. I also use explicit load thresholds mentioned below ..

–max-load=Threads_running=80
–critical-load=Threads_running=160

With pt-osc, –max-load pauses the copy when load becomes too high, while –critical-load aborts the operation when the configured threshold is exceeded. That gave me a safety model much closer to what I wanted for long-running operations.

The real solution was not pt-osc alone; it was the wrapper around it .. I did not want another DBA typing a 20-line pt-online-schema-change command manually every time. So I wrapped the whole workflow. The generic call became:

AUTO_DROP_OLD=0 \
nohup /defrag/tools/ptosc_restartable_defrag_PREPROD_FINAL.sh \
<SCHEMA> <TABLE> \
> /defrag/tools/<SCHEMA>.<TABLE>.restartable.nohup.out 2>&1 &

That is intentionally simple. The DBA provides: schema and the table and the wrapper deals with everything else.

A normal startup looks like this:
– Restartable pt-osc automation for LAB.big_table
– MySQL=8.0.x
– Threads_running=4
– Threads_connected=820
– PRIMARY KEY columns=id
– Data filesystem free=8.0Ti
– STATE target=1 new=0 old=0 triggers=0
– Selected action=fresh
– Running dry run before fresh restartable job.
– Dry run OK.
– Metadata-lock watchdog started.
– Starting FRESH restartable pt-osc job.

Before pt-osc really starts doing work, the wrapper checks MySQL connectivity and version, current load, filesystem free space, primary-key availability, existing _new and _old objects, leftover pt-osc triggers and previous history entries. The STATE line is one of the most useful parts of the whole implementation.

For example:
target=1
new=0
old=0
triggers=0
resumable_job=none

means I have a clean fresh start. If instead I see:

target=1
new=1
old=0
triggers=3
resumable_job=8

I know there is an interrupted operation that can potentially resume 🙂

Restartability saved me from throwing away hours of work and this became one of the strongest reasons for keeping the pt-osc solution. Suppose a 1 TB copy has been running for 15 hours and somebody kills the process. My wrapper does not blindly remove the shadow table. An interrupted run can look like this:

FAILED rc=137; no blind table/trigger cleanup performed.
POST-PTOSC STATE
target=1
new=1
old=0
triggers=3
resumable_job=8

Copy remains incomplete but is safely resumable. … DO NOT DROP LAB._big_table_new or the pt-osc triggers.

The job history is written to: percona.pt_osc_history … Percona’s –history option stores progress and chunk boundaries, and an unfinished job can be resumed using –resume.
The wrapper therefore knows whether it should perform a fresh start, resume an interrupted copy, or recover from a copy-complete state where cutover was not completed. For a five-minute table, this may not matter. For a table that has already consumed most of your weekend, it matters a lot.

DATA_FREE helped, but it was not the whole truth either ... Yes, that is another surprise was seeing a table with almost no DATA_FREE still reclaim more than 60 GiB after a rebuild.

Before:
Physical size : ~740 GiB
DATA_FREE : ~0.01 GiB
Fresh used : ~732 GiB

Based purely on that data, it did not look very interesting. After pt-osc:

Old table : ~732 GiB
New table : ~671 GiB

Actual reclaim : ~61 GiB .. Why? and this is because DATA_FREE does not tell you how well individual InnoDB B-tree pages are packed. A table can have almost no fully free extents and still have partially occupied pages throughout its clustered and secondary indexes. Rebuilding can pack that data more efficiently. That taught me another important lesson:

DATA_FREE ≈ 0 —> does not necessarily mean: –> rebuild saving = 0

At the same time, a huge difference between stale statistics and the physical file does not mean hundreds of gigabytes are reclaimable either. There is no single magic fragmentation column.

Now comes the monitoring part … How we can monitor the run ?? For long-running jobs I did not want to tail multiple logs and run many commands manually every few minutes. So I wrote a separate monitor and called it a ptosc_monitor.sh Usage is intentionally generic: ./ptosc_monitor.sh 40

That gives me almost everything I need from one screen. The monitor also shows the last lines from the actual pt-osc log and the wrapper log, so I can see whether the operation is copying, throttling, attempting cutover or recovering from an error. I added a metadata-lock watchdog because cutover is where good weekends go bad … A long-running copy can behave perfectly for 20 hours and then fail to cut over because another transaction is holding a metadata lock. .. I did not want to discover that manually. So the wrapper launches a metadata-lock watchdog that checks for blockers while pt-osc is running. The goal is not to kill random application sessions …

The goal is to know: Who is blocking? Which table? How long? Which user? What SQL? and optionally act against known blocker classes after a controlled threshold. All actions are recorded in: killed-blockers.log .. Most of my successful runs reported: “None” .. That is exactly what I want.

Every table execution gets its own directory: /root/ptosc/.// … For example: /root/ptosc/LAB.big_table/20260826_082509/

The important files are:
pt-online-schema-change.log
final-validation.txt
killed-blockers.log
ptosc.pid
pause.flag

The main wrapper output is written separately: /defrag/tools/..restartable.nohup.out

So if somebody asks me a week later: What happened during that 25-hour table rebuild? I do not need to reconstruct the story from bash history. I have the complete execution record.

The biggest lessons from this exercise were simple and are below.

• MySQL defragmentation is not just a storage calculation followed by OPTIMIZE TABLE.
• A large .ibd file does not always mean large reclaim. Stale statistics can create false positives, DATA_FREE does not tell the whole story, and even a successful rebuild may reclaim little—or sometimes produce a larger file.
• Native online DDL is also not automatically controllable. Large operations can generate replication lag, require expensive rollback, and still depend on metadata locks during cutover.
• For this workload, pt-online-schema-change provided the right foundation: chunking, throttling, restartability, and a predictable shadow-table model.
• But pt-osc alone was not the solution. The real solution was:
  pt-osc + prechecks + restart logic + disk safeguards + metadata-lock monitoring + replication monitoring + validation + structured logging.
  That turned a database utility into a repeatable production operation.

Resource Authored and Used:

– Monitoring Sxript : https://github.com/fatdba/newprojects/blob/main/MySQL-Defrag-PTOSC-Moniror.sh

– Main Defrag Explanation (Like a ReadmE) : https://github.com/fatdba/newprojects/blob/main/MySQL-Defrag-CompleteScript-PERCONA-PTOSCBased-HELPER
– Main Defrag Script : https://github.com/fatdba/newprojects/blob/main/MySQL-Defrag-CompleteScript-PERCONA-PTOSCBased.sh

Hope It Helped!
Prashant Dixit