search

huhongbo / devops

6 stars 0 forks source link

oneproxy 性能测试 #22

Open huhongbo opened 9 years ago

huhongbo commented 9 years ago

在上次那个百度盘上有一个oneproxy 的软件，也有文档，测试下转发和分表性能

aricy commented 9 years ago

收到，回头再测试下读写分离。

aricy commented 9 years ago

oneproxy结合onesql，测试过程中无法实现HA，只要主节点一宕机，slave节点无法切换，日志也较简单，配置信息如下： 10.70.99.141 oneproxy 10.70.99.142 master 10.70.99.143 slave 日志信息如下： 2015-01-29 16:16:55: (critical) plugin oneproxy 0.8.4 started 2015-01-29 16:16:55: (critical) Background thread 2 started 2015-01-29 16:16:55: (critical) Background thread 1 started 2015-01-29 16:16:56: (critical) Ping backend (10.70.99.142:3306) success, mark it up! 2015-01-29 16:16:56: (critical) Ping backend (10.70.99.143:3306) success, mark it up! 2015-01-29 16:20:46: (critical) Ping backend (10.70.99.142:3306) failed, mark it down! 很难排查原因，后续再对比下mysql proxy实现，不过mysql自己也不建议使用。

aricy commented 9 years ago

测试了Mysql NDB，满足热备份(ndb_restore，速度比较快)，HA，不存在单点故障，热添加SQL点和数据节点，操作简单，并且符合MPP模式，后续再测试下性能。

aricy commented 9 years ago

配置完成master-slave复制 master : 10.70.99.141 wangbin1 slave : 10.70.99.142 wangbin2 mysql-proxy : 10.70.99.143 wangbin3 1、运行mysql-proxy需要安装lua包，安装完成后，下载mysql-proxy官方包后解压至/usr/local目录 2、进行/usr/local/mysql-proxy目录下，创建log、run、scripts目录 mkdir -p log run scripts 3、复制自带的lua读写分离脚本 cp ./share/doc/mysql-proxy/rw-splitting.lua ./scripts 4、为使之后方便启停mysql-proxy，编写启停脚本控制，将脚本复制至/etc/init.d/mysql-proxy 5、启动mysql-proxy /etc/init.d/mysql-proxy start 6、测试mysql-proxy的读写分离 mysql -uroot -proot -h10.70.99.143 -P3307 由于默认的lua脚本是 min_idle_connections = 4, max_idle_connections = 8 需要大于4个连接后才能实现读写分离，4个以内都会在master节点运行 master节点(10.70.99.141) master

slave节点(10.70.99.142) slave

可以看到wangbin3已经在slave节点有1个连接，而master节点有1个连接 而在wangbin3这个mysql-proxy节点中可以看到如下进程： mysql-proxy

至此已经完成了简单部署

aricy commented 9 years ago

mysql-proxy脚本如下：

!/bin/sh

mysql-proxy This script starts and stops the mysql-proxy daemon

chkconfig: - 78 30

processname: mysql-proxy

description: mysql-proxy is a proxy daemon to mysql

Source function library.

PROXY_PATH=/usr/local/bin

PROXY_PATH=/usr/local/mysql-proxy prog="mysql-proxy"

Source networking configuration.

. /etc/sysconfig/network

Check that networking is up.

Set default mysql-proxy configuration.

PROXY_OPTIONS="--daemon"

PROXY_OPTIONS="--admin-username=root --admin-password=root --proxy-read-only-backend-addresses=10.70.99.141:3306 --proxy-backend-addresses=10.70.99.142:3306

--admin-lua-script=$PROXY_PATH/lib/mysql-proxy/lua/admin.lua --proxy-lua-script=$PROXY_PATH/scripts/rw-splitting.lua" PROXY_OPTIONS="--proxy-read-only-backend-addresses=10.70.99.142:3306 --proxy-backend-addresses=10.70.99.141:3306 --proxy-lua-script=$PROXY_PATH/scripts/rw-s plitting.lua" PROXY_PID=$PROXY_PATH/run/mysql-proxy.pid

Source mysql-proxy configuration.

PATH=$PATH:$PROXY_PATH/bin

By default it's all good

RETVAL=0

See how we were called.

case "$1" in start)

