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

1.前言

前文中我们介绍了HAProxy的安装布置(参考 http://blog.csdn.net/wjf870128/article/details/45221359)和Percona XtraDB Cluster的安装布置( http://blog.csdn.net/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）解压缩软件包

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

[root@mysql-rep01 sysbench]# cd sysbench-0.4.8

3）安装automake

[root@mysql-rep01 sysbench-0.5]# yum install automake

4）安装libtool

[root@mysql-rep01 Packages]# yum install libtool

5）安装mysql devel和shared支持包已支持sysbench的mysql环境

[root@mysql-rep01 software]# rpm -ivh MySQL-devel-advanced-5.6.23-1.el6.i686.rpm
Preparing...                ########################################### [100%]
   1:MySQL-devel-advanced   ########################################### [100%]

[root@mysql-rep01 software]# rpm -ivh MySQL-shared-advanced-5.6.23-1.el6.i686.rpm

[root@mysql-rep01 software]# rpm -ivh MySQL-shared-compat-advanced-5.6.23-1.el6.i686.rpm

6）安装sysbench软件包

[root@mysql-rep01 sysbench-0.5]# chmod u+x autogen.sh

[root@mysql-rep01 sysbench-0.5]# ./autogen.sh 

[root@mysql-rep01 sysbench-0.5]# ./configure --with-mysql-includes=/usr/include/mysql/ --with-mysql-libs=/usr/lib/mysql/

[root@mysql-rep01 sysbench-0.5]# make

[root@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）创建测试数据

[root@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数据库

[root@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关闭掉在查看状态

[root@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端口

[root@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数据库查看

[root@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能够实现节点的故障检测和根据相应算法进行负载均衡。