How Can I Reclaim Disk Space Used by MySQL InnoDB Tables?

Disk Space Reclaiming in MySQL InnoDB

When deleting data rows from a MySQL table using the InnoDB storage engine, the allocated disk space is not released automatically. This can lead to growing ibdata1 file size and potential disk space issues.

One method to address this issue is to use the "optimize table" command. However, this command does not reduce the size of the ibdata1 file. MySQL intentionally retains this space for potential reuse in future operations.

To reclaim disk space from MySQL, there are two main approaches:

1. Drop and Recreate the Table:

• Drop the existing table.
• Recreate a new table with the same schema using the ROW_FORMAT=COMPRESSED option.
• Reinsert the data into the new table.

This approach releases the allocated disk space but requires a backup and restore.

2. Configure innodb_file_per_table:

• Edit the MySQL configuration file (my.cnf) and add the following line:

  innodb_file_per_table=1

• Restart the MySQL service.

This option creates a separate .ibd file for each table in the database. When you delete data rows and run "optimize table," the .ibd file for that table will be reduced in size.

The above is the detailed content of How Can I Reclaim Disk Space Used by MySQL InnoDB Tables?. For more information, please follow other related articles on the PHP Chinese website!