Start daemon.

    echo -n $"Starting $prog: "
    $NICELEVEL $PROXY_PATH/bin/mysql-proxy $PROXY_OPTIONS --daemon --proxy-address=:3307 --pid-file=$PROXY_PID --user=root --log-level=warning --log-file

=$PROXY_PATH/log/mysql-proxy.log RETVAL=$? echo if [ $RETVAL = 0 ]; then touch /var/lock/subsys/mysql-proxy fi ;; stop)

Stop daemons.

    echo -n $"Stopping $prog: "
    killproc $prog
    RETVAL=$?
    echo
    if [ $RETVAL = 0 ]; then
            rm -f /var/lock/subsys/mysql-proxy
            rm -f $PROXY_PID
    fi
   ;;

restart) $0 stop sleep 3 $0 start ;; condrestart) [ -e /var/lock/subsys/mysql-proxy ] && $0 restart ;; status) status mysql-proxy RETVAL=$? ;; *) echo "Usage: $0 {start|stop|restart|status|condrestart}" RETVAL=1 ;; esac exit $RETVAL

aricy commented 9 years ago

以下为ONESql的测试，使用mydbtest工具： 1） option name mysql_test loop 2000000 user test/test@10.70.241.51:3306:test declare id1 int 2 1000 id2 int 1 1000 begin start; update t1 set id=id-1 where id=:id1; update t1 set id=id+1 where id=:id2; commit; end MYDBTEST: MySQL Database Test Utility , Release 1.0.1 (@) Copyright Lou Fangxin (AnySQL.net) 2012 - 2013, all rights reserved. 2015-03-16 15:09:20 ======================== mysql_test ======================== 2015-03-16 15:09:20 SQL02 exe=272443 fail=0 row=261 ela=21667 ms avg=79 us 2015-03-16 15:09:20 SQL02 1 ms exec= 272443, ela= 21667 ms, avg= 79 us, pct=100,100 2015-03-16 15:09:20 SQL03 exe=272443 fail=0 row=278 ela=21432 ms avg=78 us 2015-03-16 15:09:20 SQL03 1 ms exec= 272443, ela= 21432 ms, avg= 78 us, pct=100,100 2015-03-16 15:10:20 ======================== mysql_test ======================== 2015-03-16 15:10:20 SQL02 exe=175145 fail=0 row=170 ela=21837 ms avg=124 us 2015-03-16 15:10:20 SQL02 1 ms exec= 175145, ela= 21837 ms, avg= 124 us, pct=100,100 2015-03-16 15:10:20 SQL03 exe=175145 fail=0 row=179 ela=21602 ms avg=123 us 2015-03-16 15:10:20 SQL03 1 ms exec= 175145, ela= 21602 ms, avg= 123 us, pct=100,100 2015-03-16 15:11:20 ======================== mysql_test ======================== 2015-03-16 15:11:20 SQL02 exe=128568 fail=0 row=126 ela=21715 ms avg=168 us 2015-03-16 15:11:20 SQL02 1 ms exec= 128568, ela= 21715 ms, avg= 168 us, pct=100,100 2015-03-16 15:11:20 SQL03 exe=128568 fail=0 row=137 ela=21371 ms avg=166 us 2015-03-16 15:11:20 SQL03 1 ms exec= 128568, ela= 21371 ms, avg= 166 us, pct=100,100 2015-03-16 15:12:20 ======================== mysql_test ======================== 2015-03-16 15:12:20 SQL02 exe=119861 fail=0 row=127 ela=21659 ms avg=180 us 2015-03-16 15:12:20 SQL02 1 ms exec= 119861, ela= 21659 ms, avg= 180 us, pct=100,100 2015-03-16 15:12:20 SQL03 exe=119861 fail=0 row=130 ela=21190 ms avg=176 us 2015-03-16 15:12:20 SQL03 1 ms exec= 119861, ela= 21190 ms, avg= 176 us, pct=100,100 2015-03-16 15:13:20 ======================== mysql_test ======================== 2015-03-16 15:13:20 SQL02 exe=137105 fail=0 row=148 ela=21800 ms avg=159 us 2015-03-16 15:13:20 SQL02 1 ms exec= 137105, ela= 21800 ms, avg= 159 us, pct=100,100 2015-03-16 15:13:20 SQL03 exe=137105 fail=0 row=134 ela=21500 ms avg=156 us 2015-03-16 15:13:20 SQL03 1 ms exec= 137105, ela= 21500 ms, avg= 156 us, pct=100,100 2015-03-16 15:14:20 ======================== mysql_test ======================== 2015-03-16 15:14:20 SQL02 exe=143451 fail=0 row=116 ela=21860 ms avg=152 us 2015-03-16 15:14:20 SQL02 1 ms exec= 143451, ela= 21860 ms, avg= 152 us, pct=100,100 2015-03-16 15:14:20 SQL03 exe=143451 fail=0 row=170 ela=21617 ms avg=150 us 2015-03-16 15:14:20 SQL03 1 ms exec= 143451, ela= 21617 ms, avg= 150 us, pct=100,100 ^C2015-03-16 15:14:21 ======================== mysql_test ======================== 2015-03-16 15:14:21 SQL02 exe=4635 fail=0 row=6 ela=706 ms avg=152 us 2015-03-16 15:14:21 SQL02 1 ms exec= 4635, ela= 706 ms, avg= 152 us, pct=100,100 2015-03-16 15:14:21 SQL03 exe=4635 fail=0 row=4 ela=698 ms avg=150 us 2015-03-16 15:14:21 SQL03 1 ms exec= 4635, ela= 698 ms, avg= 150 us, pct=100,100 2015-03-16 15:14:21 Total tran=981208=2710/s, qtps=1962416=5421/s, ela=360796 ms, avg=367 us Summary: SQL02 exec=981208, rows=954=0/e, avg=133 us Summary: SQL03 exec=981208, rows=1032=0/e, avg=131 us Summary: exec=2710/s, qtps=5421/s

