MyCat 学习笔记 第十四篇 . 性能测试（中）

前一篇写了在mariadb galera cluster 环境下 mycat 压测 1000万数据的测试。
《 MyCat 学习笔记 第十四篇 . 性能测试（上）》

由于从深圳滚回老家以后，网络环境的调整，VM环境如下：

192.168.13.1   真机  mbp   i5 2.5G+16G+SSD 客户端 
192.168.13.180 vm centos 7 单核 4G mysql 5.6
192.168.13.179 vm centos 7 单核 4G mysql 5.6 
192.168.31.178 vm centos 7 单核 4G mycat 1.5 ga

这篇主要是写mycat做数据分库情况下，千万数据的批量 insert ，如果来得急的话把 select 一起看下。

验证<3> mycat 分库情况下千万条记录INSERT

schema.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://org.opencloudb/">

    <schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100">
        针对 t_mod_long 的 sharding_mod_id 字段取模表进行分库
        <table name="t_mod_long" dataNode="dn2,dn3" rule="mod-sharding-long"/>


    </schema>

    <dataNode name="dn2" dataHost="mysql_179_3307" database="test" />
    <dataNode name="dn3" dataHost="mysql_180_3307" database="test" />


    <dataHost name="mysql_179_3307" maxCon="1000" minCon="10" balance="0"
        writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <writeHost host="host_179_3307" url="192.168.13.179:3307" user="root" password="root123" />
    </dataHost>

    <dataHost name="mysql_180_3307" maxCon="1000" minCon="10" balance="0"
        writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <writeHost host="host_180_3307" url="192.168.13.180:3307" user="root" password="root123" />
    </dataHost>

</mycat:schema>

验证用SQL脚本文件

total=10000000
sql=insert into t_mod_long (sharding_mod_id,context) values (${int(1-10000000)},'${char([a-f,0-9]8:45)}')

执行数据insert脚本

test_stand_insert_perf.sh jdbc:mysql://192.168.13.178:8066/TESTDB test test 50 "file=/Users/houkai/Desktop/first.sql"


开始慢长的等待

check JAVA_HOME & java
---------set HOME_DIR------------
create jobs ...
total record 10000000 batch size:100 autocomit false
success ful created connections ,total :50
create jobs finished ,begin run test...
success create job count: 50 teset threads: 50
04 21:19:07 finished records :0 failed:0 speed:0.0
04 21:19:08 finished records :0 failed:0 speed:0.0
04 21:19:09 finished records :2200 failed:0 speed:1041.6666666666667
04 21:19:10 finished records :5000 failed:0 speed:1604.1065126724413
04 21:19:11 finished records :8200 failed:0 speed:1991.7415593879039
04 21:19:12 finished records :9600 failed:0 speed:1876.0992769200705
04 21:19:13 finished records :14000 failed:0 speed:2287.581699346405
04 21:19:14 finished records :15900 failed:0 speed:2231.892195395845
04 21:19:15 finished records :19100 failed:0 speed:2350.479940930347
04 21:19:16 finished records :23000 failed:0 speed:2518.891687657431
04 21:19:17 finished records :24000 failed:0 speed:2368.265245707519
04 21:19:18 finished records :28200 failed:0 speed:2532.1002065188113
04 21:19:19 finished records :31600 failed:0 speed:2602.751008977844
04 21:19:20 finished records :33400 failed:0 speed:2541.0833840535606
04 21:19:21 finished records :37500 failed:0 speed:2650.551314673452
。。。
04 22:09:53 finished records :9955600 failed:0 speed:3268.684800563145
04 22:09:54 finished records :9958600 failed:0 speed:3268.5923219950405
04 22:09:55 finished records :9962000 failed:0 speed:3268.6300758589914
04 22:09:56 finished records :9965400 failed:0 speed:3268.669949090828
04 22:09:57 finished records :9968500 failed:0 speed:3268.6092843783526
04 22:09:58 finished records :9971900 failed:0 speed:3268.651281037666
04 22:09:59 finished records :9974900 failed:0 speed:3268.5589655587096
04 22:10:00 finished records :9977800 failed:0 speed:3268.437165672926
04 22:10:01 finished records :9980000 failed:0 speed:3268.0851509737454
04 22:10:02 finished records :9982100 failed:0 speed:3267.701701302583
04 22:10:03 finished records :9983600 failed:0 speed:3267.118946023946
04 22:10:04 finished records :9984600 failed:0 speed:3266.3762087057567
04 22:10:05 finished records :9986700 failed:0 speed:3265.993695432279
04 22:10:06 finished records :9987200 failed:0 speed:3265.0840790535335
04 22:10:07 finished records :9987600 failed:0 speed:3264.1434450446304
04 22:10:08 finished records :9989800 failed:0 speed:3263.7957757338622
04 22:10:09 finished records :9991800 failed:0 speed:3263.3819464607827
04 22:10:10 finished records :9994000 failed:0 speed:3263.0347529199657
04 22:10:11 finished records :9996200 failed:0 speed:3262.6824614416487
04 22:10:12 finished records :9998400 failed:0 speed:3262.334658814493
finishend:10000000 failed:0
used time total:3065seconds
tps:3262.5362957162897

