上篇写了 mysql 5.5 + mycat 压入600万条记录数据,通过mycat上传花了32分钟,差不多是直联的1倍时间。mycat 内存平均占用在 200 - 300M,峰值过400M,终生代对像基本中 18M,老年代未超过50M,基本还算稳定。由于本地环境有限,没有办法开更多的CPU和内存出来,不然到是想试验一下在 8C 32G的PC SERVER跑mycat的效能究竟如何。
这篇我们把数据降到 300 万笔,看下mysql 5.6的情况又是如何?
通过mycat上送数据时间控制在 1000 sec以内,也就是 17分钟,基本和5.5的情况一样。
31 19:32:16 finished records :2981200 failed:0 speed:3004.8844996260527
31 19:32:17 finished records :2984400 failed:0 speed:3005.080967216383
31 19:32:18 finished records :2988500 failed:0 speed:3006.1581731088413
31 19:32:19 finished records :2991400 failed:0 speed:3006.033370447338
31 19:32:20 finished records :2995300 failed:0 speed:3006.906659706467
31 19:32:21 finished records :2998500 failed:0 speed:3007.0761812689793
finishend:3000000 failed:0
used time total:998seconds
tps:3005.710850616171
直联mysql 5.6 数据库,基本已经到了 mycat 一半的时间。
31 19:49:29 finished records :2975800 failed:0 speed:5939.424180430118
31 19:49:30 finished records :2981600 failed:0 speed:5939.122796184269
31 19:49:31 finished records :2988000 failed:0 speed:5940.015386786846
31 19:49:32 finished records :2995000 failed:0 speed:5942.106620637661
finishend:3000000 failed:0
used time total:505seconds
tps:5939.41793704217
houdembp:bin houkai$
再来看下 mysql 5.7 环境下情况又是如何?
mycat 在 mysql5.7上压入300万条记录,花了 943 sec。
31 20:26:46 finished records :2983600 failed:0 speed:3179.071573862056
31 20:26:47 finished records :2986500 failed:0 speed:3178.7677684762884
31 20:26:48 finished records :2990300 failed:0 speed:3179.404628493539
31 20:26:49 finished records :2993900 failed:0 speed:3179.8344604031536
31 20:26:50 finished records :2998400 failed:0 speed:3181.2216256017045
finishend:3000000 failed:0
used time total:943seconds
tps:3180.998833633761
直联mysql 5.7 环境,居然比5.6的时间更长,TPS更低 - - ;
31 20:46:46 finished records :2981200 failed:0 speed:5450.858713200944
31 20:46:47 finished records :2985900 failed:0 speed:5449.448557928337
31 20:46:48 finished records :2991000 failed:0 speed:5448.762323930833
31 20:46:49 finished records :2997100 failed:0 speed:5449.896988200467
finishend:3000000 failed:0
used time total:550seconds
tps:5453.553899291038
houdembp:bin houkai$
再横向比较一下,mysql 5.5、5.6、5.7 各自的情况
场景 | mysql 5.5(600万) | mysql 5.6(300万) | mysql 5.7 (300万) |
---|---|---|---|
mycat | sec 1953 tps 3072 | sec 998 tps 3005 | sec943 tps 3180 |
直联 | sec 1067 tps 5622 | sec 505 tps 5939 | sec 550 tps 5453 |
乍一看 mysql 5.7 的效能还不及 5.6 甚至是 5.5 ,怎么看也不科学。其实 5.6 的环境,mysql做了参数调优,innodb buffer 都作了调整,5.5环境使用mysql推荐的配置。
而 5.7 环境在没有配任何参数的情况下通过mycat压入300万数据效果要比另两个环境要好,到真是觉得有些意外。这个事情回头还真得花时间去看看。
OK,本篇完。
环境部署参考下文