DDL Tool Test Comparison
Test Procedure
- 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;
- Insert 30 million rows of data into each table.
- 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.
- 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.
- 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.
- Record the execution durations of DDL and DML statements.
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
- MySQL COPY: The UPDATE and INSERT statements are blocked, but the SELECT statement is executed properly.
- MySQL INPLACE: DML statements are not blocked for a long time, and adding a column to a large table takes the shortest time.
- 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.
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot