Why the disk space is not released after deleting data in MySQL

Problem Description

After deleting data using the delete statement in MySQL, the available disk space in the monitoring view did not increase, the disk usage did not decrease, and so on.

Solution

The reason why delete does not release space is due to MySQL's own mechanism. You need to rebuild the table to release disk space. You can refer to the following operations:

1. Execute optimize table ${table_name}.
2. If it is an InnoDB table, execute alter table ${table_name} engine = innodb.
   

There are two issues to note:

Both commands will rebuild the table. Try not to perform the operation when the disk space is tight (>90%). Expand the disk first and then shrink it after the operation.
Both commands will attempt to acquire metadata lock at the beginning and end, so try not to execute them during business peak hours.

Problem Analysis

In the MySQL mechanism, the rows deleted by delete are only marked as deleted. If there are many rows deleted and all the rows of the entire data page (innodb_page) will be deleted, the data page will only be marked as deleted. It will not be physically deleted, but will be occupied all the time, waiting to be reused.

For example:

It can be seen that data_length does not change before and after delete, but data_free increases a lot. This means that although the data has been deleted, it has not been released and is still occupied by the test1 table, but is displayed as being in a free state. When new data is written in the future, it can be reused directly without applying for new disk space.

At this time, use alter table test1 engine = innodb to see the effect:

You can see that data_length and data_free have both become empty tables, with only one innodb_page (default 16k).

PS: data_free itself can also be used to evaluate the space fragmentation of a table. When this number is very high, you can consider using the same method to rebuild the table and reclaim some disk space.

The above are the details of why the disk space is not released after MySQL Delete deletes data. For more information about why the disk space is not released after MySQL deletes data, please pay attention to other related articles on 123WORDPRESS.COM!