总共用了差不多 1 个小时，TPS控制在 3262 。
应该是虚拟机环境的原因，千万级分库插入时反而比集群情况下只快了 500 秒。
OK，再看一下几台服务器的情况

192.168.13.178 mycat 服务

192.168.13.179 mysql 1

192.168.13.180 mysql 2

小结
三台VM环境还算平稳，mycat 的CPU和网络开销比较大，应该是在计算取模的问题。
单个mysql 瞬时磁盘IO较高，估计是checkpoint 或是 文件空间不够的关系。

如果使用mycat 分库情况下，全部使用独立服务器的效果应该会不错。

验证<4> 直联 mysql 千万条记录INSERT

执行数据insert脚本

test_stand_insert_perf.sh jdbc:mysql://192.168.13.180:3307/test root root123 50 "file=/Users/houkai/Desktop/first.sql"


check JAVA_HOME & java
---------set HOME_DIR------------
create jobs ...
total record 10000000 batch size:100 autocomit false
success ful created connections ,total :50
create jobs finished ,begin run test...
success create job count: 50 teset threads: 50
05 07:39:25 finished records :0 failed:0 speed:0.0
05 07:39:26 finished records :0 failed:0 speed:0.0
05 07:39:27 finished records :0 failed:0 speed:0.0
05 07:39:28 finished records :0 failed:0 speed:0.0
05 07:39:29 finished records :0 failed:0 speed:0.0
05 07:39:30 finished records :0 failed:0 speed:0.0
05 07:39:31 finished records :600 failed:0 speed:97.38678785911378
05 07:39:32 finished records :3700 failed:0 speed:516.4712451144612
05 07:39:33 finished records :5000 failed:0 speed:612.0700208103807
05 07:39:34 finished records :5400 failed:0 speed:588.7483645878762
05 07:39:35 finished records :7000 failed:0 speed:687.8930817610063
05 07:39:36 finished records :8300 failed:0 speed:742.3307396476165
05 07:39:37 finished records :8700 failed:0 speed:713.9926138695117
05 07:39:38 finished records :8900 failed:0 speed:674.7536012130402
05 07:39:39 finished records :9700 failed:0 speed:683.5318159396801
05 07:39:40 finished records :10800 failed:0 speed:710.8536826169947
05 07:39:41 finished records :13300 failed:0 speed:821.1904173870091
。。。
05 08:14:19 finished records :9917300 failed:0 speed:4735.349842812341
05 08:14:20 finished records :9921700 failed:0 speed:4735.180755551907
05 08:14:21 finished records :9926400 failed:0 speed:4735.157196972407
05 08:14:22 finished records :9929700 failed:0 speed:4734.466143014759
05 08:14:23 finished records :9932400 failed:0 speed:4733.4852956665
05 08:14:24 finished records :9940200 failed:0 speed:4734.936987068738
05 08:14:25 finished records :9945000 failed:0 speed:4734.956692643463
05 08:14:26 finished records :9950500 failed:0 speed:4735.311753452559
05 08:14:27 finished records :9954800 failed:0 speed:4735.097935969535
05 08:14:28 finished records :9959000 failed:0 speed:4734.832276922316
05 08:14:29 finished records :9963100 failed:0 speed:4734.52160094889
05 08:14:30 finished records :9966700 failed:0 speed:4733.9737317584295
05 08:14:31 finished records :9970600 failed:0 speed:4733.568810649652
05 08:14:32 finished records :9977000 failed:0 speed:4734.348344971089
05 08:14:33 finished records :9980700 failed:0 speed:4733.846525989271
05 08:14:34 finished records :9983700 failed:0 speed:4733.017820935415
05 08:14:35 finished records :9988400 failed:0 speed:4732.993204531702
05 08:14:36 finished records :9993800 failed:0 speed:4733.297906300234
05 08:14:37 finished records :9997500 failed:0 speed:4732.8042628612975
finishend:10000000 failed:0
used time total:2113seconds
tps:4732.383701670532

整体执行 35 分钟左右 ， 和集群环境差别不大，再来看下数据库在批量插入时的情况。