首页 > > 网络编程 > Mysql >

探究MySQL MGR的读写分离

2018-07-17 04:07:58来源：博客园 阅读 ()

新老客户大回馈,云服务器低至5折

1：现有环境
机器MySQL环境
172.16.128.240 MGR NODE1 MGR
172.16.128.241 MGR NODE2 MGR
172.16.128.242 MGR NODE3 MGR
172.16.128.239 VIP keepalived

环境介绍，VIP 172.16.128.239在172.16.128.240上，我们是基于172.16.128.240和172.16.128.241做的keepalived。MGR为多主模式。我们考虑使用中间件能够实现MGR的读写分离。中间件选型为Atlas,Atlas是360团队开源的一套基于MySQL-Proxy基础之上的代理，修改了MySQL-Proxy的一些BUG，并且进行了优化。

2：安装Atlas
[root@YC-ss1 tmp]# rpm -ivh Atlas-2.2.1.el6.x86_64.rpm 
Preparing...                          ################################# [100%]
Updating / installing...
   1:Atlas-2.2.1-1                    ################################# [100%]
[root@YC-ss1 mysql-proxy]# pwd
/usr/local/mysql-proxy
[root@YC-ss1 mysql-proxy]# ls
bin  conf  lib  log

 

我们看一下，安装完Atlas 以后会生成四个文件夹。首先我们要配置密码的加密：

[root@YC-ss1 bin]# ./encrypt 123456 
/iZxz+0GRoA=

 

我的root@%账号密码就是123456，比较简单。我们先记录这个加密字符串，等会要在配置文件中配置。配置配置文件

[root@YC-ss1 conf]# cp test.cnf atlas.cnf
[root@YC-ss1 conf]# cat atlas.cnf 
[mysql-proxy]


#带#号的为非必需的配置项目

#管理接口的用户名
admin-username = user

#管理接口的密码
admin-password = pwd

#Atlas后端连接的MySQL主库的IP和端口，可设置多项，用逗号分隔
proxy-backend-addresses = 172.16.128.239:3306

#Atlas后端连接的MySQL从库的IP和端口，@后面的数字代表权重，用来作负载均衡，若省略则默认为1，可设置多项，用逗号分隔
proxy-read-only-backend-addresses = 172.16.128.240:3306,172.16.128.241:3306,172.16.128.242:3306

#用户名与其对应的加密过的MySQL密码，密码使用PREFIX/bin目录下的加密程序encrypt加密，下行的user1和user2为示例，将其替换为你的MySQL的用户名和加密密码！
pwds = root:/iZxz+0GRoA=

#设置Atlas的运行方式，设为true时为守护进程方式，设为false时为前台方式，一般开发调试时设为false，线上运行时设为true,true后面不能有空格。
daemon = true

#设置Atlas的运行方式，设为true时Atlas会启动两个进程，一个为monitor，一个为worker，monitor在worker意外退出后会自动将其重启，设为false时只有worker，没有monitor，一般开发调试时设为false，线上运行时设为true,true后面不能有空格。
keepalive = true

#工作线程数，对Atlas的性能有很大影响，可根据情况适当设置
event-threads = 16

#日志级别，分为message、warning、critical、error、debug五个级别
log-level = message

#日志存放的路径
log-path = /usr/local/mysql-proxy/log

#SQL日志的开关，可设置为OFF、ON、REALTIME，OFF代表不记录SQL日志，ON代表记录SQL日志，REALTIME代表记录SQL日志且实时写入磁盘，默认为OFF
#sql-log = OFF

#慢日志输出设置。当设置了该参数时，则日志只输出执行时间超过sql-log-slow（单位：ms)的日志记录。不设置该参数则输出全部日志。
#sql-log-slow = 10

#实例名称，用于同一台机器上多个Atlas实例间的区分
#instance = test

#Atlas监听的工作接口IP和端口
proxy-address = 0.0.0.0:6002

#Atlas监听的管理接口IP和端口
admin-address = 0.0.0.0:6003

#分表设置，此例中person为库名，mt为表名，id为分表字段，3为子表数量，可设置多项，以逗号分隔，若不分表则不需要设置该项
#tables = person.mt.id.3

#默认字符集，设置该项后客户端不再需要执行SET NAMES语句
#charset = utf8

#允许连接Atlas的客户端的IP，可以是精确IP，也可以是IP段，以逗号分隔，若不设置该项则允许所有IP连接，否则只允许列表中的IP连接
#client-ips = 127.0.0.1, 192.168.1

