mydumper性能测试

本想mk-parallel-dump和mydumper做个性能对比,mk-parallel-dump的作者认为存在潜在风险(http://www.dbunix.com/?p=2584),所以就放弃对mk-parallel-dump测试(个人认为不适合线上使用)。

Domas Mituzas对三个工具做了性能上对比(库大小为20G):

mysqldump: 75m18s
maatkit: 8m13s
mydumper: 6m44s

对mydumper及myloader做个测试,来回答以下问题:

1、mydumper开启多少线程,导出最快?
2、mydumper压缩率有多高?如果不压缩,导出多快?
3、myloader开启多少线程,恢复最快?

测试环境(实际环境不一样,可能结果也不一样):
机器:Dell PowerEdge 2950、磁盘做了raid10
数据库大小:3.7G
数据库表总数:20张

mydumper压缩情况下对比:

mydumper开启2线程

[xxx@xxx ~]
$time mydumper -u root -c -B lx_test -t 2 -o /usr/local/mysql/tmp
 
real    1m32.872s
user    2m20.259s
sys     0m2.655s

mydumper开启4线程

[xxx@xxx ~]
$time mydumper -u root -c -B lx_test -t 4 -o /usr/local/mysql/tmp 
 
real    0m53.454s
user    2m18.817s
sys     0m2.155s

mydumper开启6线程

[xxx@xxx ~]
$time mydumper -u root -c -B lx_test -t 6 -o /usr/local/mysql/tmp 
 
real    0m41.588s
user    2m18.920s
sys     0m2.431s

mydumper开启8个线程

[xxx@xxx ~]
$time mydumper -u root -c -B lx_test -t 8 -o /usr/local/mysql/tmp 
 
real    0m43.196s
user    2m19.397s
sys     0m2.765s

小结:可以发现,不是线程开的越多越好,到开到8个线程时,导出反而慢了。mydumper用多线程导出,写到不同的文件中,一个表最终会生成两个文件,一个table meta、一个table data。

mydumper未开启压缩情况下对比:

mydumper开启2线程

[xxx@xxx ~]
$time mydumper -u root -B lx_test -t 2 -o /usr/local/mysql/tmp
 
real    0m52.264s
user    0m34.607s
sys     0m8.252s

mydumper开启4线程

[xxx@xxx ~]
$time mydumper -u root -B lx_test -t 4 -o /usr/local/mysql/tmp 
 
real    0m32.877s
user    0m34.686s
sys     0m8.417s

mydumper开启6线程

[xxx@xxx ~]
$time mydumper -u root -B lx_test -t 6 -o /usr/local/mysql/tmp 
 
real    0m32.245s
user    0m34.045s
sys     0m9.117s

mydumper开启8线程

[xxx@xxx ~]
$time mydumper -u root -B lx_test -t 8 -o /usr/local/mysql/tmp 
 
real    0m35.713s
user    0m34.830s
sys     0m9.027s

小结:也是开到8线程时,导出反而慢了

上面内容总结一下:在开启6线程时,导出最快,压缩和未压缩时间上相差9秒(未压缩快),压缩率在10%左右(10G的数据压缩后在1G左右)

myloader恢复数据对比:

[xxx@xxx ~]
$time myloader -u root -B lx_test -t 2 -d /usr/local/mysql/tmp/
 
real    6m12.358s
user    0m5.215s
sys     0m6.471s
 
[xxx@xxx ~]
$time myloader -u root -B lx_test -t 4 -d /usr/local/mysql/tmp/ 
 
real    4m17.518s
user    0m5.173s
sys     0m6.156s
 
[xxx@xxx ~]
$time myloader -u root -B lx_test -t 6 -d /usr/local/mysql/tmp/ 
 
real    3m38.339s
user    0m5.126s
sys     0m6.140s
 
[xxx@xxx ~]
$time myloader -u root -B lx_test -t 8 -d /usr/local/mysql/tmp/ 
 
real    3m54.703s
user    0m5.156s
sys     0m5.911s

小结:开到8线程时,导入反而慢了,6线程时CPU利用率在60%左右,磁盘写入在100M/s,8线程时CPU利用率在90%左右,磁盘写入在60M/s


2 Responses to “mydumper性能测试”

  1. […] 参考:http://www.dbunix.com/?p=2878 […]

  2. […] Mydumper是一个使用c语言编写的多线程导出导入工具,并且能够保证多个表之间的一致性。Mydumper已经好几篇blog在讨论:Mydumper性能测试,Mydumper使用和源码分析。通过stronghearted的测试,我们看到不是线程越多越好,6个线程的时候速度最快(这个肯定跟机器的配置等诸多因素有关,只能作为一个经验值而不是绝对值,机器好的时候,线程越多越好)。 […]

Post a Comment