2） option name mysql_test loop 2000000 user test/test@10.70.241.51:3306:test declare id1 int 2 1000 id2 int 1 1000 begin start; update [trx_queue :id2] t1 set id=id-1 where id=:id1; update t1 set id=id+1 where id=:id2; commit; end MYDBTEST: MySQL Database Test Utility , Release 1.0.1 (@) Copyright Lou Fangxin (AnySQL.net) 2012 - 2013, all rights reserved. 2015-03-16 15:02:27 ======================== mysql_test ======================== 2015-03-16 15:02:27 SQL02 exe=119702 fail=0 row=112 ela=21530 ms avg=179 us 2015-03-16 15:02:27 SQL02 1 ms exec= 119702, ela= 21530 ms, avg= 179 us, pct=100,100 2015-03-16 15:02:27 SQL03 exe=119702 fail=0 row=103 ela=20981 ms avg=175 us 2015-03-16 15:02:27 SQL03 1 ms exec= 119702, ela= 20981 ms, avg= 175 us, pct=100,100 2015-03-16 15:03:27 ======================== mysql_test ======================== 2015-03-16 15:03:27 SQL02 exe=170634 fail=0 row=165 ela=21888 ms avg=128 us 2015-03-16 15:03:27 SQL02 1 ms exec= 170634, ela= 21888 ms, avg= 128 us, pct=100,100 2015-03-16 15:03:27 SQL03 exe=170634 fail=0 row=159 ela=21291 ms avg=124 us 2015-03-16 15:03:27 SQL03 1 ms exec= 170634, ela= 21291 ms, avg= 124 us, pct=100,100 2015-03-16 15:04:27 ======================== mysql_test ======================== 2015-03-16 15:04:27 SQL02 exe=318098 fail=0 row=308 ela=21938 ms avg=68 us 2015-03-16 15:04:27 SQL02 1 ms exec= 318098, ela= 21938 ms, avg= 68 us, pct=100,100 2015-03-16 15:04:27 SQL03 exe=318098 fail=0 row=310 ela=21341 ms avg=67 us 2015-03-16 15:04:27 SQL03 1 ms exec= 318098, ela= 21341 ms, avg= 67 us, pct=100,100 2015-03-16 15:05:27 ======================== mysql_test ======================== 2015-03-16 15:05:27 SQL02 exe=315375 fail=0 row=295 ela=21936 ms avg=69 us 2015-03-16 15:05:27 SQL02 1 ms exec= 315375, ela= 21936 ms, avg= 69 us, pct=100,100 2015-03-16 15:05:27 SQL03 exe=315375 fail=0 row=345 ela=21340 ms avg=67 us 2015-03-16 15:05:27 SQL03 1 ms exec= 315375, ela= 21340 ms, avg= 67 us, pct=100,100 2015-03-16 15:06:27 ======================== mysql_test ======================== 2015-03-16 15:06:27 SQL02 exe=316691 fail=0 row=319 ela=21941 ms avg=69 us 2015-03-16 15:06:27 SQL02 1 ms exec= 316691, ela= 21941 ms, avg= 69 us, pct=100,100 2015-03-16 15:06:27 SQL03 exe=316691 fail=0 row=327 ela=21340 ms avg=67 us 2015-03-16 15:06:27 SQL03 1 ms exec= 316691, ela= 21340 ms, avg= 67 us, pct=100,100 2015-03-16 15:07:27 ======================== mysql_test ======================== 2015-03-16 15:07:27 SQL02 exe=318543 fail=0 row=303 ela=21945 ms avg=68 us 2015-03-16 15:07:27 SQL02 1 ms exec= 318543, ela= 21945 ms, avg= 68 us, pct=100,100 2015-03-16 15:07:27 SQL03 exe=318543 fail=0 row=343 ela=21341 ms avg=66 us 2015-03-16 15:07:27 SQL03 1 ms exec= 318543, ela= 21341 ms, avg= 66 us, pct=100,100 ^C2015-03-16 15:07:43 ======================== mysql_test ======================== 2015-03-16 15:07:43 SQL02 exe=86369 fail=0 row=73 ela=5950 ms avg=68 us 2015-03-16 15:07:43 SQL02 1 ms exec= 86369, ela= 5950 ms, avg= 68 us, pct=100,100 2015-03-16 15:07:43 SQL03 exe=86369 fail=0 row=87 ela=5786 ms avg=67 us 2015-03-16 15:07:43 SQL03 1 ms exec= 86369, ela= 5786 ms, avg= 67 us, pct=100,100 2015-03-16 15:07:43 Total tran=1645412=4372/s, qtps=3290824=8745/s, ela=375063 ms, avg=227 us Summary: SQL02 exec=1645412, rows=1575=0/e, avg=83 us Summary: SQL03 exec=1645412, rows=1674=0/e, avg=81 us

