Technology Sharing | MySQL table space defragmentation method

Author: Yao Yuan

MySQL ACE, Huawei Cloud MVP, focused on Oracle, MySQL database for many years, Oracle 10G and 12C OCM, MySQL 5.6, 5.7, 8.0 OCP. Now Dingjia Technology serves as a technical consultant, providing database training and technical support services for colleagues and customers.

Source of this article: original submission

* Produced by Aikesheng open source community, original content is not allowed to be used without authorization, please contact the editor and indicate the source for reprinting.

---

After multiple delete, update, and insert of MySQL tables, the table space will be fragmented. Regular table space arranging to eliminate fragmentation can improve the performance of accessing table space.

Check table space fragmentation

The following experiment is used to verify the impact on performance after the table space is organized. First, check the size of the table with 1 million records:

mysql> analyze table sbtest1;
+----------------+---------+----------+-----------------------------+
| Table          | Op      | Msg_type | Msg_text                    |
+----------------+---------+----------+-----------------------------+
| sbtest.sbtest1 | analyze | status   | Table is already up to date |
+----------------+---------+----------+-----------------------------+
1 row in set (0.06 sec)

mysql> show table status like 'sbtest1'\G
*************************** 1. row ***************************
           Name: sbtest1
         Engine: MyISAM
        Version: 10
     Row_format: Fixed
           Rows: 1000000
 Avg_row_length: 729
    Data_length: 729000000
Max_data_length: 205195258022068223
   Index_length: 20457472
      Data_free: 0
 Auto_increment: 1000001
    Create_time: 2021-05-31 18:54:22
    Update_time: 2021-05-31 18:54:43
     Check_time: 2021-05-31 18:55:05
      Collation: utf8mb4_0900_ai_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

mysql> system ls -l /var/lib/mysql/sbtest/sbtest1.*
-rw-r----- 1 mysql mysql 729000000 May 31 08:24 /var/lib/mysql/sbtest/sbtest1.MYD
-rw-r----- 1 mysql mysql  20457472 May 31 08:25 /var/lib/mysql/sbtest/sbtest1.MYI

The command show table status is consistent with the size of the data file seen from the OS layer, and the Data_free is zero at this time.

Delete two-thirds of the records in this table:

mysql> delete from sbtest1 where id%3<>0;
Query OK, 666667 rows affected (51.72 sec)

Check the status of the table after re-collecting the statistics of this table:

mysql> analyze table sbtest1;
+----------------+---------+----------+----------+
| Table          | Op      | Msg_type | Msg_text |
+----------------+---------+----------+----------+
| sbtest.sbtest1 | analyze | status   | OK       |
+----------------+---------+----------+----------+
1 row in set (0.13 sec)

mysql> show table status like 'sbtest1'\G
*************************** 1. row ***************************
           Name: sbtest1
         Engine: MyISAM
        Version: 10
     Row_format: Fixed
           Rows: 333333
 Avg_row_length: 729
    Data_length: 729000000
Max_data_length: 205195258022068223
   Index_length: 20457472
      Data_free: 486000243
 Auto_increment: 1000001
    Create_time: 2021-05-31 18:54:22
    Update_time: 2021-05-31 19:03:59
     Check_time: 2021-05-31 18:55:05
      Collation: utf8mb4_0900_ai_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.01 sec)

mysql> select 486000243/729000000;
+---------------------+
| 486000243/729000000 |
+---------------------+
|              0.6667 |
+---------------------+
1 row in set (0.00 sec)

mysql> system ls -l /var/lib/mysql/sbtest/sbtest1.*
-rw-r----- 1 mysql mysql 729000000 May 31 08:33 /var/lib/mysql/sbtest/sbtest1.MYD
-rw-r----- 1 mysql mysql  20457472 May 31 08:34 /var/lib/mysql/sbtest/sbtest1.MYI

It is found that two-thirds of the records in this table have been deleted, but the size of the data file is still the same as before. Because the deleted records are only marked as deleted, the storage space they occupy is not released.

Perform a full table scan to see the performance:

mysql> select count(*) from sbtest1 where c<>'aaa';
+----------+
| count(*) |
+----------+
|   333333 |
+----------+
1 row in set (0.82 sec)

