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