Detailed example of clearing tablespace fragmentation in MySQL

Detailed example of clearing tablespace fragmentation in MySQL

Causes of fragmentation

(1) The storage of the table will be fragmented. Whenever a row is deleted, the space will become blank and left empty. A large number of deletion operations over a period of time will make the empty space larger than the space used to store the table content.

(2) When performing an insert operation, MySQL will try to use empty space. However, if an empty space has not been occupied by data of the appropriate size, it still cannot be completely occupied, resulting in fragmentation.

(3) When MySQL scans data, it actually scans the upper limit of the table's capacity requirement, that is, the peak portion of the area where the data is written;

For example:

A table has 10,000 rows, each row is 10 bytes, which will occupy 100,000 bytes of storage space. If a deletion operation is performed, only one row will be left, and the actual content will only be 10 bytes. However, when MySQL reads it, it still treats it as a 100,000-byte table. Therefore, the more fragments there are, the more it will affect query performance.

Check the size of table fragments

(1) Check the fragment size of a table

mysql> SHOW TABLE STATUS LIKE 'table name';

The value of the 'Data_free' column in the result is the fragment size

(2) List all tables that have been fragmented

mysql> select table_schema db, table_name, data_free, engine   
from information_schema.tables 
where table_schema not in ('information_schema', 'mysql') and data_free > 0;

Clear table fragmentation

(1) MyISAM table

mysql> optimize table table name

(2) InnoDB table

mysql> alter table table name engine = InnoDB

The operation of OPTIMIZE is different for different engines. Since the index and data of MyISAM are separated, OPTIMIZE can sort the data files and rearrange the index.

The OPTIMIZE operation will temporarily lock the table, and the larger the amount of data, the longer it will take. After all, it is not a simple query operation. Therefore, it is inappropriate to put the Optimize command in the program. No matter how low the hit rate is set, when the number of visits increases, the overall hit rate will also increase, which will definitely have a great impact on the running efficiency of the program. A better way is to make a shell, regularly check the information_schema.TABLES field in mysql, check the DATA_FREE field, if it is greater than 0, it means there is fragmentation.

suggestion

The clearing operation will temporarily lock the table. The larger the amount of data, the longer it takes. You can create a script and execute it regularly during low-access hours. For example, check the DATA_FREE field at dawn every Wednesday. If it is greater than the warning value you think is, clean it up once.

If you have any questions, please leave a message or come to the community to discuss. Thank you for reading and I hope it can help you. Thank you for your support of this site!