#Atlas前面挂接的LVS的物理网卡的IP(注意不是虚IP)，若有LVS且设置了client-ips则此项必须设置，否则可以不设置
#lvs-ips = 192.168.1.1
 

 

启动Atlas

[root@YC-ss1 bin]# ./mysql-proxyd  atlas start
OK: MySQL-Proxy of atlas is started

 

OK，现在我们的Atlas已经启动了。我们登录查看一下信息：

[root@YC-ss1 conf]#  mysql -h127.0.0.1 -P6003 -uuser -ppwd
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.99-agent-admin

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
(user@127.0.0.1) [(none)]> SELECT * FROM help
    -> ;
+----------------------------+---------------------------------------------------------+
| command                    | description                                             |
+----------------------------+---------------------------------------------------------+
| SELECT * FROM help         | shows this help                                         |
| SELECT * FROM backends     | lists the backends and their state                      |
| SET OFFLINE $backend_id    | offline backend server, $backend_id is backend_ndx's id |
| SET ONLINE $backend_id     | online backend server, ...                              |
| ADD MASTER $backend        | example: "add master 127.0.0.1:3306", ...               |
| ADD SLAVE $backend         | example: "add slave 127.0.0.1:3306", ...                |
| REMOVE BACKEND $backend_id | example: "remove backend 1", ...                        |
| SELECT * FROM clients      | lists the clients                                       |
| ADD CLIENT $client         | example: "add client 192.168.1.2", ...                  |
| REMOVE CLIENT $client      | example: "remove client 192.168.1.2", ...               |
| SELECT * FROM pwds         | lists the pwds                                          |
| ADD PWD $pwd               | example: "add pwd user:raw_password", ...               |
| ADD ENPWD $pwd             | example: "add enpwd user:encrypted_password", ...       |
| REMOVE PWD $pwd            | example: "remove pwd user", ...                         |
| SAVE CONFIG                | save the backends to config file                        |
| SELECT VERSION             | display the version of Atlas                            |
+----------------------------+---------------------------------------------------------+
16 rows in set (0.00 sec)

(user@127.0.0.1) [(none)]>  SELECT * FROM backends;
+-------------+---------------------+-------+------+
| backend_ndx | address             | state | type |
+-------------+---------------------+-------+------+
|           1 | 172.16.128.239:3306 | up    | rw   |
|           2 | 172.16.128.240:3306 | up    | ro   |
|           3 | 172.16.128.241:3306 | up    | ro   |
|           4 | 172.16.128.242:3306 | up    | ro   |
+-------------+---------------------+-------+------+
4 rows in set (0.00 sec)

 

然后使用服务端口访问一下：

[root@YC-ss1 ~]# mysql -h127.0.0.1 -P6002 -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.0.81-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

