MySQL table defragmentation
1. Calculate fragment size
To defragment, you first need to understand the calculation method of fragments.
You can view it with the command show table [from|in db|name] status like '% table|name%':
mysql> show table from employees status like 't1'\G *************************** 1. row *************************** Name: t1 Engine: InnoDB Version: 10 Row_format: Dynamic Rows: 1176484 Avg_row_length: 86 Data_length: 101842944 Max_data_length: 0 Index_length: 0 Data_free: 39845888 Auto_increment: NULL Create_time: 2018-08-28 13:40:19 Update_time: 2018-08-28 13:50:43 Check_time: NULL Collation: utf8mb4_general_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec)
Fragment size = total data size - actual tablespace file size
-
Total data size = data length + data length = 101842944
-
Actual tablespace file size = rows * AVG? Row? Length = 1176484 * 86 = 101177624
-
Fragment size = (101842944 - 101177624) / 1024 /1024 = 0.63MB
Check whether there are fragments in the table through the data free column of information schema.tables:
SELECT t.TABLE_SCHEMA, t.TABLE_NAME, t.TABLE_ROWS, t.DATA_LENGTH, t.INDEX_LENGTH, concat(round(t.DATA_FREE / 1024 / 1024, 2), 'M') AS datafree FROM information_schema.tables t WHERE t.TABLE_SCHEMA = 'employees' +--------------+--------------+------------+-------------+--------------+----------+ | TABLE_SCHEMA | TABLE_NAME | TABLE_ROWS | DATA_LENGTH | INDEX_LENGTH | datafree | +--------------+--------------+------------+-------------+--------------+----------+ | employees | departments | 9 | 16384 | 16384 | 0.00M | | employees | dept_emp | 331143 | 12075008 | 11567104 | 0.00M | | employees | dept_manager | 24 | 16384 | 32768 | 0.00M | | employees | employees | 299335 | 15220736 | 0 | 0.00M | | employees | salaries | 2838426 | 100270080 | 36241408 | 5.00M | | employees | t1 | 1191784 | 48824320 | 17317888 | 5.00M | | employees | titles | 442902 | 20512768 | 11059200 | 0.00M | | employees | ttt | 2 | 16384 | 0 | 0.00M | +--------------+--------------+------------+-------------+--------------+----------+ 8 rows in set (0.00 sec)
2. Defragment
2.1 use the alter table table_name engine = innodb command to organize.
root@localhost [employees] 14:27:01> alter table t1 engine=innodb; Query OK, 0 rows affected (5.69 sec) Records: 0 Duplicates: 0 Warnings: 0 root@localhost [employees] 14:27:15> show table status like 't1'\G *************************** 1. row *************************** Name: t1 Engine: InnoDB Version: 10 Row_format: Dynamic Rows: 1191062 Avg_row_length: 48 Data_length: 57229312 Max_data_length: 0 Index_length: 0 Data_free: 2097152 Auto_increment: NULL Create_time: 2018-08-28 14:27:15 Update_time: NULL Check_time: NULL Collation: utf8mb4_general_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec)
2.2 the PT online schema change tool can also be used to organize table structure and collect fragments online.
[root@mysqldb1 14:29:29 /root] # pt-online-schema-change --alter="ENGINE=innodb" D=employees,t=t1 --execute Cannot chunk the original table `employees`.`t1`: There is no good index and the table is oversized. at /opt/percona-toolkit-3.0.11/bin/pt-online-schema-change line 5852.
You need a primary key or unique index on the table to run
[root@mysqldb1 14:31:16 /root] # pt-online-schema-change --alter='engine=innodb' D=employees,t=salaries --execute No slaves found. See --recursion-method if host mysqldb1 has slaves. Not checking slave lag because no slaves were found and --check-slave-lag was not specified. Operation, tries, wait: analyze_table, 10, 1 copy_rows, 10, 0.25 create_triggers, 10, 1 drop_triggers, 10, 1 swap_tables, 10, 1 update_foreign_keys, 10, 1 Altering `employees`.`salaries`... Creating new table... Created new table employees._salaries_new OK. Altering new table... Altered `employees`.`_salaries_new` OK. 2018-08-28T14:37:01 Creating triggers... 2018-08-28T14:37:01 Created triggers OK. 2018-08-28T14:37:01 Copying approximately 2838426 rows... Copying `employees`.`salaries`: 74% 00:10 remain 2018-08-28T14:37:41 Copied rows OK. 2018-08-28T14:37:41 Analyzing new table... 2018-08-28T14:37:42 Swapping tables... 2018-08-28T14:37:42 Swapped original and new tables OK. 2018-08-28T14:37:42 Dropping old table... 2018-08-28T14:37:42 Dropped old table `employees`.`_salaries_old` OK. 2018-08-28T14:37:42 Dropping triggers... 2018-08-28T14:37:42 Dropped triggers OK. Successfully altered `employees`.`salaries`.
2.3 use the optimize table command to defragment.
Run OPTIMIZE TABLE, InnoDB creates a new. ibd file with a temporary name, using only the space required for the actual data stored. When the optimization is complete, InnoDB deletes the old. ibd file and replaces it with a new one. If the previous. ibd file grows significantly but the actual data is only a part of its size, running OPTIMIZE TABLE reclaims unused space.
mysql>optimize table account; +--------------+----------+----------+-------------------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +--------------+----------+----------+-------------------------------------------------------------------+ | test.account | optimize | note | Table does not support optimize, doing recreate + analyze instead | | test.account | optimize | status | OK | +--------------+----------+----------+-------------------------------------------------------------------+ 2 rows in set (0.09 sec)