It was found that this full table scan SQL took 0.82 seconds. Check the last_statement_latency in the sys.session view to see the same time.

Organize table space and improve performance

To organize the table space:

mysql> alter table sbtest1 force;
Query OK, 333333 rows affected (10.73 sec)
Records: 333333  Duplicates: 0  Warnings: 0

mysql> analyze table sbtest1;
+----------------+---------+----------+-----------------------------+
| Table          | Op      | Msg_type | Msg_text                    |
+----------------+---------+----------+-----------------------------+
| sbtest.sbtest1 | analyze | status   | Table is already up to date |
+----------------+---------+----------+-----------------------------+
1 row in set (0.04 sec)

mysql> show table status like 'sbtest1'\G
*************************** 1. row ***************************
           Name: sbtest1
         Engine: MyISAM
        Version: 10
     Row_format: Fixed
           Rows: 333333
 Avg_row_length: 729
    Data_length: 242999757
Max_data_length: 205195258022068223
   Index_length: 6820864
      Data_free: 0
 Auto_increment: 1000001
    Create_time: 2021-05-31 19:10:35
    Update_time: 2021-05-31 19:10:41
     Check_time: 2021-05-31 19:10:45
      Collation: utf8mb4_0900_ai_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.48 sec)

mysql> system ls -l /var/lib/mysql/sbtest/sbtest1.*
-rw-r----- 1 mysql mysql 242999757 May 31 08:40 /var/lib/mysql/sbtest/sbtest1.MYD
-rw-r----- 1 mysql mysql   6820864 May 31 08:40 /var/lib/mysql/sbtest/sbtest1.MYI

After arranging, the hard disk space occupies one third of the original space, Data_free becomes zero again, and the hard disk space of the deleted records is released.

Execute the SQL statement of the full table scan again:

mysql> select count(*) from sbtest1 where c<>'aaa';
+----------+
| count(*) |
+----------+
|   333333 |
+----------+
1 row in set (0.29 sec)

It was found that the execution speed was also increased to approximately three times the original. MyISAM table is used here for testing. If you use InnoDB table, the speed improvement is not so obvious, because InnoDB data will be cached in InnoDB cache, MyISAM table data MySQL will not be cached, OS may cache, so you need to get accurate For the test results, use the following command to release the cache of the system before each test on the Linux system:

# echo 3 > /proc/sys/vm/drop_caches

The use of alter table force to organize the table space has the same effect as the OPTIMIZE TABLE command. This command is applicable to the tables of InnoDB, MyISAM and ARCHIVE engines. But for InnoDB tables, OPTIMIZE TABLE command is not supported, you can use alter table sbtest1 engine=innodb instead, for example:

mysql> OPTIMIZE TABLE sbtest2;
+----------------+----------+----------+-------------------------------------------------------------------+
| Table          | Op       | Msg_type | Msg_text                                                          |
+----------------+----------+----------+-------------------------------------------------------------------+
| sbtest.sbtest2 | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| sbtest.sbtest2 | optimize | status   | OK                                                                |
+----------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (1 min 25.24 sec)

mysql> alter table sbtest2 engine=innodb;
Query OK, 0 rows affected (1 min 3.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

Use mysqlcheck for batch tablespace optimization

The following command can find the largest 10 tables in the table space whose free space exceeds 10M:

mysql> select table_name,round(data_length/1024/1024) as data_length_mb,  round(data_free/1024/1024) as data_free_mb   
from information_schema.tables   where round(data_free/1024/1024) > 10  order by data_free_mb desc limit 10;
+------------+----------------+--------------+
| TABLE_NAME | data_length_mb | data_free_mb |
+------------+----------------+--------------+
| sbtest2    |            232 |          174 |
+------------+----------------+--------------+
1 row in set (0.02 sec)

You can use the -o option of MySQL's own tool mysqlcheck to optimize the table space. This tool is suitable for batch processing in scripts and can be called by crontab in Linux or scheduled tasks in Windows.

An example of table space optimization for a single table is as follows:

$ mysqlcheck -o sbtest sbtest1

You can also use the following command to optimize the table space of all tables in a database:

$ mysqlcheck -o sbtest

You can also optimize the table space for all databases in the entire instance:

$ mysqlcheck -o --all-databases