BiteSizedChunks.comLearn one small thing at a time.
Course contentsShow
← Databases & SQL
Lesson 989 of 1,9539. MySQL and MariaDB EcosystemsFree lesson

Managing Tablespace Fragmentation

Execute strategies for shrinking physical files and reclaiming allocated storage space.

In this lesson, you will learn how to reclaim unused physical disk space from InnoDB tablespaces after significant data deletion or modification.

Understanding Fragmentation

In InnoDB, when you delete a large volume of rows, the database marks those pages as "free" within the tablespace file (.ibd), but it does not automatically shrink the file size on your operating system. The file maintains its high-water mark, essentially leaving empty "holes" inside the data file. This leads to wasted storage and potentially inefficient I/O, as the database must still navigate these sparse pages.

Reclaiming Space: The OPTIMIZE TABLE Strategy

The most effective way to shrink physical files and defragment data in MySQL and MariaDB is using the OPTIMIZE TABLE command.

When you run OPTIMIZE TABLE your_table_name;, InnoDB performs an "online rebuild" of the table. It creates a new, temporary tablespace file, copies all the data into it while packing the records as tightly as possible, and then swaps the new file with the old one.

-- Reclaim space for a specific table
OPTIMIZE TABLE employees;

Important Considerations

Before running this, keep in mind:

  1. Disk Headroom: You need enough free disk space to accommodate a second copy of the table during the optimization process. If your table is 100GB, you need at least another 100GB of free space on the partition.
  2. Performance Load: While modern InnoDB implementations allow this to happen while the table remains readable and writable, it is a resource-intensive process that generates significant I/O and CPU overhead. Always execute this during low-traffic maintenance windows.
  3. Tablespace Configuration: If you use innodb_file_per_table = OFF, all tables are stored in the shared system tablespace (ibdata1). Because the system tablespace cannot be shrunk easily, reclaiming space requires a full database dump, a stop of the service, the deletion of the data files, and a restoration of the dump. This reinforces why innodb_file_per_table = ON is the best practice for management.

Key Takeaway: To reclaim physical disk space from fragmented tables, use OPTIMIZE TABLE to rebuild the data structure, ensuring you have sufficient disk headroom and performing the operation during off-peak hours.