oneproxy 性能测试

[huhongbo]

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

[aricy]

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

[aricy]

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]

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

[aricy]

配置完成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)

slave节点(10.70.99.142)

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

至此已经完成了简单部署

[aricy]

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]

以下为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]

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