MySQL DDL方案测试及选型.
性能测试
一、测试背景
1、机器配置和版本
机器配置（下面测试qps数据都是以本机器配置为准）
型号：Dell s3710
磁盘：SSD 3T
CPU:32
内存：128G
MySQL版本：5.7.22
gh-ost版本：1.0.46
pt-osc版本：3.0.12
2、数据准备和业务模拟
----插入1000万条数据,表大小约2G--
sysbench --mysql-user=darren --mysql-password=darren --mysql-host=10.126.126.164 --mysql-port=3306 --test=tests/db/oltp.lua \
--oltp_tables_count=1 --oltp-table-size=10000000 --rand-init=on prepare
----64个并发线程模拟SQL----
sysbench --mysql-user=darren --mysql-password=darren --mysql-host=10.126.126.164 --mysql-port=3306 --test=tests/db/oltp.lua \
--oltp-reconnect-mode=transaction --oltp_tables_count=1 --oltp-table-size=10000000 --num-threads=64 --oltp-read-only=off \
--report-interval=10 --rand-type=uniform --max-time=6000 --max-requests=0 run
二、性能测试
1、测试工具命令
-----------------------------------------gh-ost-------------------------------------------
time gh-ost \
--max-load=Threads_running=70 \
--critical-load=Threads_running=80 \
--chunk-size=1000 \
--dml-batch-size=100 \
--initially-drop-old-table \
--initially-drop-ghost-table \
--initially-drop-socket-file \
--ok-to-drop-table \
--host="10.126.126.164" \
--port=3306 \
--user="darren" \
--password="darren" \
--assume-rbr \
--allow-on-master \
--assume-master-host=10.126.126.164:3306 \
--database="sbtest" \
--table="sbtest1" \
--alter="drop primary key" \
--panic-flag-file=/tmp/ghost.panic.flag \
--serve-socket-file=/tmp/ghost.sock \
--verbose \
--execute
-----------------------------------------pt-osc-------------------------------------------
time pt-online-schema-change \
--user=darren \
--password=darren \
--port=3306 \
--host=10.126.126.164 \
--alter "engine=innodb" \
D=sbtest,t=sbtest1 \
--max-load="Threads_running=70" \
--critical-load="Threads_running=80" \
--execute
2、单表高负载测试(64个并发线程，写入QPS：26927)
| DDL类型 | 执行前QPS（r/w） | pt-osc执行时间 | pt-osc执行时QPS（r/w） | gh-ost执行时间 | gh-ost执行时QPS（r/w） | online执行时间 | online执行时QPS（r/w） |
|---|---|---|---|---|---|---|---|
| 添加普通索引 | （94254/26927） | 8m32.448s | （73123/22011) | 无法完成，一直在追日志 | （91363/24880） | 1 min 5.76 | (0.00/0.00) |
| 删除普通索引 | (93112/26543) | 7m59.124s | （74021/21280) | 无法完成，一直在追日志 | （91363/24880） | 0.03s | (92902/26043) |
| 修改普通索引名 | (92993/26887) | 8m27.328s | （75089/23321) | 无法完成，一直在追日志 | （90291/23879） | 0.00s | (92110/26876) |
| 添加主键索引 | (89991/26012) | 18m53.515s | （73021/19872) | 无法完成，一直在追日志 | - | 1 min 5.44 s | (79282/20838) |
| 删除主键索引 | (89809/25667) | 不支持 | - | 不支持 | - | 1 min 48.03s | (0/0) |
| 添加列 | (92484/26312) | 8m12.238s | （73521/21953) | 无法完成，一直在追日志 | （90362/25821） | 4 min 16.05 | (0.00/0.00) |
| 删除列 | (92001/26933) | 8m15.085s | (78430/22397) | 无法完成，一直在追日志 | (91138/26041) | 5 min 20.69 s | (0.00/0.00) |
| 修改列类型 | (91990/26989) | 8m49.021s | (73421/21859) | 无法完成，一直在追日志 | (93238/26123) | 2 min 2.76 | (0.00/0.00) |
| 修改列名 | (92901/27045) | 8m03.559s | (73552/22001) | 无法完成，一直在追日志 | (92998/25899) | 0.01s | (92808/27001) |
| 修改列默认值 | (93328/26312) | 8m19.230s | (74001/22339) | 无法完成，一直在追日志 | (92298/25005) | 0.01s | (91990/26909) |
| 修改列注释 | (93029/27049) | 7m50.689s | (73504/21992) | 无法完成，一直在追日志 | (91900/25698) | 0.01s | (92989/27022) |
| 修改自增值 | (92314/26978) | 8m08.329s | (72990/22334) | 无法完成，一直在追日志 | (92398/26821) | 0.00s | (91990/26876) |
| 优化表 | (93253/26312) | 8m50s.120s | (73523/22323) | 无法完成，一直在追日志 | (91092/27090) | 2 min 21s | (0.10/0.00) |
3、单表正常负载测试(4个并发线程以下，写入QPS5000以下)
| DDL类型 | 执行前QPS（r/w） | pt-osc执行时间 | pt-osc执行时QPS（r/w） | gh-ost执行时间 | gh-ost执行时QPS（r/w） | online执行时间 | online执行时QPS（r/w） |
|---|---|---|---|---|---|---|---|
| 添加普通索引 | (19341/5525) | 3m46.584s | (14474/4134) | 23m37.340s | (17224/4920) | 25.89s | (18713/5346) |
| 删除普通索引 | (19311/5606) | 2m53.548s | (14089/4033) | 16m45.551s | (17335/4953) | 0.01s | (19050/5439) |
| 修改普通索引名 | (19299/5510) | 4m2.699s | (14321/4103) | 17m39.098s | (17098/4934) | 0.00s | (19298/5409) |
| 添加主键索引 | (18993/6087) | 16m29.108s | （10089/3321) | 无法完成，一直在追日志 | - | 1 min 5.44 s | (16202/3089) |
| 删除主键索引 | (19993/5887) | 不支持 | - | 不支持 | - | 1m52s | (0/0) |
| 添加列 | (19248/5540) | 3m0.202s | (13441/3840) | 24m14.164s | (17359/4960) | 2m40.44 | （18606/5316） |
| 删除列 | (19200/5472) | 4m1.786s | (13208/3888) | 23m2.302s | (17116/4890) | 1m29.71 sec | (18026/5389) |
| 修改列类型 | (19199/5618) | 4m0.872s | (13212/3901) | 22m3.992s | (17818/5090) | 2 m49.54s | (0.00/0.00) |
| 修改列名 | (19150/5472) | 3m49.122s | (13200/3890) | 23m0.387s | (17959/5130) | 0.01 s | (19101/5411) |
| 修改列默认值 | (19332/5014) | 2m51.322s | (13902/3860) | 24m3.712s | (17789/4987) | 0.01s | (19211/5493) |
| 修改列注释 | (19302/5388) | 4m1.302s | (13443/3844) | 23m3.902s | (17289/4887) | 0.00s | (19201/5421) |
| 修改自增值 | (19231/5339) | 3m3.112s | (13903/3904) | 21m3.002s | (17399/4917) | 0.00s | (19008/5239) |
| 优化表 | (19372/5533) | 2m47.955s | (13230/3780） | 17m41.545s | (17255/4929) | 1m37.13 | （18977/5421） |
【注】
经过测试：当写入QPS5000以上，gh-ost无法完成任务，其原因是apply binlog是单线程，可以理解为slave，当原表写入量巨大时(QPS=5000以上)，
一直在应用日志，而gh-ost设计是binlog应用优先级高于row copy，所以我们看到row copy进度一直没变，这样如果原表一直压力这么大，那么gh-ost DDL将无法完成。
经过在s3710机器上测试如果原表写入的QPS大于5000将大概率出现此情况，小于5000的话没问题。
Copy: 0/9705089 0.0%; Applied: 183480; Backlog: 1000/1000; Time: 1m24s(total), 1m24s(copy); streamer: tjtx-126-164.001588:441763689;
Copy: 0/9705089 0.0%; Applied: 185490; Backlog: 1000/1000; Time: 1m25s(total), 1m25s(copy); streamer: tjtx-126-164.001588:442986068;
Copy: 0/9705089 0.0%; Applied: 207590; Backlog: 1000/1000; Time: 1m30s(total), 1m30s(copy); streamer: tjtx-126-164.001588:455843331;
三、各个工具优缺点对比
四、建议的DDL方案
1、MySQL5.5版本DDL方案
MySQL5.5版本online ddl功能不完善，建议全部使用PT-OSC工具进行
2、MySQL5.6和5.7版本DDL方案
3、MySQL8.0版本DDL方案
