一 背景
我们知道在线DDL一定程度上会影响DML.为了减小这种影响.现在可供我们选择的工具一般就是percona的pt-osc或者GitHub的gh-ost或者使用MySQL的online DDL功能,当然如果使用MySQL自带的online DDL功能.要求你的版本最少是5.7以上了.那么接下来我们对这三种方式进行压测.看看他们的表现吧.
二 压测场景介绍
使用sysbench在主库执行update200万的表,同时在另一个session执行DDL语句.通过对比DDL语句的执行时间及sysbench的tps,qps对比三种方式的优劣.
三 压测加列的表现
3.1 online DDL表现:
语句:
mysql> alter table sbtest1 add column name varchar(20), ALGORITHM=INPLACE, LOCK=NONE;
Query OK, 0 rows affected (46.94 sec)
Records: 0 Duplicates: 0 Warnings: 0
可以看到online DDL执行了46秒使用online DDL期间qps及tps查看:
[root@slowquery ~]# sysbench /usr/local/share/sysbench/oltp_update_index.lua --mysql-host=localhost --mysql-port=3306 --mysql-user=root --mysql-password=123 --mysql-db=increment --table_size=2000000 --tables=1 run
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 1
Initializing random number generator from current time
Initializing worker threads...
Threads started!
SQL statistics:
queries performed:
read: 0
write: 1335
other: 0
total: 1335
transactions: 1335 (133.41 per sec.)
queries: 1335 (133.41 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 10.0046s
total number of events: 1335
Latency (ms):
min: 2.64
avg: 7.49
max: 57.16
95th percentile: 20.37
sum: 9999.12
Threads fairness:
events (avg/stddev): 1335.0000/0.00
execution time (avg/stddev): 9.9991/0.00
可以发现使用inplace方式的online DDL期间的tps和qps为133
3.2 gh-ost表现
[root@slowquery ~]# gh-ost --max-load=Threads_running=25 --critical-load=Threads_running=100 --chunk-size=10000 --throttle-control-replicas="10.2.4.178:3307" --max-lag-millis=1500 --user="ghost" --password="123" --host=10.2.4.178 --port=3307 --database="increment" --table="sbtest1" --verbose --alter='add column name varchar(20)' --switch-to-rbr --allow-on-master --cut-over=default --exact-rowcount --concurrent-rowcount --default-retries=120 --initially-drop-ghost-table --initially-drop-old-table --ok-to-drop-table --execute
2021-07-27 17:05:21 INFO starting gh-ost 1.0.49
2021-07-27 17:05:21 INFO Migrating `increment`.`sbtest1`
2021-07-27 17:05:21 INFO connection validated on 10.2.4.178:3307
2021-07-27 17:05:21 INFO User has ALL privileges
2021-07-27 17:05:21 INFO binary logs validated on 10.2.4.178:3307
2021-07-27 17:05:21 INFO Restarting replication on 10.2.4.178:3307 to make sure binlog settings apply to replication thread
2021-07-27 17:05:21 INFO Inspector initiated on slowquery:3307, version 5.7.28-log
2021-07-27 17:05:21 INFO Table found. Engine=InnoDB
2021-07-27 17:05:21 INFO Estimated number of rows via EXPLAIN: 1972656
2021-07-27 17:05:21 INFO Recursively searching for replication master
2021-07-27 17:05:21 INFO Master found to be 10.2.4.178:3306
2021-07-27 17:05:21 INFO log_slave_updates validated on 10.2.4.178:3307
2021-07-27 17:05:21 INFO connection validated on 10.2.4.178:3307
2021-07-27 17:05:21 INFO Connecting binlog streamer at backups.000018:382749546
[2021/07/27 17:05:21] [info] binlogsyncer.go:133 create BinlogSyncer with config {99999 mysql 10.2.4.178 3307 ghost false false <nil> false UTC true 0 0s 0s 0 false}
[2021/07/27 17:05:21] [info] binlogsyncer.go:354 begin to sync binlog from position (backups.000018, 382749546)
[2021/07/27 17:05:21] [info] binlogsyncer.go:203 register slave for master server 10.2.4.178:3307
[2021/07/27 17:05:21] [info] binlogsyncer.go:723 rotate to (backups.000018, 382749546)
2021-07-27 17:05:21 INFO rotate to next log from backups.000018:0 to backups.000018
2021-07-27 17:05:21 INFO connection validated on 10.2.4.178:3306
2021-07-27 17:05:21 INFO connection validated on 10.2.4.178:3306
2021-07-27 17:05:21 INFO will use time_zone='SYSTEM' on applier
2021-07-27 17:05:21 INFO Examining table structure on applier
2021-07-27 17:05:21 INFO Applier initiated on slowquery:3306, version 5.7.28-log
2021-07-27 17:05:21 INFO Dropping table `increment`.`_sbtest1_gho`
2021-07-27 17:05:21 INFO Table dropped
2021-07-27 17:05:21 INFO Dropping table `increment`.`_sbtest1_del`
2021-07-27 17:05:21 INFO Table dropped
2021-07-27 17:05:21 INFO Dropping table `increment`.`_sbtest1_ghc`
2021-07-27 17:05:21 INFO Table dropped
2021-07-27 17:05:21 INFO Creating changelog table `increment`.`_sbtest1_ghc`
2021-07-27 17:05:21 INFO Changelog table created
2021-07-27 17:05:21 INFO Creating ghost table `increment`.`_sbtest1_gho`
2021-07-27 17:05:21 INFO Ghost table created
2021-07-27 17:05:21 INFO Altering ghost table `increment`.`_sbtest1_gho`
2021-07-27 17:05:21 INFO Ghost table altered
2021-07-27 17:05:21 INFO Waiting for ghost table to be migrated. Current lag is 0s
2021-07-27 17:05:22 INFO Intercepted changelog state GhostTableMigrated
2021-07-27 17:05:22 INFO Handled changelog state GhostTableMigrated
2021-07-27 17:05:22 INFO Chosen shared unique key is PRIMARY
2021-07-27 17:05:22 INFO Shared columns are id,k,c,pad,firstname,dtname
2021-07-27 17:05:22 INFO Listening on unix socket file: /tmp/gh-ost.increment.sbtest1.sock
2021-07-27 17:05:22 INFO As instructed, counting rows in the background; meanwhile I will use an estimated count, and will update it later on
2021-07-27 17:05:22 INFO As instructed, I'm issuing a SELECT COUNT(*) on the table. This may take a while
2021-07-27 17:05:22 INFO Migration min values: [1]
2021-07-27 17:05:22 INFO Migration max values: [2000000]
2021-07-27 17:05:22 INFO Waiting for first throttle metrics to be collected
2021-07-27 17:05:22 INFO First throttle metrics collected
# Migrating `increment`.`sbtest1`; Ghost table is `increment`.`_sbtest1_gho`
# Migrating slowquery:3306; inspecting slowquery:3307; executing on slowquery
# Migration started at Tue Jul 27 17:05:21 +0800 2021
# chunk-size: 10000; max-lag-millis: 1500ms; dml-batch-size: 10; max-load: Threads_running=25; critical-load: Threads_running=100; nice-ratio: 0.000000
# throttle-additional-flag-file: /tmp/gh-ost.throttle
# throttle-control-replicas count: 1
# Serving on unix socket: /tmp/gh-ost.increment.sbtest1.sock
Copy: 0/1972656 0.0%; Applied: 0; Backlog: 0/1000; Time: 0s(total), 0s(copy); streamer: backups.000018:382809103; Lag: 0.32s, State: migrating; ETA: N/A
Copy: 40000/1972656 2.0%; Applied: 126; Backlog: 0/1000; Time: 2s(total), 1s(copy); streamer: backups.000018:382891984; Lag: 0.71s, State: migrating; ETA: 48s
Copy: 100000/1972656 5.1%; Applied: 208; Backlog: 0/1000; Time: 3s(total), 2s(copy); streamer: backups.000018:388641630; Lag: 1.22s, State: migrating; ETA: 37s
2021-07-27 17:05:24 INFO Exact number of rows via COUNT: 2000000
Copy: 150000/2000000 7.5%; Applied: 284; Backlog: 8/1000; Time: 4s(total), 3s(copy); streamer: backups.000018:396292561; Lag: 1.41s, State: migrating; ETA: 37s
Copy: 180000/2000000 9.0%; Applied: 311; Backlog: 70/1000; Time: 5s(total), 4s(copy); streamer: backups.000018:403946317; Lag: 1.71s, State: throttled, lag=1.714667s; ETA: 40s
Copy: 180000/2000000 9.0%; Applied: 311; Backlog: 174/1000; Time: 6s(total), 5s(copy); streamer: backups.000018:411609220; Lag: 2.01s, State: throttled, lag=2.014571s; ETA: 50s
Copy: 180000/2000000 9.0%; Applied: 311; Backlog: 282/1000; Time: 7s(total), 6s(copy); streamer: backups.000018:417380022; Lag: 2.31s, State: throttled, lag=2.314590s; ETA: 1m0s
Copy: 180000/2000000 9.0%; Applied: 311; Backlog: 444/1000; Time: 8s(total), 7s(copy); streamer: backups.000018:417491638; Lag: 2.61s, State: throttled, lag=2.514669s; ETA: 1m10s
Copy: 180000/2000000 9.0%; Applied: 311; Backlog: 675/1000; Time: 9s(total), 8s(copy); streamer: backups.000018:417652420; Lag: 2.31s, State: throttled, lag=2.414704s; ETA: 1m20s
Copy: 180000/2000000 9.0%; Applied: 311; Backlog: 927/1000; Time: 10s(total), 9s(copy); streamer: backups.000018:417828852; Lag: 1.91s, State: throttled, lag=2.014705s; ETA: 1m31s
Copy: 180000/2000000 9.0%; Applied: 311; Backlog: 1000/1000; Time: 11s(total), 10s(copy); streamer: backups.000018:417880663; Lag: 0.01s, State: throttled, 10.2.4.178:3307 replica-lag=1.912530s; ETA: 1m41s
Copy: 210000/2000000 10.5%; Applied: 1319; Backlog: 0/1000; Time: 12s(total), 11s(copy); streamer: backups.000018:421709758; Lag: 0.21s, State: migrating; ETA: 1m33s
Copy: 250000/2000000 12.5%; Applied: 1319; Backlog: 0/1000; Time: 13s(total), 12s(copy); streamer: backups.000018:431206774; Lag: 0.12s, State: migrating; ETA: 1m24s
Copy: 300000/2000000 15.0%; Applied: 1319; Backlog: 0/1000; Time: 14s(total), 13s(copy); streamer: backups.000018:440703025; Lag: 0.11s, State: migrating; ETA: 1m13s
Copy: 350000/2000000 17.5%; Applied: 1319; Backlog: 0/1000; Time: 15s(total), 14s(copy); streamer: backups.000018:448300501; Lag: 0.11s, State: migrating; ETA: 1m6s
Copy: 390000/2000000 19.5%; Applied: 1319; Backlog: 0/1000; Time: 16s(total), 15s(copy); streamer: backups.000018:457796754; Lag: 0.11s, State: migrating; ETA: 1m1s
Copy: 440000/2000000 22.0%; Applied: 1319; Backlog: 0/1000; Time: 17s(total), 16s(copy); streamer: backups.000018:465394615; Lag: 0.12s, State: migrating; ETA: 56s
Copy: 490000/2000000 24.5%; Applied: 1319; Backlog: 0/1000; Time: 18s(total), 17s(copy); streamer: backups.000018:472991711; Lag: 0.31s, State: migrating; ETA: 52s
Copy: 530000/2000000 26.5%; Applied: 1319; Backlog: 0/1000; Time: 19s(total), 18s(copy); streamer: backups.000018:482488346; Lag: 0.21s, State: migrating; ETA: 49s
Copy: 570000/2000000 28.5%; Applied: 1319; Backlog: 0/1000; Time: 20s(total), 19s(copy); streamer: backups.000018:490086208; Lag: 0.21s, State: migrating; ETA: 47s
Copy: 610000/2000000 30.5%; Applied: 1319; Backlog: 0/1000; Time: 21s(total), 20s(copy); streamer: backups.000018:499583226; Lag: 0.01s, State: migrating; ETA: 45s
Copy: 660000/2000000 33.0%; Applied: 1319; Backlog: 0/1000; Time: 22s(total), 21s(copy); streamer: backups.000018:507180320; Lag: 0.21s, State: migrating; ETA: 42s
Copy: 710000/2000000 35.5%; Applied: 1319; Backlog: 0/1000; Time: 23s(total), 22s(copy); streamer: backups.000018:516676570; Lag: 0.21s, State: migrating; ETA: 39s
Copy: 760000/2000000 38.0%; Applied: 1319; Backlog: 0/1000; Time: 24s(total), 23s(copy); streamer: backups.000018:526173203; Lag: 0.11s, State: migrating; ETA: 37s
Copy: 800000/2000000 40.0%; Applied: 1319; Backlog: 0/1000; Time: 25s(total), 24s(copy); streamer: backups.000018:531871522; Lag: 0.41s, State: migrating; ETA: 36s
[2021/07/27 17:05:46] [info] binlogsyncer.go:723 rotate to (backups.000019, 4)
[2021/07/27 17:05:46] [info] binlogsyncer.go:723 rotate to (backups.000019, 4)
2021-07-27 17:05:46 INFO rotate to next log from backups.000019:537569120 to backups.000019
2021-07-27 17:05:46 INFO rotate to next log from backups.000019:0 to backups.000019
Copy: 850000/2000000 42.5%; Applied: 1319; Backlog: 0/1000; Time: 26s(total), 25s(copy); streamer: backups.000019:1900537; Lag: 0.41s, State: migrating; ETA: 33s
Copy: 880000/2000000 44.0%; Applied: 1319; Backlog: 0/1000; Time: 27s(total), 26s(copy); streamer: backups.000019:11397547; Lag: 0.21s, State: migrating; ETA: 33s
Copy: 920000/2000000 46.0%; Applied: 1319; Backlog: 0/1000; Time: 28s(total), 27s(copy); streamer: backups.000019:18995789; Lag: 0.11s, State: migrating; ETA: 31s
Copy: 960000/2000000 48.0%; Applied: 1319; Backlog: 0/1000; Time: 29s(total), 28s(copy); streamer: backups.000019:26593645; Lag: 0.11s, State: migrating; ETA: 30s
Copy: 1010000/2000000 50.5%; Applied: 1319; Backlog: 0/1000; Time: 30s(total), 29s(copy); streamer: backups.000019:35415243; Lag: 0.12s, State: migrating; ETA: 28s
Copy: 1050000/2000000 52.5%; Applied: 1319; Backlog: 0/1000; Time: 31s(total), 30s(copy); streamer: backups.000019:43687373; Lag: 0.21s, State: migrating; ETA: 27s
Copy: 1100000/2000000 55.0%; Applied: 1319; Backlog: 0/1000; Time: 32s(total), 31s(copy); streamer: backups.000019:53184005; Lag: 0.11s, State: migrating; ETA: 25s
Copy: 1140000/2000000 57.0%; Applied: 1319; Backlog: 0/1000; Time: 33s(total), 32s(copy); streamer: backups.000019:60781485; Lag: 0.22s, State: migrating; ETA: 24s
Copy: 1190000/2000000 59.5%; Applied: 1319; Backlog: 0/1000; Time: 34s(total), 33s(copy); streamer: backups.000019:68379342; Lag: 0.21s, State: migrating; ETA: 22s
Copy: 1230000/2000000 61.5%; Applied: 1319; Backlog: 0/1000; Time: 35s(total), 34s(copy); streamer: backups.000019:75977201; Lag: 0.22s, State: migrating; ETA: 21s
Copy: 1270000/2000000 63.5%; Applied: 1319; Backlog: 0/1000; Time: 36s(total), 35s(copy); streamer: backups.000019:85473450; Lag: 0.21s, State: migrating; ETA: 20s
Copy: 1310000/2000000 65.5%; Applied: 1319; Backlog: 0/1000; Time: 37s(total), 36s(copy); streamer: backups.000019:89461314; Lag: 0.42s, State: migrating; ETA: 18s
Copy: 1350000/2000000 67.5%; Applied: 1319; Backlog: 0/1000; Time: 38s(total), 37s(copy); streamer: backups.000019:98770018; Lag: 0.31s, State: migrating; ETA: 17s
Copy: 1390000/2000000 69.5%; Applied: 1319; Backlog: 0/1000; Time: 39s(total), 38s(copy); streamer: backups.000019:108266650; Lag: 0.21s, State: migrating; ETA: 16s
Copy: 1440000/2000000 72.0%; Applied: 1319; Backlog: 0/1000; Time: 40s(total), 39s(copy); streamer: backups.000019:115864127; Lag: 0.31s, State: migrating; ETA: 15s
Copy: 1470000/2000000 73.5%; Applied: 1319; Backlog: 0/1000; Time: 41s(total), 40s(copy); streamer: backups.000019:123462374; Lag: 0.22s, State: migrating; ETA: 14s
Copy: 1510000/2000000 75.5%; Applied: 1319; Backlog: 0/1000; Time: 42s(total), 41s(copy); streamer: backups.000019:131060234; Lag: 0.21s, State: migrating; ETA: 13s
Copy: 1560000/2000000 78.0%; Applied: 1319; Backlog: 0/1000; Time: 43s(total), 42s(copy); streamer: backups.000019:138658098; Lag: 0.21s, State: migrating; ETA: 11s
Copy: 1600000/2000000 80.0%; Applied: 1319; Backlog: 0/1000; Time: 44s(total), 43s(copy); streamer: backups.000019:148154731; Lag: 0.11s, State: migrating; ETA: 10s
Copy: 1640000/2000000 82.0%; Applied: 1319; Backlog: 0/1000; Time: 45s(total), 44s(copy); streamer: backups.000019:155752208; Lag: 0.21s, State: migrating; ETA: 9s
Copy: 1680000/2000000 84.0%; Applied: 1319; Backlog: 0/1000; Time: 46s(total), 45s(copy); streamer: backups.000019:161450915; Lag: 0.41s, State: migrating; ETA: 8s
Copy: 1720000/2000000 86.0%; Applied: 1319; Backlog: 0/1000; Time: 47s(total), 46s(copy); streamer: backups.000019:169048393; Lag: 0.41s, State: migrating; ETA: 7s
Copy: 1750000/2000000 87.5%; Applied: 1319; Backlog: 0/1000; Time: 48s(total), 47s(copy); streamer: backups.000019:176647016; Lag: 0.22s, State: migrating; ETA: 6s
Copy: 1790000/2000000 89.5%; Applied: 1319; Backlog: 0/1000; Time: 49s(total), 48s(copy); streamer: backups.000019:184244877; Lag: 0.21s, State: migrating; ETA: 5s
Copy: 1840000/2000000 92.0%; Applied: 1319; Backlog: 0/1000; Time: 50s(total), 49s(copy); streamer: backups.000019:191842351; Lag: 0.31s, State: migrating; ETA: 4s
Copy: 1880000/2000000 94.0%; Applied: 1319; Backlog: 0/1000; Time: 51s(total), 50s(copy); streamer: backups.000019:201338985; Lag: 0.21s, State: migrating; ETA: 3s
Copy: 1920000/2000000 96.0%; Applied: 1319; Backlog: 0/1000; Time: 52s(total), 51s(copy); streamer: backups.000019:208936848; Lag: 0.21s, State: migrating; ETA: 2s
Copy: 1960000/2000000 98.0%; Applied: 1319; Backlog: 0/1000; Time: 53s(total), 52s(copy); streamer: backups.000019:215860053; Lag: 0.32s, State: migrating; ETA: 1s
2021-07-27 17:06:14 INFO Row copy complete
Copy: 2000000/2000000 100.0%; Applied: 1319; Backlog: 0/1000; Time: 53s(total), 52s(copy); streamer: backups.000019:222233406; Lag: 0.21s, State: migrating; ETA: due
2021-07-27 17:06:14 INFO Grabbing voluntary lock: gh-ost.1397.lock
2021-07-27 17:06:14 INFO Setting LOCK timeout as 6 seconds
2021-07-27 17:06:14 INFO Looking for magic cut-over table
2021-07-27 17:06:14 INFO Creating magic cut-over table `increment`.`_sbtest1_del`
2021-07-27 17:06:14 INFO Magic cut-over table created
2021-07-27 17:06:14 INFO Locking `increment`.`sbtest1`, `increment`.`_sbtest1_del`
2021-07-27 17:06:14 INFO Tables locked
2021-07-27 17:06:14 INFO Session locking original & magic tables is 1397
2021-07-27 17:06:14 INFO Writing changelog state: AllEventsUpToLockProcessed:1627376774998971000
2021-07-27 17:06:15 INFO Waiting for events up to lock
Copy: 2000000/2000000 100.0%; Applied: 1319; Backlog: 0/1000; Time: 54s(total), 52s(copy); streamer: backups.000019:224132562; Lag: 0.21s, State: migrating; ETA: due
2021-07-27 17:06:15 INFO Intercepted changelog state AllEventsUpToLockProcessed
2021-07-27 17:06:15 INFO Handled changelog state AllEventsUpToLockProcessed
2021-07-27 17:06:15 INFO Waiting for events up to lock: got AllEventsUpToLockProcessed:1627376774998971000
2021-07-27 17:06:15 INFO Done waiting for events up to lock; duration=972.650659ms
# Migrating `increment`.`sbtest1`; Ghost table is `increment`.`_sbtest1_gho`
# Migrating slowquery:3306; inspecting slowquery:3307; executing on slowquery
# Migration started at Tue Jul 27 17:05:21 +0800 2021
# chunk-size: 10000; max-lag-millis: 1500ms; dml-batch-size: 10; max-load: Threads_running=25; critical-load: Threads_running=100; nice-ratio: 0.000000
# throttle-additional-flag-file: /tmp/gh-ost.throttle
# throttle-control-replicas count: 1
# Serving on unix socket: /tmp/gh-ost.increment.sbtest1.sock
Copy: 2000000/2000000 100.0%; Applied: 1319; Backlog: 0/1000; Time: 54s(total), 52s(copy); streamer: backups.000019:226036743; Lag: 0.01s, State: migrating; ETA: due
2021-07-27 17:06:15 INFO Setting RENAME timeout as 3 seconds
2021-07-27 17:06:15 INFO Session renaming tables is 1395
2021-07-27 17:06:15 INFO Issuing and expecting this to block: rename /* gh-ost */ table `increment`.`sbtest1` to `increment`.`_sbtest1_del`, `increment`.`_sbtest1_gho` to `increment`.`sbtest1`
2021-07-27 17:06:15 INFO Found atomic RENAME to be blocking, as expected. Double checking the lock is still in place (though I don't strictly have to)
2021-07-27 17:06:15 INFO Checking session lock: gh-ost.1397.lock
2021-07-27 17:06:15 INFO Connection holding lock on original table still exists
2021-07-27 17:06:15 INFO Will now proceed to drop magic table and unlock tables
2021-07-27 17:06:15 INFO Dropping magic cut-over table
2021-07-27 17:06:15 INFO Releasing lock from `increment`.`sbtest1`, `increment`.`_sbtest1_del`
2021-07-27 17:06:15 INFO Tables unlocked
2021-07-27 17:06:16 INFO Tables renamed
2021-07-27 17:06:16 INFO Lock & rename duration: 1.008119112s. During this time, queries on `sbtest1` were blocked
2021-07-27 17:06:16 INFO Looking for magic cut-over table
[2021/07/27 17:06:16] [info] binlogsyncer.go:164 syncer is closing...
2021-07-27 17:06:16 INFO Closed streamer connection. err=<nil>
2021-07-27 17:06:16 INFO Dropping table `increment`.`_sbtest1_ghc`
[2021/07/27 17:06:16] [error] binlogstreamer.go:77 close sync with err: sync is been closing...
[2021/07/27 17:06:16] [info] binlogsyncer.go:179 syncer is closed
2021-07-27 17:06:16 INFO Table dropped
2021-07-27 17:06:16 INFO Dropping table `increment`.`_sbtest1_del`
2021-07-27 17:06:16 ERROR Error 1146: Table 'increment._sbtest1_ghc' doesn't exist
2021-07-27 17:06:16 ERROR Error 1146: Table 'increment._sbtest1_ghc' doesn't exist
Copy: 2000000/2000000 100.0%; Applied: 1319; Backlog: 0/1000; Time: 55s(total), 52s(copy); streamer: backups.000019:226037642; Lag: 0.01s, State: migrating; ETA: due
2021-07-27 17:06:16 INFO Table dropped
2021-07-27 17:06:16 INFO Done migrating `increment`.`sbtest1`
2021-07-27 17:06:16 INFO Removing socket file: /tmp/gh-ost.increment.sbtest1.sock
2021-07-27 17:06:16 INFO Tearing down inspector
2021-07-27 17:06:16 INFO Tearing down applier
2021-07-27 17:06:16 INFO Tearing down streamer
2021-07-27 17:06:16 INFO Tearing down throttler
# Done
提取执行时间:
Copy: 2000000/2000000 100.0%; Applied: 1319; Backlog: 0/1000; Time: 55s(total), 52s(copy); streamer: backups.000019:226037642; Lag: 0.01s, State: migrating; ETA: due
2021-07-27 17:06:16 INFO Table dropped
可以发现gh-ost执行了55秒查看TPS和QPS
SQL statistics:
queries performed:
read: 0
write: 1804
other: 0
total: 1804
transactions: 1804 (180.31 per sec.)
queries: 1804 (180.31 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 10.0027s
total number of events: 1804
Latency (ms):
min: 2.48
avg: 5.54
max: 139.66
95th percentile: 11.87
sum: 9996.57
Threads fairness:
events (avg/stddev): 1804.0000/0.00
execution time (avg/stddev): 9.9966/0.00
可以发现使用gh-ost的TPS和QPS为180
3.3 pt-osc表现
[root@slowquery ~]# pt-online-schema-change --print --statistics --progress time,30 --preserve-triggers --user=root --password=123 --alter 'add column name varchar(20)' h=localhost,P=3306,D=increment,t=sbtest1 --pause-file=/tmp/ptosc.txt --max-load=threads_running=100,threads_connected=200 --critical-load=threads_running=1000 --chunk-size=10000 --alter-foreign-keys-method auto --execute
Cannot connect to P=3306,h=10.2.4.178,p=...,u=root
No slaves found. See --recursion-method if host slowquery 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
No foreign keys reference `increment`.`sbtest1`; ignoring --alter-foreign-keys-method.
Altering `increment`.`sbtest1`...
Creating new table...
CREATE TABLE `increment`.`_sbtest1_new` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`k` int(11) NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
`firstname` varchar(20) DEFAULT NULL,
`dtname` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=2000001 DEFAULT CHARSET=latin1
Created new table increment._sbtest1_new OK.
Altering new table...
ALTER TABLE `increment`.`_sbtest1_new` add column name varchar(20)
Altered `increment`.`_sbtest1_new` OK.
2021-07-27T17:20:25 Creating triggers...
2021-07-27T17:20:25 Created triggers OK.
2021-07-27T17:20:25 Copying approximately 1972656 rows...
INSERT LOW_PRIORITY IGNORE INTO `increment`.`_sbtest1_new` (`id`, `k`, `c`, `pad`, `firstname`, `dtname`) SELECT `id`, `k`, `c`, `pad`, `firstname`, `dtname` FROM `increment`.`sbtest1` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) AND ((`id` <= ?)) LOCK IN SHARE MODE /*pt-online-schema-change 28208 copy nibble*/
SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `increment`.`sbtest1` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) ORDER BY `id` LIMIT ?, 2 /*next chunk boundary*/
Copying `increment`.`sbtest1`: 62% 00:18 remain
2021-07-27T17:21:12 Copied rows OK.
2021-07-27T17:21:12 Adding original triggers to new table.
2021-07-27T17:21:12 Analyzing new table...
2021-07-27T17:21:12 Swapping tables...
RENAME TABLE `increment`.`sbtest1` TO `increment`.`_sbtest1_old`, `increment`.`_sbtest1_new` TO `increment`.`sbtest1`
2021-07-27T17:21:12 Swapped original and new tables OK.
2021-07-27T17:21:12 Dropping old table...
DROP TABLE IF EXISTS `increment`.`_sbtest1_old`
2021-07-27T17:21:12 Dropped old table `increment`.`_sbtest1_old` OK.
2021-07-27T17:21:12 Dropping triggers...
DROP TRIGGER IF EXISTS `increment`.`pt_osc_increment_sbtest1_del`
DROP TRIGGER IF EXISTS `increment`.`pt_osc_increment_sbtest1_upd`
DROP TRIGGER IF EXISTS `increment`.`pt_osc_increment_sbtest1_ins`
2021-07-27T17:21:12 Dropped triggers OK.
# Event Count
# ================== =====
# INSERT 200
# mysql_warning_1062 69
Successfully altered `increment`.`sbtest1`.
提取执行时间:
开始时间2021-07-27T17:20:25 Creating triggers...
结束时间2021-07-27T17:21:12 Dropped triggers OK.
可以看到pt-osc执行大概使用了60秒查看TPS和QPS
SQL statistics:
queries performed:
read: 0
write: 533
other: 0
total: 533
transactions: 533 (52.53 per sec.)
queries: 533 (52.53 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 10.1436s
total number of events: 533
Latency (ms):
min: 2.90
avg: 19.02
max: 299.61
95th percentile: 176.73
sum: 10140.00
Threads fairness:
events (avg/stddev): 533.0000/0.00
execution time (avg/stddev): 10.1400/0.00
可以发现使用pt-osc时候的TPS和QPS为52
四 压测添加索引的表现
4.1 online DDL 表现
mysql> CREATE INDEX idx_pad ON sbtest1(pad);
Query OK, 0 rows affected (20.12 sec)
Records: 0 Duplicates: 0 Warnings: 0可以看到执行时间20秒
查看TPS和QPS
SQL statistics:
queries performed:
read: 0
write: 1152
other: 0
total: 1152
transactions: 1152 (115.11 per sec.)
queries: 1152 (115.11 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 10.0051s
total number of events: 1152
Latency (ms):
min: 2.65
avg: 8.68
max: 433.15
95th percentile: 23.10
sum: 9999.98
Threads fairness:
events (avg/stddev): 1152.0000/0.00
execution time (avg/stddev): 10.0000/0.00
可以看到此时的TPS和QPS为115
4.2 gh-ost的表现
gh-ost --max-load=Threads_running=25 --critical-load=Threads_running=100 --chunk-size=10000 --throttle-control-replicas="10.2.4.178:3307" --max-lag-millis=1500 --user="ghost" --password="123" --host=10.2.4.178 --port=3307 --database="increment" --table="sbtest1" --verbose --alter='ADD INDEX idx_pad (pad)' --switch-to-rbr --allow-on-master --cut-over=default --exact-rowcount --concurrent-rowcount --default-retries=120 --initially-drop-ghost-table --initially-drop-old-table --ok-to-drop-table --execute
Copy: 2000000/2000000 100.0%; Applied: 1365; Backlog: 0/1000; Time: 1m49s(total), 1m47s(copy); streamer: backups.000020:450728723; Lag: 0.07s, State: migrating; ETA: due
可以看到gh-ost加索引使用了1分49秒
查看TPS和QPS
SQL statistics:
queries performed:
read: 0
write: 1910
other: 0
total: 1910
transactions: 1910 (190.94 per sec.)
queries: 1910 (190.94 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 10.0005s
total number of events: 1910
Latency (ms):
min: 2.42
avg: 5.23
max: 97.54
95th percentile: 10.84
sum: 9993.91
Threads fairness:
events (avg/stddev): 1910.0000/0.00
execution time (avg/stddev): 9.9939/0.00
可以看到此时的TPS和QPS为191
4.3 pt-osc的表现
pt-online-schema-change --print --statistics --progress time,30 --preserve-triggers --user=root --password=123 --alter 'ADD INDEX idx_pad (pad)' h=localhost,P=3306,D=increment,t=sbtest1 --pause-file=/tmp/ptosc.txt --max-load=threads_running=100,threads_connected=200 --critical-load=threads_running=1000 --chunk-size=10000 --alter-foreign-keys-method auto --execute
开始时间2021-07-27T17:56:42 Creating triggers...
结束时间2021-07-27T17:57:51 Dropped triggers OK.可以看到执行时间大概为1分9秒
查看TPS和QPS
SQL statistics:
queries performed:
read: 0
write: 542
other: 0
total: 542
transactions: 542 (53.83 per sec.)
queries: 542 (53.83 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 10.0659s
total number of events: 542
Latency (ms):
min: 2.19
avg: 18.57
max: 400.85
95th percentile: 235.74
sum: 10063.03
Threads fairness:
events (avg/stddev): 542.0000/0.00
execution time (avg/stddev): 10.0630/0.00
可以看到此时的TPS和QPS为53
五 对比总结
通过以上的两个场景对比发现.相比于pt-osc和gh-ost.onlineDDL执行相对较快.从对TPS和QPS的影响来看.gh-ost是对并发影响最小的.pt-osc的性能最差.最后建议大家在执行onlineDDL的时候使用gh-ost这个工具.同时还是要选择业务低峰期执行.保证安全.
六 文章总结
这篇文章主要对MySQL的online DDL的inplace方式和gh-ost和pt-osc的压测对比.