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方案

转载于:https://www.cnblogs.com/mysql-dba/p/9901618.html