(root@127.0.0.1) [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mxqteset           |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

(root@127.0.0.1) [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|  1282403306 |
+-------------+
1 row in set (0.00 sec)

(root@127.0.0.1) [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|  1282413306 |
+-------------+
1 row in set (0.00 sec)

(root@127.0.0.1) [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|  1282423306 |
+-------------+
1 row in set (0.00 sec)

 

可以看到我们查询了三次。已经路由到了MGR的三个节点上了。读已经是没有问题的。写的话都会通过VIP:172.16.128.239来写。而且我们的VIP是会自动故障转移，也就是说保证是可写的。这样基本上就实现的读写分离。如果我们不想让240这台机器发生读，就可以把优先级配置的低一些，优先路由倒其他的机器。

3：压力测试

我们使用tpcc-mysql进行测试。先进行安装，下载地址：http://imysql.com/wp-content/uploads/2014/09/tpcc-mysql-src.tgz

[root@YC-ss1 tpccmysql]# gunzip tpcc-mysql-src.tgz
[root@YC-ss1 tpccmysql]#  tar xf tpcc-mysql-src.tar
[root@YC-ss1 tpccmysql]# cd src/
-bash: cd: src/: No such file or directory
[root@YC-ss1 tpccmysql]# ls
tpcc-mysql  tpcc-mysql-src.tar
[root@YC-ss1 tpccmysql]# cd tpcc-mysql
[root@YC-ss1 tpcc-mysql]# ls
add_fkey_idx.sql  count.sql  create_table.sql  drop_cons.sql  load.sh  README  schema2  scripts  src
[root@YC-ss1 tpcc-mysql]# cd src/
[root@YC-ss1 src]# make
cc -w -O2 -g -I. `mysql_config --include`  -c load.c
cc -w -O2 -g -I. `mysql_config --include`  -c support.c
cc load.o support.o `mysql_config --libs_r` -lrt -o ../tpcc_load
cc -w -O2 -g -I. `mysql_config --include`  -c main.c
cc -w -O2 -g -I. `mysql_config --include`  -c spt_proc.c
cc -w -O2 -g -I. `mysql_config --include`  -c driver.c
cc -w -O2 -g -I. `mysql_config --include`  -c sequence.c
cc -w -O2 -g -I. `mysql_config --include`  -c rthist.c
cc -w -O2 -g -I. `mysql_config --include`  -c neword.c
cc -w -O2 -g -I. `mysql_config --include`  -c payment.c
cc -w -O2 -g -I. `mysql_config --include`  -c ordstat.c
cc -w -O2 -g -I. `mysql_config --include`  -c delivery.c
cc -w -O2 -g -I. `mysql_config --include`  -c slev.c
cc main.o spt_proc.o driver.o support.o sequence.o rthist.o neword.o payment.o ordstat.o delivery.o slev.o `mysql_config --libs_r` -lrt -o ../tpcc_start
[root@YC-ss1 src]# cd ..
[root@YC-ss1 tpcc-mysql]# ls
add_fkey_idx.sql  count.sql  create_table.sql  drop_cons.sql  load.sh  README  schema2  scripts  src  tpcc_load  tpcc_start

 

初始化压测数据：

(root@127.0.0.1) [tpccmysql]> create database tpccmysql;
Query OK, 1 row affected (0.02 sec)

(root@127.0.0.1) [tpccmysql]> use tpccmysql
Database changed
(root@127.0.0.1) [tpccmysql]> source /tmp/create_table.sql;
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected, 1 warning (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.18 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

 

但是没有外键的话是不能压测的，及时压测很多也是失败的。唉，坑爹，还好有一个TPCC去除外键的，这个应该是叶大大搞的，github地址：https://github.com/yejr/tpcc-mysql-autoinc-pk

我们使用去除外键的TPCC进行压测，先进行安装

[root@YC-ss1 tpccmysql]# cd tpcc-mysql-autoinc-pk-master
[root@YC-ss1 tpcc-mysql-autoinc-pk-master]# ls
add_fkey_idx.sql  create_table-autoinc-pk.sql  load.sh  README  README.md  run_tpcc.sh  schema2  scripts  src  tpcc_load_parallel.sh
[root@YC-ss1 tpcc-mysql-autoinc-pk-master]# cd src/
[root@YC-ss1 src]# make
cc -w -O2 -g -I. `mysql_config --include`  -c load.c
cc -w -O2 -g -I. `mysql_config --include`  -c support.c
cc load.o support.o `mysql_config --libs_r` -lrt -o ../tpcc_load
cc -w -O2 -g -I. `mysql_config --include`  -c main.c
cc -w -O2 -g -I. `mysql_config --include`  -c spt_proc.c
cc -w -O2 -g -I. `mysql_config --include`  -c driver.c
cc -w -O2 -g -I. `mysql_config --include`  -c sequence.c
cc -w -O2 -g -I. `mysql_config --include`  -c rthist.c
cc -w -O2 -g -I. `mysql_config --include`  -c neword.c
cc -w -O2 -g -I. `mysql_config --include`  -c payment.c
cc -w -O2 -g -I. `mysql_config --include`  -c ordstat.c
cc -w -O2 -g -I. `mysql_config --include`  -c delivery.c
cc -w -O2 -g -I. `mysql_config --include`  -c slev.c
cc main.o spt_proc.o driver.o support.o sequence.o rthist.o neword.o payment.o ordstat.o delivery.o slev.o `mysql_config --libs_r` -lrt -o ../tpcc_start
[root@YC-ss1 src]# ls
delivery.c  driver.c  load.c       load.o  main.o    neword.c  ordstat.c  parse_port.h  payment.o  rthist.h  sequence.c  sequence.o  slev.o      spt_proc.h  support.c  tpc.h
delivery.o  driver.o  load.c.orig  main.c  Makefile  neword.o  ordstat.o  payment.c     rthist.c   rthist.o  sequence.h  slev.c      spt_proc.c  spt_proc.o  support.o  trans_if.h
[root@YC-ss1 src]# cd ..
[root@YC-ss1 tpcc-mysql-autoinc-pk-master]# ls
add_fkey_idx.sql  create_table-autoinc-pk.sql  load.sh  README  README.md  run_tpcc.sh  schema2  scripts  src  tpcc_load  tpcc_load_parallel.sh  tpcc_start

 

初始化数据

(root@127.0.0.1) [(none)]> create database tpcc;
Query OK, 1 row affected (0.07 sec)

(root@127.0.0.1) [(none)]> use tpcc
Database changed
(root@127.0.0.1) [tpcc]> source /home/tpccmysql/tpcc-mysql-autoinc-pk-master/create_table-autoinc-pk.sql;

 

使用tpcc_load加载数据：

[root@YC-ss1 tpcc-mysql-autoinc-pk-master]# ./tpcc_load 127.0.0.1:6002  tpcc root "123456" 30
*************************************
*** ###easy### TPC-C Data Loader  ***
*************************************
<Parameters>
     [server]: 127.0.0.1
     [port]: 6002
     [DBname]: tpcc
       [user]: root
       [pass]: 123456
  [warehouse]: 30
TPCC Data Load Started...
Loading Item 
.................................................. 50000
.................................................. 100000

 

这个过程有点漫长，可以去楼下抽支烟。然后进行压力测试，测试一个小时，这个时间也有点久，可以下楼买个冰棍了。

[root@YC-ss1 tpcc-mysql-autoinc-pk-master]# ./tpcc_start -h 127.0.0.1 -P 6002 -d tpcc -u root -p 123456 -w 30 -c 8 -r 120 -l 3600  -f tpcc_0705
***************************************
*** ###easy### TPC-C Load Generator ***
***************************************
option h with value '127.0.0.1'
option P with value '6002'
option d with value 'tpcc'
option u with value 'root'
option p with value '123456'
option w with value '30'
option c with value '8'
option r with value '120'
option l with value '3600'
option f with value 'tpcc_0705'
<Parameters>
     [server]: 127.0.0.1
     [port]: 6002
     [DBname]: tpcc
       [user]: root
       [pass]: 123456
  [warehouse]: 30
 [connection]: 8
     [rampup]: 120 (sec.)
    [measure]: 3600 (sec.)

 

等待擦测试结果

STOPPING THREADS........

<Raw Results>
  [0] sc:24209  lt:1  rt:0  fl:0 
  [1] sc:24167  lt:0  rt:0  fl:0 
  [2] sc:2421  lt:1  rt:0  fl:0 
  [3] sc:2420  lt:0  rt:0  fl:0 
  [4] sc:2423  lt:0  rt:0  fl:0 
 in 3600 sec.

<Raw Results2(sum ver.)>
  [0] sc:24209  lt:1  rt:0  fl:0 
  [1] sc:24210  lt:0  rt:0  fl:0 
  [2] sc:2421  lt:1  rt:0  fl:0 
  [3] sc:2421  lt:0  rt:0  fl:0 
  [4] sc:2423  lt:0  rt:0  fl:0 

<Constraint Check> (all must be [OK])
 [transaction percentage]
        Payment: 43.43% (>=43.0%) [OK]
   Order-Status: 4.35% (>= 4.0%) [OK]
       Delivery: 4.35% (>= 4.0%) [OK]
    Stock-Level: 4.35% (>= 4.0%) [OK]
 [response time (at least 90% passed)]
      New-Order: 100.00%  [OK]
        Payment: 100.00%  [OK]
   Order-Status: 99.96%  [OK]
       Delivery: 100.00%  [OK]
    Stock-Level: 100.00%  [OK]

<TpmC>
                 403.500 TpmC

 

我们可以看到在我这个级别压测下，Atlas+MGR还是很稳定的。下面我们看下故障转移

4：故障转移的测试

往上面回顾一下我们的机器环境

机器MySQL环境
172.16.128.240 MGR NODE1 MGR
172.16.128.241 MGR NODE2 MGR
172.16.128.242 MGR NODE3 MGR
172.16.128.239 VIP keepalived

现在我们Atlas提供的服务端口是172.16.128.242:6002，我们模拟NODE1节点故障 ，然后看一下压测的结果最终结果是如何的。

[root@YC-ss1 tpcc-mysql-autoinc-pk-master]# ./tpcc_start -h 127.0.0.1 -P 6002 -d tpcc -u root -p 123456 -w 30 -c 8 -r 120 -l 300  -f tpcc_0706
***************************************
*** ###easy### TPC-C Load Generator ***
***************************************
option h with value '127.0.0.1'
option P with value '6002'
option d with value 'tpcc'
option u with value 'root'
option p with value '123456'
option w with value '30'
option c with value '8'
option r with value '120'
option l with value '300'
option f with value 'tpcc_0706'
<Parameters>
     [server]: 127.0.0.1
     [port]: 6002
     [DBname]: tpcc
       [user]: root
       [pass]: 123456
  [warehouse]: 30
 [connection]: 8
     [rampup]: 120 (sec.)
    [measure]: 300 (sec.)

RAMP-UP TIME.(120 sec.)

 

第二部关闭NODE1，

2006, HY000, MySQL server has gone away
payment 4:1
2006, HY000, MySQL server has gone away
payment 4:1
2006, HY000, MySQL server has gone away
payment 4:1
2006, HY000, MySQL server has gone away

 

全部都TM的gone away了，也真是无奈的，我们在重新开始压测，发现是没问题的，

[root@YC-ss1 tpcc-mysql-autoinc-pk-master]# ./tpcc_start -h 127.0.0.1 -P 6002 -d tpcc -u root -p 123456 -w 30 -c 8 -r 120 -l 300  -f tpcc_0706
***************************************
*** ###easy### TPC-C Load Generator ***
***************************************
option h with value '127.0.0.1'
option P with value '6002'
option d with value 'tpcc'
option u with value 'root'
option p with value '123456'
option w with value '30'
option c with value '8'
option r with value '120'
option l with value '300'
option f with value 'tpcc_0706'
<Parameters>
     [server]: 127.0.0.1
     [port]: 6002
     [DBname]: tpcc
       [user]: root
       [pass]: 123456
  [warehouse]: 30
 [connection]: 8
     [rampup]: 120 (sec.)
    [measure]: 300 (sec.)

RAMP-UP TIME.(120 sec.)

MEASURING START.

  10, 905(0):1.694|4.648, 905(0):0.449|4.042, 91(0):0.217|0.562, 90(0):2.178|3.151, 90(0):5.098|10.983
  20, 724(0):1.750|2.948, 725(0):0.548|1.545, 73(0):0.173|0.230, 72(0):1.947|2.309, 73(0):4.700|5.217
  30, 1050(0):1.746|4.647, 1046(0):0.372|0.746, 104(0):0.163|0.258, 105(0):1.843|1.966, 105(0):5.323|5.616
  40, 917(0):1.754|4.656, 918(0):0.392|0.985, 92(0):0.190|0.191, 91(0):2.042|2.340, 92(0):5.574|6.931
  50, 951(0):1.688|2.792, 950(1):0.440|5.618, 96(0):0.206|0.373, 96(0):2.240|3.440, 95(0):4.809|4.985
  60, 965(0):1.764|2.737, 966(0):0.393|0.836, 96(0):0.222|0.517, 97(0):2.220|2.448, 95(0):4.989|4.990
  70, 940(0):1.589|2.525, 941(0):0.463|1.766, 94(0):0.164|0.263, 94(0):2.446|3.339, 96(0):5.512|5.704
  80, 746(2):1.671|7.135, 742(1):0.445|5.139, 75(0):0.229|0.324, 74(0):2.353|2.653, 74(0):5.342|5.454
  90, 1022(0):1.784|4.030, 1022(0):0.421|1.311, 102(0):0.311|0.479, 102(0):2.093|3.696, 102(0):5.810|6.025
 100, 1012(0):1.809|2.774, 1012(0):0.436|0.961, 101(0):0.207|0.381, 102(0):2.135|2.262, 101(0):5.867|5.931
 110, 928(0):1.651|2.960, 930(0):0.397|1.347, 93(0):0.193|0.371, 92(0):2.157|3.873, 93(0):5.445|6.339
 120, 1000(0):1.670|2.486, 1001(0):0.408|0.888, 99(0):0.257|0.405, 100(0):1.943|2.189, 100(0):5.292|5.770
 130, 992(0):1.749|4.123, 990(0):0.463|1.120, 100(0):0.206|0.277, 99(0):1.742|1.759, 100(0):4.674|5.843
 140, 868(1):1.893|7.145, 864(0):0.447|1.527, 86(0):0.188|0.221, 87(0):2.262|2.292, 86(0):4.586|4.684
 150, 1024(0):1.641|2.351, 1027(0):0.415|0.680, 103(0):0.153|0.195, 102(0):1.936|5.337, 102(0):4.877|5.004
 
<Raw Results>
  [0] sc:28932  lt:6  rt:0  fl:0 
  [1] sc:28921  lt:2  rt:0  fl:0 
  [2] sc:2894  lt:0  rt:0  fl:0 
  [3] sc:2893  lt:0  rt:0  fl:0 
  [4] sc:2894  lt:0  rt:0  fl:0 
 in 300 sec.

<Raw Results2(sum ver.)>
  [0] sc:28932  lt:6  rt:0  fl:0 
  [1] sc:28934  lt:2  rt:0  fl:0 
  [2] sc:2894  lt:0  rt:0  fl:0 
  [3] sc:2893  lt:0  rt:0  fl:0 
  [4] sc:2894  lt:0  rt:0  fl:0 

<Constraint Check> (all must be [OK])
 [transaction percentage]
        Payment: 43.47% (>=43.0%) [OK]
   Order-Status: 4.35% (>= 4.0%) [OK]
       Delivery: 4.35% (>= 4.0%) [OK]
    Stock-Level: 4.35% (>= 4.0%) [OK]
 [response time (at least 90% passed)]
      New-Order: 99.98%  [OK]
        Payment: 99.99%  [OK]
   Order-Status: 100.00%  [OK]
       Delivery: 100.00%  [OK]
    Stock-Level: 100.00%  [OK]

<TpmC>
                 5787.600 TpmC

 

我们再看一下Atlas的管理信息

(user@127.0.0.1) [(none)]> SELECT * FROM backends;
+-------------+---------------------+-------+------+
| backend_ndx | address             | state | type |
+-------------+---------------------+-------+------+
|           1 | 172.16.128.239:3306 | up    | rw   |
|           2 | 172.16.128.240:3306 | up    | ro   |
|           3 | 172.16.128.241:3306 | up    | ro   |
|           4 | 172.16.128.242:3306 | up    | ro   |
+-------------+---------------------+-------+------+
4 rows in set (0.00 sec)

发现有问题的NODE1节点的状态居然还是UP，也就是说我们还要自己想办法通知Atlas我这个节点挂了，要修改配置信息。不过最起码说明读写分离是可以做到了。所以说这个还是有点不靠谱的啊，我们没办法实现。我们选材mycat试试

MyCat读写分离高可用测试

安装使用mycat，贴一下mycat的配置文件

[root@YC-ss1 conf]# cat schema.xml 
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

    <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
        <table name="customer" primaryKey="aid"  dataNode="tp1" />
        <table name="district" primaryKey="aid"  dataNode="tp1" />
        <table name="history" primaryKey="aid"  dataNode="tp1" />
        <table name="item" primaryKey="aid"  dataNode="tp1" />
        <table name="new_orders" primaryKey="aid"  dataNode="tp1" />
        <table name="order_line" primaryKey="aid"  dataNode="tp1" />
        <table name="orders" primaryKey="aid"  dataNode="tp1" />
        <table name="stock" primaryKey="aid"  dataNode="tp1" />
        <table name="warehouse" primaryKey="aid"  dataNode="tp1" />
    </schema>
    
    
    <dataNode name="tp1" dataHost="dbhost239" database="tpcc" />

    <dataHost name="dbhost239" maxCon="1000" minCon="10" balance="1"
                writeType="0" dbType="mysql" dbDriver="native" switchType="-1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                <writeHost host="hostM1" url="172.16.128.239:3306" user="root"
                        password="123456">
                </writeHost>
                <writeHost host="hostS1" url="172.16.128.240:3306" user="root"
                        password="123456" />
                <writeHost host="hostS2" url="172.16.128.241:3306" user="root"                      
                        password="123456" />
                <writeHost host="hostS3" url="172.16.128.242:3306" user="root"                      
                        password="123456" />
    </dataHost>

</mycat:schema>

 

不在赘述，直接通过节点一load数据

[root@YC-ss1 tpcc-mysql-autoinc-pk-master]# ./tpcc_load 172.16.128.239:3306  tpcc root "123456" 10
*************************************
*** ###easy### TPC-C Data Loader  ***
*************************************
<Parameters>
     [server]: 172.16.128.239
     [port]: 3306
     [DBname]: tpcc
       [user]: root
       [pass]: 123456
  [warehouse]: 10
TPCC Data Load Started...
Loading Item 
.................................................. 50000
.................................................. 100000
Item Done. 
Loading Warehouse 
Loading Stock Wid=1
.................................................. 50000
.................................................. 100000
 Stock Done.
Loading District
Loading Stock Wid=2
.................................................. 50000
.................................................. 100000

 

然后先压测：

[root@YC-ss1 tpcc-mysql-autoinc-pk-master]# ./tpcc_start -h 127.0.0.1 -P 8066 -d TESTDB -u root -p 123456 -w 10 -c 8 -r 120 -l 300  -f tpcc_0710

 

看一下压测结果

 250, 1320(0):1.793|4.080, 1320(0):0.370|1.357, 133(0):0.160|0.185, 131(0):1.993|2.155, 132(0):2.476|3.372
 260, 1154(0):1.695|3.316, 1153(0):0.371|1.664, 114(0):0.138|0.144, 116(0):1.936|3.117, 114(0):3.079|3.506
 270, 1076(0):1.780|3.807, 1076(0):0.359|0.706, 108(0):0.155|0.195, 107(0):1.830|3.335, 109(0):2.296|2.368
 280, 1094(0):1.682|2.435, 1092(0):0.362|1.352, 109(0):0.142|0.163, 110(0):1.963|2.259, 109(0):2.760|2.868
 290, 1264(0):1.668|2.191, 1269(0):0.339|0.744, 127(0):0.159|0.188, 127(0):1.847|1.943, 126(0):2.909|3.890
 300, 1169(0):1.605|4.055, 1166(0):0.336|1.421, 117(0):0.160|0.192, 117(0):1.874|3.247, 118(0):2.456|2.595

STOPPING THREADS........

<Raw Results>
  [0] sc:35560  lt:3  rt:0  fl:0 
  [1] sc:35563  lt:1  rt:0  fl:0 
  [2] sc:3557  lt:0  rt:0  fl:0 
  [3] sc:3557  lt:0  rt:0  fl:0 
  [4] sc:3557  lt:0  rt:0  fl:0 
 in 300 sec.

<Raw Results2(sum ver.)>
  [0] sc:35560  lt:3  rt:0  fl:0 
  [1] sc:35565  lt:1  rt:0  fl:0 
  [2] sc:3557  lt:0  rt:0  fl:0 
  [3] sc:3557  lt:0  rt:0  fl:0 
  [4] sc:3557  lt:0  rt:0  fl:0 

<Constraint Check> (all must be [OK])
 [transaction percentage]
        Payment: 43.48% (>=43.0%) [OK]
   Order-Status: 4.35% (>= 4.0%) [OK]
       Delivery: 4.35% (>= 4.0%) [OK]
    Stock-Level: 4.35% (>= 4.0%) [OK]
 [response time (at least 90% passed)]
      New-Order: 99.99%  [OK]
        Payment: 100.00%  [OK]
   Order-Status: 100.00%  [OK]
       Delivery: 100.00%  [OK]
    Stock-Level: 100.00%  [OK]

<TpmC>
                 7112.600 TpmC

 

由于大部分压测都是更新数据，所以都路由到了239VIP上。我们可以直接测一下读取

(root@127.0.0.1) [TESTDB]> select *,@@server_id from item limit 1;
+-----+------+---------+-----------------------+---------+-----------------------------------------+-------------+
| aid | i_id | i_im_id | i_name                | i_price | i_data                                  | @@server_id |
+-----+------+---------+-----------------------+---------+-----------------------------------------+-------------+
|  35 |    1 |    1320 | lUPdQrxNjkFvKSw698smv |   11.14 | jtIe2rjbaByDZp8wXr9QBbNFjV7VOIL8BdmBSTk |  1282403306 |
+-----+------+---------+-----------------------+---------+-----------------------------------------+-------------+
1 row in set (0.37 sec)

(root@127.0.0.1) [TESTDB]> select *,@@server_id from item limit 1;
+-----+------+---------+-----------------------+---------+-----------------------------------------+-------------+
| aid | i_id | i_im_id | i_name                | i_price | i_data                                  | @@server_id |
+-----+------+---------+-----------------------+---------+-----------------------------------------+-------------+
|  35 |    1 |    1320 | lUPdQrxNjkFvKSw698smv |   11.14 | jtIe2rjbaByDZp8wXr9QBbNFjV7VOIL8BdmBSTk |  1282413306 |
+-----+------+---------+-----------------------+---------+-----------------------------------------+-------------+
1 row in set (0.05 sec)
(root@127.0.0.1) [TESTDB]> select *,@@server_id from item limit 1;
+-----+------+---------+-----------------------+---------+-----------------------------------------+-------------+
| aid | i_id | i_im_id | i_name                | i_price | i_data                                  | @@server_id |
+-----+------+---------+-----------------------+---------+-----------------------------------------+-------------+
|  35 |    1 |    1320 | lUPdQrxNjkFvKSw698smv |   11.14 | jtIe2rjbaByDZp8wXr9QBbNFjV7VOIL8BdmBSTk |  1282423306 |
+-----+------+---------+-----------------------+---------+-----------------------------------------+-------------+
1 row in set (0.07 sec)

 

读的请求会路由倒三台机器上。现在假设240的主机挂掉，VIP239从240飘到241，我们看一下mycat会不会把连接有没有问题。 首先给一个持续的压测，

[root@YC-ss1 tpcc-mysql-autoinc-pk-master]# ./tpcc_start -h 127.0.0.1 -P 8066 -d TESTDB -u root -p 123456 -w 10 -c 8 -r 120 -l 1800

 

在压测的过程中我们停掉240主机，VIP会切换的241上。我们再看压测结果。

1184, HY000, Connection refused
payment 7:1
1184, HY000, Connection refused
neword 4:1
1184, HY000, Connection refused
payment 6:1
1184, HY000, Connection refused
payment 5:1
1184, HY000, Connection refused
payment 1:1
1184, HY000, Connection refused
payment 0:1
1184, HY000, Connection refused
neword 3:1
1184, HY000, Connection refused
payment 6:1
1184, HY000, Connection refused
neword 4:1
1184, HY000, Connection refused
payment 7:1
1184, HY000, Connection refused
  70, 723(2):1.654|47.728, 726(5):0.655|76.966, 72(0):0.164|0.298, 72(0):1.957|2.127, 72(0):2.550|2.571
  80, 1178(0):1.769|2.702, 1182(0):0.416|0.870, 118(0):0.159|0.288, 117(0):1.938|1.971, 118(0):2.769|3.452
  90, 1332(0):1.766|4.103, 1330(0):0.425|1.254, 134(0):0.175|0.178, 133(0):2.207|2.644, 134(0):3.027|3.508
 100, 901(0):1.822|2.738, 902(0):0.431|1.109, 90(0):0.152|0.158, 90(0):2.040|2.049, 90(0):2.991|3.176
 -------
 <Raw Results>
  [0] sc:22875  lt:3  rt:81654  fl:40 
  [1] sc:22879  lt:5  rt:87541  fl:40 
  [2] sc:2288  lt:0  rt:9995  fl:5 
  [3] sc:2287  lt:0  rt:13993  fl:7 
  [4] sc:2291  lt:0  rt:5997  fl:3 
 in 240 sec.

<Raw Results2(sum ver.)>
  [0] sc:22881  lt:3  rt:81655  fl:40 
  [1] sc:22881  lt:5  rt:87552  fl:40 
  [2] sc:2288  lt:0  rt:9995  fl:5 
  [3] sc:2287  lt:0  rt:13993  fl:7 
  [4] sc:2291  lt:0  rt:5997  fl:3 

<Constraint Check> (all must be [OK])
 [transaction percentage]
        Payment: 43.48% (>=43.0%) [OK]
   Order-Status: 4.35% (>= 4.0%) [OK]
       Delivery: 4.35% (>= 4.0%) [OK]
    Stock-Level: 4.35% (>= 4.0%) [OK]
 [response time (at least 90% passed)]
      New-Order: 99.99%  [OK]
        Payment: 99.98%  [OK]
   Order-Status: 100.00%  [OK]
       Delivery: 100.00%  [OK]
    Stock-Level: 100.00%  [OK]

<TpmC>
                 5719.500 TpmC

 

可以看到，在keepalived切换过程中，连接是失败的，但是最后VIP切换成功以后所有的连接就正常了。所以说使用MYCAT来实现读写分离是可行的。但是也有一个问题，session close的时候会有问题，但是新链接能够保证是没有问题的，从最后压测结果也是可以看出来的。但是从基本也满足了我们的基本需求。

标签：

版权申明：本站文章部分自网络，如有侵权，请联系：west999com@outlook.com
特别注意：本站所有转载文章言论不代表本站观点，本站所提供的摄影照片，插画，设计作品，如需使用，请与原作者联系，版权归原作者所有

上一篇：mysql的表锁和行锁，排他锁和共享锁。

下一篇：JDBC与Statement和PreparedStatement的区别

相关文章

IDC资讯： 主机资讯 注册资讯 托管资讯 vps资讯 网站建设

网站运营： 建站经验 策划盈利 搜索优化 网站推广 免费资源

网站联盟： 联盟新闻 联盟介绍 联盟点评 网赚技巧

行业资讯： 搜索引擎 网络游戏 电子商务 广告传媒

网络编程： Asp.Net编程 Asp编程 Php编程 Xml编程 Access Mssql Mysql 其它

服务器技术： Web服务器 Ftp服务器 Mail服务器 Dns服务器 安全防护

软件技巧： 其它软件 Word Excel Powerpoint Ghost Vista QQ空间 QQ FlashGet 迅雷

网页制作： FrontPages Dreamweaver Javascript css photoshop fireworks Flash

程序设计： Java技术 C/C++ VB delphi

网络知识： 网络协议 网络安全 网络管理 组网方案 Cisco技术

操作系统： Win2000 WinXP Win2003 Mac OS Linux FreeBSD

热门词条
最新资讯
热门关注
热门标签