Summary: exec=4372/s, qtps=8745/s

3） option name mysql_test loop 2000000 user test/test@10.70.241.51:3306:test declare id1 int 2 100 id2 int 1 100 begin start; select [trx_queue :id2] * from t1 where id in (:id1,:id2) for update; update t1 set id=id-1 where id=:id1; update t1 set id=id+1 where id=:id2; commit; end MYDBTEST: MySQL Database Test Utility , Release 1.0.1 (@) Copyright Lou Fangxin (AnySQL.net) 2012 - 2013, all rights reserved. 2015-03-16 15:16:07 ======================== mysql_test ======================== 2015-03-16 15:16:07 SQL02 exe=140445 fail=0 row=286 ela=18251 ms avg=129 us 2015-03-16 15:16:07 SQL02 1 ms exec= 140445, ela= 18251 ms, avg= 129 us, pct=100,100 2015-03-16 15:16:07 SQL03 exe=140445 fail=0 row=143 ela=15323 ms avg=109 us 2015-03-16 15:16:07 SQL03 1 ms exec= 140445, ela= 15323 ms, avg= 109 us, pct=100,100 2015-03-16 15:16:07 SQL04 exe=140445 fail=0 row=143 ela=14829 ms avg=105 us 2015-03-16 15:16:07 SQL04 1 ms exec= 140445, ela= 14829 ms, avg= 105 us, pct=100,100 2015-03-16 15:17:07 ======================== mysql_test ======================== 2015-03-16 15:17:07 SQL02 exe=183930 fail=0 row=323 ela=18281 ms avg=99 us 2015-03-16 15:17:07 SQL02 1 ms exec= 183930, ela= 18281 ms, avg= 99 us, pct=100,100 2015-03-16 15:17:07 SQL03 exe=183930 fail=0 row=148 ela=15336 ms avg=83 us 2015-03-16 15:17:07 SQL03 1 ms exec= 183930, ela= 15336 ms, avg= 83 us, pct=100,100 2015-03-16 15:17:07 SQL04 exe=183930 fail=0 row=175 ela=14827 ms avg=80 us 2015-03-16 15:17:07 SQL04 1 ms exec= 183930, ela= 14827 ms, avg= 80 us, pct=100,100 2015-03-16 15:18:07 ======================== mysql_test ======================== 2015-03-16 15:18:07 SQL02 exe=205490 fail=0 row=387 ela=18234 ms avg=88 us 2015-03-16 15:18:07 SQL02 1 ms exec= 205489, ela= 18233 ms, avg= 88 us, pct= 99, 99 2015-03-16 15:18:07 SQL02 2 ms exec= 1, ela= 1 ms, avg= 1422 us, pct= 0,100 2015-03-16 15:18:07 SQL03 exe=205490 fail=0 row=189 ela=15323 ms avg=74 us 2015-03-16 15:18:07 SQL03 1 ms exec= 205490, ela= 15323 ms, avg= 74 us, pct=100,100 2015-03-16 15:18:07 SQL04 exe=205490 fail=0 row=198 ela=14794 ms avg=71 us 2015-03-16 15:18:07 SQL04 1 ms exec= 205490, ela= 14794 ms, avg= 71 us, pct=100,100 2015-03-16 15:19:07 ======================== mysql_test ======================== 2015-03-16 15:19:07 SQL02 exe=220145 fail=0 row=454 ela=18235 ms avg=82 us 2015-03-16 15:19:07 SQL02 1 ms exec= 220145, ela= 18235 ms, avg= 82 us, pct=100,100 2015-03-16 15:19:07 SQL03 exe=220145 fail=0 row=211 ela=15329 ms avg=69 us 2015-03-16 15:19:07 SQL03 1 ms exec= 220145, ela= 15329 ms, avg= 69 us, pct=100,100 2015-03-16 15:19:07 SQL04 exe=220145 fail=0 row=243 ela=14790 ms avg=67 us 2015-03-16 15:19:07 SQL04 1 ms exec= 220145, ela= 14790 ms, avg= 67 us, pct=100,100 2015-03-16 15:20:07 ======================== mysql_test ======================== 2015-03-16 15:20:07 SQL02 exe=200138 fail=0 row=417 ela=18272 ms avg=91 us 2015-03-16 15:20:07 SQL02 1 ms exec= 200138, ela= 18272 ms, avg= 91 us, pct=100,100 2015-03-16 15:20:07 SQL03 exe=200138 fail=0 row=205 ela=15347 ms avg=76 us 2015-03-16 15:20:07 SQL03 1 ms exec= 200138, ela= 15347 ms, avg= 76 us, pct=100,100 2015-03-16 15:20:07 SQL04 exe=200138 fail=0 row=213 ela=14799 ms avg=73 us 2015-03-16 15:20:07 SQL04 1 ms exec= 200138, ela= 14799 ms, avg= 73 us, pct=100,100 ^C2015-03-16 15:20:09 ======================== mysql_test ======================== 2015-03-16 15:20:09 SQL02 exe=7042 fail=0 row=11 ela=578 ms avg=82 us 2015-03-16 15:20:09 SQL02 1 ms exec= 7042, ela= 578 ms, avg= 82 us, pct=100,100 2015-03-16 15:20:09 SQL03 exe=7042 fail=0 row=4 ela=484 ms avg=68 us 2015-03-16 15:20:09 SQL03 1 ms exec= 7042, ela= 484 ms, avg= 68 us, pct=100,100 2015-03-16 15:20:09 SQL04 exe=7042 fail=0 row=7 ela=467 ms avg=66 us 2015-03-16 15:20:09 SQL04 1 ms exec= 7042, ela= 467 ms, avg= 66 us, pct=100,100 2015-03-16 15:20:09 Total tran=957190=3170/s, qtps=2871570=9511/s, ela=300777 ms, avg=314 us Summary: SQL02 exec=957190, rows=1878=0/e, avg=95 us Summary: SQL03 exec=957190, rows=900=0/e, avg=80 us Summary: SQL04 exec=957190, rows=979=0/e, avg=77 us Summary: exec=3170/s, qtps=9511/s

aricy commented 9 years ago

热点隔离的作用比较明显。