Database Table Space Reclamation

Table Storage Location

  • The table data storage location is controlled by the parameter innodb_file_per_table.
    1. OFF: Table data is stored in the system’s shared tablespace, i.e., alongside the data dictionary.
    2. ON: Each InnoDB table data is stored in an individual file with a .ibd suffix.

Note: Starting from MySQL version 5.6.6, the default value is set to ON. It is recommended to set this parameter to ON because managing individual files for each table is more straightforward. Dropping a table will directly delete its corresponding file if stored individually. But, if stored in a shared tablespace, even after deleting the table, the space won’t be reclaimed.

Data Deletion Process

  • When deleting a record (e.g., R4), the InnoDB engine only marks it as “deleted”. If a new record is added between two existing IDs (e.g., between 300 and 600), the engine might reuse the “deleted” slot. However, the size of the disk file remains unchanged.

  • Data in InnoDB is stored in pages. Deleting all records on a page will mark that page as reusable. If two adjacent data pages have low utilization, they might be merged, marking one as reusable.

  • Using the delete command to erase all data in a table will mark all its pages as reusable. Still, the file’s size on the disk remains unchanged.

  • Updating a value in an index can be viewed as deleting an old value and inserting a new one, which can also create gaps or “holes”. After numerous insertions, deletions, and modifications, a table can have these gaps. To remove these gaps and shrink the table space, you can rebuild the table using the alter table A engine=InnoDB command.

  • Starting from MySQL 5.6, the “Online DDL” feature optimizes this process:

    1. A temporary file is created, scanning all data pages of table A’s primary key.
    2. Data pages from table A are used to generate a B+ tree, which is stored in the temporary file.
    3. All operations on table A during this process are logged in a row log file, corresponding to the state2 stage.
    4. After the temporary file is created, operations from the log file are applied to the temporary file to get a data file identical to table A, corresponding to the state3 stage.
    5. The temporary file then replaces table A’s data file.

Note: Because of the log file operation recording and replay functionality, this approach allows for adding, deleting, and modifying table A during the rebuilding process. In versions prior to 5.5, new data writes to table A during this process could lead to data loss. This entire DDL process is completed within InnoDB. From the server layer’s perspective, since the data isn’t moved to a temporary table, it’s an “in-place” operation, hence the name “inplace”.

Tip: If you have a 1TB table and only 1.2TB of disk space, an inplace DDL might not be feasible since the tmp_file requires temporary space.