使用sysbench检测HAProxy对于Percona XtraDB Cluster的负载均衡和故障检测

evenno

1.前言

前文中我们介绍了HAProxy的安装布置(参考http://blog.iyunv.com/wjf870128/article/details/45221359)和Percona XtraDB Cluster的安装布置(http://blog.iyunv.com/wjf870128/article/details/45176011)。下面我们使用sysbench来检测下HAProxy是否能够实现负载均衡和对于集群中节点故障的请求转移。


2.sysbench安装

1）环境：


OS：redhat 6.5 32位
software：sysbench-0.4.8.tar.gz
ip addr：192.168.163.8(已经配置好haproxy)


2）解压缩软件包


[iyunv@mysql-rep01 sysbench]# tar -zxvf sysbench-0.4.8.tar.gz
[iyunv@mysql-rep01 sysbench]# cd sysbench-0.4.8


3）安装automake
[iyunv@mysql-rep01 sysbench-0.5]# yum install automake


4）安装libtool
[iyunv@mysql-rep01 Packages]# yum install libtool


5）安装mysql devel和shared支持包已支持sysbench的mysql环境
[iyunv@mysql-rep01 software]# rpm -ivh MySQL-devel-advanced-5.6.23-1.el6.i686.rpm
Preparing...                ########################################### [100%]
1:MySQL-devel-advanced   ########################################### [100%][iyunv@mysql-rep01 software]# rpm -ivh MySQL-shared-advanced-5.6.23-1.el6.i686.rpm
[iyunv@mysql-rep01 software]# rpm -ivh MySQL-shared-compat-advanced-5.6.23-1.el6.i686.rpm


6）安装sysbench软件包
[iyunv@mysql-rep01 sysbench-0.5]# chmod u+x autogen.sh[iyunv@mysql-rep01 sysbench-0.5]# ./autogen.sh
　　

[iyunv@mysql-rep01 sysbench-0.5]# ./configure --with-mysql-includes=/usr/include/mysql/ --with-mysql-libs=/usr/lib/mysql/[iyunv@mysql-rep01 sysbench-0.5]# make[iyunv@mysql-rep01 sysbench-0.5]# make install
3.测试HAProxy分发

1）我们的haproxy的配置文件如下：
global
log 127.0.0.1 local0
log 127.0.0.1 local1 notice
maxconn 4096
user nobody
group nobody
daemon
defaults
log global
mode http
option tcplog
option dontlognull
retries 3
option redispatch
maxconn 2000
timeout connect 5000
timeout client 50000
timeout server 50000
frontend pxc-front
bind *:3307
mode tcp
default_backend pxc-back
frontend stats-front
bind *:80
mode http
default_backend stats-back
frontend pxc-onenode-front
bind *:3306
mode tcp
default_backend pxc-onenode-back
backend pxc-back
mode tcp
balance roundrobin
option httpchk
server mysql-pxc01 192.168.163.6:3306 check port 9200 inter 12000 rise 3 fall 3
server mysql-pxc02 192.168.163.7:3306 check port 9200 inter 12000 rise 3 fall 3
backend stats-back
mode http
balance roundrobin
stats uri /haproxy/stats
stats auth pxcstats:secret
backend pxc-onenode-back
mode tcp
balance leastconn
option httpchk
server mysql-pxc01 192.168.163.6:3306 check port 9200 inter 12000 rise 3 fall 3
server mysql-pxc02 192.168.163.7:3306 check port 9200 inter 12000 rise 3 fall 3

2）在我们的percona集群中创建测试数据库和测试用户


mysql> create database sbtest;
Query OK, 1 row affected (0.39 sec)
mysql> grant all on sbtest.* to 'sbtest'@'%' identified by 'sbpass';
Query OK, 0 rows affected (0.25 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)


3）创建测试数据
[iyunv@mysql-rep01 db]# sysbench --test=/software/sysbench/sysbench-0.5/sysbench/tests/db/oltp.lua --mysql-table-engine=innodb --mysql-host=127.0.0.1 --mysql-port=3307 --mysql-user=sbtest --mysql-password=sbpass --oltp-table-size=10000 prepare
sysbench 0.5:  multi-threaded system evaluation benchmark
Creating table 'sbtest1'...
Inserting 10000 records into 'sbtest1'

4）使用3306端口访问mysql数据库
[iyunv@mysql-rep01 db]# sysbench --test=/software/sysbench/sysbench-0.5/sysbench/tests/db/oltp.lua --mysql-table-engine=innodb --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=sbtest --mysql-password=sbpass --oltp-table-size=10000 --num-threads=8 run
sysbench 0.5:  multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 8
Random number generator seed is 0 and will be ignored

Threads started!登录haproxy的状态界面 http://192.168.163.8/haproxy/stats查看状态


　　分发成功
　　5）我们将主节点mysql-pxc01关闭掉在查看状态
　　

[iyunv@mysql-pxc01 ~]# service mysql stop
Shutting down MySQL (Percona XtraDB Cluster)..........[  OK  ]此时我们的sysbench报警退出...　　
　　

ALERT: failed to execute MySQL query: `UPDATE sbtest1 SET c='48430239815-39636634349-44303135689-38090726026-22121476891-44178343736-66628055146-51151353374-76675391132-92944723344' WHERE id=5000`:
ALERT: Error 1317 Query execution was interrupted
FATAL: failed to execute function `event': (null)
ALERT: failed to execute MySQL query: `SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN 3535 AND 3535+99 ORDER BY c`:
ALERT: Error 1047 Unknown command
FATAL: failed to execute function `event': (null)
ALERT: failed to execute MySQL query: `SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN 5050 AND 5050+99 ORDER BY c`:
ALERT: Error 1047 Unknown command
FATAL: failed to execute function `event': (null)
ALERT: failed to execute MySQL query: `SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN 5010 AND 5010+99 ORDER BY c`:
ALERT: Error 1047 Unknown command
FATAL: failed to execute function `event': (null)

再次执行的时候发现，haproxy识别到节点1关闭掉，状态如下　　



　　6）我们再次打开节点1，看看haproxy在leastconn的分发






　　由于我们是双节点的集群，节点1启动的时候需要抓取节点2的日志数据，备节点2不对外服务，所以连接为0。
　　同步完成后状态转化为：






　　7）节点正常后，由于我们使用的是leastconn，节点1上暂时没有连接，再次运行命令，创建5个线程连接3306端口
　　

[iyunv@mysql-rep01 ~]# sysbench --test=/software/sysbench/sysbench-0.5/sysbench/tests/db/oltp.lua --mysql-table-engine=innodb --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=sbtest --mysql-password=sbpass --oltp-table-size=10000 --num-threads=5 run
sysbench 0.5:  multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 5
Random number generator seed is 0 and will be ignored

Threads started!状态如下：　　



　　可见leastconn的算法生效。
　　


　　

8）使用3307端口访问mysql数据库查看
[iyunv@mysql-rep01 db]# sysbench --test=/software/sysbench/sysbench-0.5/sysbench/tests/db/oltp.lua --mysql-table-engine=innodb --mysql-host=127.0.0.1 --mysql-port=3307 --mysql-user=sbtest --mysql-password=sbpass --oltp-table-size=10000 --num-threads=8 run
sysbench 0.5:  multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 8
Random number generator seed is 0 and will be ignored

Threads started!




发现haproxy成功分发到各个server中

　　
　　9）断开节点1



　　10)恢复节点1






　　已有的连接不会进行roundrobin循环分配
　　11）我们再次建立6个连接到3307端口



　　发现每个节点各自分配了3个新连接。
　　


　　HAProxy能够实现节点的故障检测和根据相应算法进行负载均衡。