MySQL table defragmentation

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)

Keywords: MySQL Fragment

Added by fert on Sun, 05 Jan 2020 02:11:37 +0200