Every DBA has had that moment where a command looks simple on paper, but the database decides to teach you a lesson in production-style humility. For me, this happened during a MySQL InnoDB space reclaim activity. The task looked straightforward. We had a few large InnoDB tables where a lot of data had been deleted or moved over time. The logical data inside the table was much smaller than the physical .ibd file sitting on disk. So the goal was simple: rebuild the table, compact the data, and return unused space back to the filesystem.

In MySQL terms, this usually means running —–> OPTIMIZE TABLE XXXX;

The environment was MySQL Community Server 8.0.43. The tables were InnoDB tables, using innodb_file_per_table, which means each table had its own .ibd file on disk. That part is important because if the table is stored in its own file per table tablespace, rebuilding the table can physically shrink the .ibd file and return unused space back to the operating system … At least, that was the expectation. Before starting, we checked the table size from MySQL and from the operating system. For example, one test table, let’s call it appdata.customer_activity_history, had a physical .ibd file that was much larger than the active data inside the table.

The checks were simple and familiar:

SELECT NAME, SPACE_TYPE, ROW_FORMAT, PAGE_SIZE, ROUND(FILE_SIZE/1024/1024/1024,2) AS FILE_SIZE_GB, ROUND(ALLOCATED_SIZE/1024/1024/1024,2) AS ALLOCATED_SIZE_GB, 
STATE FROM information_schema.INNODB_TABLESPACES WHERE NAME = 'appdata/customer_activity_history';

-- And from the OS side:
du -sh /var/lib/mysql/appdata/customer_activity_history.ibd

The table had a clear gap. The .ibd file was large, and based on the data pattern, we expected a rebuild to reclaim a good amount of space. So we ran the usual command —> OPTIMIZE TABLE appdata.customer_activity_history

The command completed, and MySQL returned the familiar InnoDB message … “Table does not support optimize, doing recreate + analyze instead” .. BTW, this message is normal for InnoDB. It simply means MySQL is not doing a classic MyISAM style optimize. For InnoDB, MySQL translates the operation into a table rebuild and analyze style operation.

mysql> OPTIMIZE TABLE appdata.customer_activity_history;

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

2 rows in set (3 hours 8 min 33.18 sec)

But then came the surprise. When we checked the .ibd file again, the space was not reclaimed as expected. In some cases, the file looked almost the same. In other cases, it actually increased. That is the kind of thing that makes you stop for a minute.

You run a command to reclaim space, wait for a long table rebuild to finish, and then the file gets bigger. Not exactly the kind of result you want to explain with confidence. The first reaction was to question the obvious things. Was the table really using InnoDB? Was innodb_file_per_table enabled? Was the file we checked really the correct .ibd file? Were there long-running transactions? Was there enough free disk space? Was this table partitioned? Were stats outdated? Was the table still holding more active data than expected?

All of those checks were important. Nothing looked wrong from a basic validation point of view. The table was InnoDB. The table had its own .ibd file. The table was accessible. There was enough disk space. The command completed successfully. But the physical reclaim was still not happening the way we expected.

At this point, the issue looked less like a data problem and more like a rebuild method problem. The fix came from changing how MySQL performed the rebuild. Instead of relying on the default optimized ALTER behavior, we forced MySQL to use the older copy style ALTER method by setting this session parameter before running the optimize command …. old_alter_table

Then we ran the optimize again in the same session:

SET old_alter_table=1;
OPTIMIZE TABLE appdata.customer_activity_history;

This small change made the difference … with old_alter_table=1, MySQL does not use the newer optimized ALTER processing method. It falls back to the older temporary table copy method. In simple words, MySQL creates a new copy of the table, copies the active data into it, rebuilds the indexes, and then renames the rebuilt table back into place. That copy style rebuild gave us the clean physical rebuild we were looking for.

After running the command with the session parameter enabled, we checked the .ibd file again and this time, the file size reduced as expected. That was the “okay, now we are getting somewhere” moment.

But one successful table is not enough. A DBA never trusts one clean result when dealing with large tables and filesystem reclaim. So we repeated the same process on a few more large InnoDB tables and for each table, we followed the same careful pattern. We checked the file size before starting. We checked available disk space. We made sure there were no obvious long-running transactions or blockers. We ran SET old_alter_table=1; in the same MySQL session. Then we ran OPTIMIZE TABLE. After it completed, we checked the .ibd file again from the OS level and validated that the table was still accessible.

The important part was not just that the command completed. The important part was that the physical file size actually reduced after the operation. That confirmed the workaround was repeatable. One detail that should not be missed is that old_alter_table is a session-level setting when set this way. It is not something you run once in one window and then assume it applies everywhere forever. If the MySQL session disconnects, or if you open a new MySQL session, you need to run it again before the next OPTIMIZE TABLE.

Another important point is disk space. This type of rebuild can temporarily require extra space because MySQL may need space for the rebuilt copy while the operation is running. So even though the final result may be a smaller .ibd file, you still need enough free filesystem space before starting. This is especially important when working with hundreds of GBs or TB-sized tables. This is also not something I would run blindly across many tables in parallel. Large InnoDB rebuilds can be heavy. They can consume I/O, generate temporary space usage, affect replication lag if replication is involved, and introduce metadata locking during parts of the operation. The more controlled approach is to run one table at a time, validate the result, then move to the next one.

The other lesson here is about expectations. OPTIMIZE TABLE sounds like a simple maintenance command, but with InnoDB it is really a table rebuild operation. And with large InnoDB tables, the rebuild method matters. The command can complete successfully from MySQL’s point of view, but that does not always mean the physical reclaim result will match what you expected. In our case, the standard optimize path did not reclaim space correctly and even increased the .ibd file size in some cases. After we added the session command SET old_alter_table=1; immediately before OPTIMIZE TABLE, MySQL used the copy-style rebuild method, and the .ibd file started shrinking as expected. The final version of the approach was simple ..

-- In the session run below param 
SET old_alter_table=1;

-- Now all set to run the optimize command and to avoid the issue
OPTIMIZE TABLE appdata.customer_activity_history;

-- Then validate from the OS:
du -sh /var/lib/mysql/appdata/customer_activity_history.ibd

-- And validate from MySQL:
SELECT
    table_schema,
    table_name,
    ROUND((data_length + index_length)/1024/1024/1024, 2) AS logical_gb,
    table_rows
FROM information_schema.tables
WHERE table_schema = 'appdata'
  AND table_name = 'customer_activity_history';

So if you ever run OPTIMIZE TABLE on a large MySQL InnoDB table and the .ibd file does not shrink, or worse, it grows, do not panic immediately. First, verify the basics. Confirm the storage engine, confirm innodb_file_per_table, check the physical file, check the logical size, and make sure you have enough disk space. It is a small setting, but in this case, it made a big difference.

And yes, sometimes the most useful DBA lessons start with a command doing the exact opposite of what you expected.Then, if the default rebuild path is not giving the expected reclaim, test the session-level copy-style rebuild method carefully

Hope It Helped!
Prashant Dixit