The concept of MySQL tablespace fragmentation and solutions to related problems

background

If you use MySQL frequently, you will find that the disk space of MySQL data files generally keeps growing, and sometimes when you delete data or insert a batch of data, the disk space sometimes remains unchanged. What causes this strange phenomenon is MySQL table space fragmentation.

What is tablespace fragmentation?

Tablespace fragmentation refers to the existence of fragments in the tablespace. To use a more vivid metaphor, it is like a piece of A4 paper. "Tablespace fragmentation" is like tearing this A4 paper into pieces and then putting it back together. There will be some gaps between the pieces, and these gaps are "tablespace fragments". The reassembled fragments will actually be a size larger than a complete A4 paper, which also represents a problem that table space can easily cause: wasted space.

The phenomenon described in the background can be explained with a picture:

The numbers in the figure represent actual data rows, and the rounded rectangle represents the table space of a table. From left to right, the first operation is to delete data. Since MySQL is designed not to release space actively, when the data row in the table is deleted, although the data is "deleted", this part of the space is not actually released and will still be occupied by Table A. Therefore, the following scenario occurs: a lot of data in the log table is deleted, but the disk space of MySQL is not reduced.

PS: This design of not releasing space is mostly related to lazy deletion. When designing databases in the early days, the IO devices used were generally mechanical disks, whose read and write performance was much worse than that of SSDs, so deletion operations generally did not directly trigger the deletion of data on the disk.

It can be seen that after the data is deleted, two blank areas appear in the originally continuous space. This is generally called a tablespace hole. If there are too many holes, it is called tablespace fragmentation (the corresponding tablespace is continuous). Although this part of the space will not be released, it will be marked as reusable. Refer to the tablespace diagram on the far right (the third rounded rectangle). When new data is inserted, the new data will be rewritten into the tablespace hole. This also means that when writing data to a table that has deleted data on a large scale, the tablespace may not grow significantly or may not grow at all.

In fact, delete is not the only operation that can cause tablespace holes. Update can also cause this problem. For example, when modifying data in a variable-length character column such as varchar, a very small hole will appear when the column is shortened. If the column is lengthened, some data in the data row may be migrated to other places due to insufficient space.

How to check tablespace fragmentation

MySQL's system tables record tablespace usage, which can be checked with the following query:

SELECT CONCAT(table_schema,'.',table_name) AS 'table_name',
                table_rows AS 'Number of Rows',
                CONCAT(ROUND(data_length/(1024*1024),2),' M') AS 'data_size',
                CONCAT(ROUND(index_length/(1024*1024),2),' M') AS 'index_size' ,
                CONCAT(ROUND(data_free/(1024*1024),2),' M') AS'data_free',
                CONCAT(ROUND(data_free/data_length,2),' %') AS 'data_free_pct',
                ENGINE as 'engine'
FROM information_schema.TABLES
WHERE table_schema = 'tablename' 
ORDER by data_free desc;

data_free refers to the total space size of the table space fragmentation, and data_free_pct refers to the fragmentation percentage of this table. The effect is as follows:

mysql> SELECT CONCAT(table_schema,'.',table_name) AS 'table_name',
    -> table_rows AS 'Number of Rows',
    -> CONCAT(ROUND(data_length/(1024*1024),2),' M') AS 'data_size',
    -> CONCAT(ROUND(index_length/(1024*1024),2),' M') AS 'index_size' ,
    -> CONCAT(ROUND(data_free/(1024*1024),2),' M') AS'data_free',
    -> CONCAT(ROUND(data_free/data_length,2),' %') AS 'data_free_pct',
    -> ENGINE as 'engine'
    -> FROM information_schema.TABLES
    -> WHERE table_schema = 'sbtest'
    -> ORDER by data_free desc;
+----------------+----------------+-----------+------------+-----------+---------------+--------+
| table_name | Number of Rows | data_size | index_size | data_free | data_free_pct | engine |
+----------------+----------------+-----------+------------+-----------+---------------+--------+
| sbtest.sbtest5 | 0 | 0.02 M | 0.00 M | 44.00 M | 2816.00 % | InnoDB |
| sbtest.sbtest4 | 986400 | 214.70 M | 15.52 M | 4.00 M | 0.02 % | InnoDB |
| sbtest.sbtest3 | 986400 | 214.70 M | 15.52 M | 4.00 M | 0.02 % | InnoDB |
| sbtest.sbtest2 | 986400 | 214.70 M | 15.52 M | 4.00 M | 0.02 % | InnoDB |
| sbtest.sbtest1 | 987400 | 199.70 M | 15.52 M | 4.00 M | 0.02 % | InnoDB |
+----------------+----------------+-----------+------------+-----------+---------------+--------+
5 rows in set (0.00 sec)

The first row of data is used for testing. All data in the table has been deleted, so the calculated data_free_pct exceeds 100%.

How to solve the table space fragmentation problem

Currently, there is only one way to reclaim table space, which is to rebuild the table, including but not limited to optimize, alter table, etc. Some operations of alter table can only be completed by rebuilding the table. Therefore, sometimes after performing some maintenance operations on a large table, you will see a decrease in disk space usage. This is because the space freed up by table space fragments has been reclaimed.

From general experience, it is not recommended to perform the table space fragmentation recovery operation frequently. Once a month is sufficient, because rebuilding the table will have a greater impact on server resources and will affect the write operation of this table. You don't need to worry too much if the fragmentation rate (data_free_pct) is less than 20%, unless the disk space is very tight and the logs are basically cleared.

Regarding the issue of reclaiming space

For some log tables or tables with regional characteristics, it is recommended to use MySQL partition tables to manage them. When a batch of data needs to be cleaned up, partition truncate can be used to clean it up, and the disk space can be directly released.

The above is the detailed content of the concept of MySQL table space fragmentation and the solution of related problems. For more information about MySQL table space fragmentation, please pay attention to other related articles on 123WORDPRESS.COM!