Updated on 2025-05-06 GMT+08:00

DDL Tool Test Comparison

Test Procedure

  1. Create four tables. The table structures are as follows:
    CREATE TABLE if not exists users
    	(
    	 `rid` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    	  `nid` bigint(20) DEFAULT NULL,
    	  `level` int(11) DEFAULT NULL,
    	  `vip` int(11) DEFAULT NULL,
    	  `vip_exp` int(11) DEFAULT NULL,
    	  `reg_channel` int(11) DEFAULT NULL,
    	  `guild_id` bigint(20) unsigned DEFAULT '0',
    	  `guild_open` tinyint(1) DEFAULT '0',
    	  `forbid_login_time` bigint(20) DEFAULT NULL,
    	  `forbid_talk_time` bigint(20) DEFAULT NULL,
    	  `ctime` bigint(20) DEFAULT NULL,
    	  `mtime` datetime(3) DEFAULT NULL,
    	  `last_offline_time` bigint(20) DEFAULT NULL,
    	  `friend_open` tinyint(1) DEFAULT '0',
    	  `user_data_str` mediumblob,
    	  `name` varchar(64) DEFAULT NULL,
    	  `db_fix_version` int(10) DEFAULT '0',
    	  PRIMARY KEY (`rid`),
    	  KEY `idx_users_99_nid` (`nid`),
    	  KEY `idx_users_99_level` (`level`),
    	  KEY `idx_users_99_ctime` (`ctime`),
    	  KEY `idx_users_99_mtime` (`mtime`),
    	  KEY `idx_users_99_last_offline_time` (`last_offline_time`),
    	  KEY `idx_users_99_name` (`name`)
    	) ENGINE=InnoDB AUTO_INCREMENT=4393751571200 DEFAULT CHARSET=utf8mb4;
  2. Insert 30 million rows of data into each table.
  3. Use the MySQL native COPY algorithm to add a column to table 1. During the execution, create a new session and perform the SELECT, UPDATE, and INSERT operations on 100,000 data records.
  4. Use the MySQL native INPLACE algorithm to add a column to table 2. During the execution, create a new session and perform the SELECT, UPDATE, and INSERT operations on 100,000 data records.
  5. Use gh-ost to add a column to table 3. During the execution, create a new session and perform the SELECT, UPDATE, and INSERT operations on 100,000 data records.
  6. Record the execution durations of DDL and DML statements.
Table 1 Test data (unit: s)

Operation

MySQL COPY

MySQL INPLACE

gh-ost

Adding a column

1294.29

755.52

1876.79

SELECT

1.35

1.29

1.29

UPDATE

1266.78

0.19

0.11

INSERT

1296.19

7.47

4.49

Test Results

  1. MySQL COPY: The UPDATE and INSERT statements are blocked, but the SELECT statement is executed properly.
  2. MySQL INPLACE: DML statements are not blocked for a long time, and adding a column to a large table takes the shortest time.
  3. gh-ost: It almost does not block DML statements. It takes a longer time to add a column than the two native MySQL algorithms.

Suggestions

INPLACE blocks DML operations only for a short time while performing DDL operations. If you have no strict requirements on the primary/standby replication delay, you are advised to use this algorithm to quickly change the table structure. If your workload is sensitive to primary/standby replication delay, gh-ost is recommended. If you use MySQL 8.0.12 or later and the INSTANT algorithm conditions are met, you can use INSTANT to minimize the impact on workloads.