pt-archive提速的实践经验
最近遇到很多业务需求,需要进行数据导出工作,由于有格式要求,故之前一直使用mysqldump的方法。
mysqldump -uuser -ppassword -S mysql.sock -t db table -T /data1/dbatemp/
当然可以根据需求增加分隔符和行结束符。--fields-terminated-by和--lines-terminated-by,其他也可以增加where条件进行检索,可以自行使用--help查询。
但是后续由于业务需求比较频发,同事需求数据容量越来越大,已经不适合在localhost进行操作,需要一台中心管理机来统一进行管理,这时候mysqldump加-T参数导出CSV格式只能在本地操作的局限性就不能满足要求了。于是开始转而需求其他方法,研究了一下percona的工具pt-archive,发现可以满足我们的需求,于是开始使用,但是在实际使用过程中发现一个问题,导致pt-archinve完全无法使用,这就是速度问题。同mysqldump对比,pt-archive的速度完全无法接受,经过实际测试,不加参数的pt-archive比mysqldump要慢很多,属于完全无法使用状态。
我们的实验环境如下,mysql版本5.5,服务器是12块盘的SAS服务器,目标数据库表大小872M。
使用mysqldump的导出信息如下:
time mysqldump -uroot -p -S /tmp/mysql10010.sock -t gemini table_definition_20130821 -T /data1/dbatemp/mysql10010/
real 0m9.679s user 0m0.004s sys 0m0.001s
使用pt-archive的导出信息如下:
time pt-archiver --source u=root,p=,h=localhost,S=/tmp/mysql10010.sock,D=gemini,t=table_definition_20130821 --no-delete --where "1=1" --no-check-charset --file=/data1/dbatemp/mysql10010/4.txt --statistics
real 9m5.620s user 3m58.810s sys 0m38.124s
一个9s多,一个9m多,相差近60倍,导致pt-archive完全无法使用。根据--statistics的输出结果我们可以看到select占了很大一部分。
Action Count Time Pct select 1065539 294.1826 52.01 commit 1065539 54.3843 9.62 print_file 1065538 8.0095 1.42 other 0 209.0001 36.95
从而我们的加速思路即为如何减少select占用的时间,开启general log之后,发现为一个大select后跟着一个commit,众所周知,大select的查询效率非常慢。那么我们尝试这将一个大select分片成很多个小select,看看会不会降低查询时间。这里就要使用--limit参数了。
time pt-archiver --source u=root,p=,h=localhost,S=/tmp/mysql10010.sock,D=gemini,t=table_definition_20130821 --no-delete --where "1=1" --no-check-charset --file=/data1/dbatemp/mysql10010/4.txt --limit=1000 --statistics
real 3m13.553s user 2m15.873s sys 0m26.648s Action Count Time Pct commit 1065539 46.1518 23.86 print_file 1065538 6.2581 3.24 select 1067 4.6308 2.39 other 0 136.3800 70.51
从上面可以看出增加了--limit参数之后,速度快了很多,基本是原来不加参数的1/3,但是和dump比较还是相差很多,仍然有将近20倍的差距,还处于不可用状态。根据状态分析,这次commit所占的时间比较多。再次查看general log,发现一次select后,跟着n个commit,导致commit的时间非常大。思考采用--txn-size参数来控制commit的次数。
time pt-archiver --source u=root,p=,h=localhost,S=/tmp/mysql10010.sock,D=gemini,t=table_definition_20130821 --no-delete --where "1=1" --no-check-charset --file=/data1/dbatemp/mysql10010/4.txt --limit=1000 --txn-size=1000 --statistics
real 1m57.196s user 1m41.504s sys 0m10.627s Action Count Time Pct print_file 1065538 4.9122 4.19 select 1067 4.4760 3.82 commit 1066 0.1161 0.10 other 0 107.5997 91.88
增加txn-size之后,速度再次提高,提升幅度在30%,虽然标准值仍和mysqldump比有较大差距。从状态分析结果看,主要时间消耗再other上了,但是由于输出没有明确指向,故有很多可能。只能在从pt-archive的参数中查找看是否还有优化的选项。
首先,尝试加入--buffer参数,并没明显提高
Action Count Time Pct select 1067 5.1447 4.40 print_file 1065538 0.3666 0.31 commit 1066 0.1133 0.10 flush 1066 0.0173 0.01 other 0 111.2178 95.17 real 1m56.989s user 1m45.411s sys 0m7.626s
然后加入--ascend-first参数测试
Action Count Time Pct select 1067 4.6041 4.31 commit 1066 0.1501 0.14 flush 1066 0.0101 0.01 print_file 1065538 -0.4222 -0.40 other 0 102.4029 95.93 real 1m46.876s user 1m34.415s sys 0m6.143s
可以看出仍然变化不大,经过多次测试之后,添加只使用主键参数可以将时间缩减近1m之内。
time pt-archiver --source u=root,p=,h=localhost,S=/tmp/mysql10010.sock,D=gemini,t=table_definition_20130821 --no-delete --where "1=1" --no-check-charset --statistics --buffer --limit=10000 --commit-each --no-check-charset --primary-key-only --share-lock --file=/data1/dbatemp/mysql10010/12.txt Action Count Time Pct select 108 1.1020 1.94 commit 108 0.0358 0.06 flush 108 0.0009 0.00 print_file 1065538 -5.2057 -9.18 other 0 60.7444 107.18 real 0m56.810s user 0m54.604s sys 0m0.629s