sysbench 0.5 ，mysql 5.7.14 ，atlas Atlas-2.2.1.el6.x86_64.rpm
3台虚拟机 M 143 ，S145 ， Atlas149
用atlas中间件 对mysql进行读写分离
目标：
用sysbench 对atlas 进行压测试，检查在使atlas中间件读写分离后的mysql集群整体的性能吞吐情况
过程：
1. sysbench 直接对主库加压，命令如下
# sysbench --mysql-table-engine=innodb --oltp-table-size=10000 --debug=on --num-threads=3 --mysql-db=db1 --mysql-user=root --mysql-host=192.168.186.143 --mysql-password=123456 --port=3306 --test=/usr/local/sysbench/sysbench/tests/db/oltp.lua --oltp_tables_count=3 prepare
sysbench 0.5: multi-threaded system evaluation benchmark
No DB drivers specified, using mysql
Creating table 'sbtest1'...
Inserting 10000 records into 'sbtest1'
Creating secondary indexes on 'sbtest1'...
Creating table 'sbtest2'...
Inserting 10000 records into 'sbtest2'
Creating secondary indexes on 'sbtest2'...
Creating table 'sbtest3'...
Inserting 10000 records into 'sbtest3'
Creating secondary indexes on 'sbtest3'...
[root@localhost /usr/local/sysbench-0.5/bin]
# sysbench --mysql-table-engine=innodb --oltp-table-size=10000 --debug=on --num-threads=3 --mysql-db=db1 --mysql-user=root --mysql-host=192.168.186.143 --mysql-password=123456 --port=3306 --test=/usr/local/sysbench/sysbench/tests/db/oltp.lua --oltp_tables_count=3 run
sysbench 0.5: multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 3
Debug mode enabled.
Random number generator seed is 0 and will be ignored
Initializing worker threads...
No DB drivers specified, using mysql
DEBUG: Worker thread (#2) started!
DEBUG: Worker thread (#1) started!
DEBUG: Worker thread (#0) started!
Threads started!
DEBUG: Ignoring error 1213 Deadlock found when trying to get lock; try restarting transaction,
DEBUG: Ignored error encountered, restarting transaction
DEBUG: Ignoring error 1213 Deadlock found when trying to get lock; try restarting transaction,
DEBUG: Ignored error encountered, restarting transaction
DEBUG: Ignoring error 1213 Deadlock found when trying to get lock; try restarting transaction,
DEBUG: Ignored error encountered, restarting transaction
DEBUG: Ignoring error 1213 Deadlock found when trying to get lock; try restarting transaction,
DEBUG: Ignored error encountered, restarting transaction
DEBUG: Ignoring error 1213 Deadlock found when trying to get lock; try restarting transaction,
DEBUG: Ignored error encountered, restarting transaction
DEBUG: Ignoring error 1213 Deadlock found when trying to get lock; try restarting transaction,
DEBUG: Ignored error encountered, restarting transaction
DEBUG: Ignoring error 1213 Deadlock found when trying to get lock; try restarting transaction,
DEBUG: Ignored error encountered, restarting transaction
Done.
OLTP test statistics:
queries performed:
read: 140098
write: 40007
other: 20007
total: 200112
transactions: 10000 (39.26 per sec.)
read/write requests: 180105 (707.04 per sec.)
other operations: 20007 (78.54 per sec.)
ignored errors: 7 (0.03 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 254.7299s
total number of events: 10000
total time taken by event execution: 764.0871s
response time:
min: 27.57ms
avg: 76.41ms
max: 539.19ms
approx. 95 percentile: 246.52ms
Threads fairness:
events (avg/stddev): 3333.3333/5.56
execution time (avg/stddev): 254.6957/0.01
DEBUG: Verbose per-thread statistics:
DEBUG: thread # 0: min: 0.0280s avg: 0.0765s max: 0.5086s events: 3328
DEBUG: total time taken by even execution: 254.6939s
DEBUG: thread # 1: min: 0.0321s avg: 0.0765s max: 0.4619s events: 3331
DEBUG: total time taken by even execution: 254.7094s
DEBUG: thread # 2: min: 0.0276s avg: 0.0762s max: 0.5392s events: 3341
DEBUG: total time taken by even execution: 254.6837s
2 sysbench 连接Atlas代理进行加压，命令如下
# sysbench --mysql-table-engine=innodb --oltp-table-size=10000 --debug=on --num-threads=3 --mysql-db=db1 --mysql-user=root --mysql-host=192.168.186.149 --mysql-password=123456 --port=1234 --test=/usr/local/sysbench/sysbench/tests/db/oltp.lua --oltp_tables_count=3 prepare
sysbench 0.5: multi-threaded system evaluation benchmark
No DB drivers specified, using mysql
FATAL: unable to connect to MySQL server, aborting...
FATAL: error 2003: Can't connect to MySQL server on '192.168.186.149' (111)
FATAL: failed to execute function `prepare': /usr/local/sysbench/sysbench/tests/db/common.lua:103: Failed to connect to the database
2 个解决方案
#1
以下是atlas中间件的配置情况
1 [mysql-proxy]
2
3 #Atlas加载的模块名称，不需要改
4 plugins = admin, proxy
5
6 #管理接口的用户名
7 admin-username = atroot
8
9 #管理接口的密码
10 admin-password = at123456
11
12 #实现管理接口的Lua脚本所在路径
13 admin-lua-script = /usr/local/mysql-proxy/lib/mysql-proxy/lua/admin.lua
14
15 #Atlas后端连接的MySQL主库的IP和端口，可设置多项，用逗号分隔
16 proxy-backend-addresses = 192.168.186.143:3306
17
19 proxy-read-only-backend-addresses = 192.168.186.145:3306@1
22 daemon = true
24 keepalive = true
25
26 pwds = root:/iZxz+0GRoA=
27
28
31
32 #日志级别，分为message、warning、critical、error、debug五个级别
33 log-level = message
34
35
36 sql-log = REALTIME
37
38 #日志存放的路径
39 log-path = /usr/local/mysql-proxy/log
40
41 #实例名称，用于同一台机器上多个Atlas实例间的区分
42 instance = test
43
44 #Atlas监听的工作接口IP和端口
45 proxy-address = 0.0.0.0:1234
46
47 #Atlas监听的管理接口IP和端口
48 admin-address = 0.0.0.0:2345
49 #连接池的最小空闲连接数，应设为event-threads的整数倍，可根据业务请求量大小适当调大或调小
50 min-idle-connections = 8
51
52 #分表设置，此例中person为库名，mt为表名，id为分表字段，3为子表数量，可设置多项，以逗号分隔，若不分表则不需要> 设置该项
53 #tables = person.mt.id.3
54
55 #用户名与其对应的加密过的MySQL密码，密码使用PREFIX/bin目录下的加密程序encrypt加密，此设置项用于多个用户名同时
访问同一个Atlas实例的情况，若只有一个用户名>则不需要设置该项
56 #pwds = user1:+jKsgB3YAG8=, user2:GS+tr4TPgqc=
57
58 #默认字符集，若不设置该项，则默认字符集为latin1
59 charset = utf8
60
61 #允许连接Atlas的客户端的IP，可以是精确IP，也可以是IP段，以逗号分隔，若不设置该项则允许所有IP连接，否则只允许> 列表中的IP连接
62 #client-ips = 127.0.0.1, 192.168.1
63
64 #Atlas前面挂接的LVS的物理网卡的IP(注意不是虚IP)，若有LVS且设置了client-ips则此项必须设置，否则可以不设置
65 #lvs-ips = 192.168.1.1
#2
sysbench --mysql-table-engine=innodb --oltp-table-size=10000 --debug=on --num-threads=3 --mysql-db=db1 --mysql-user=root --mysql-host=192.168.186.149 --mysql-password=123456 --port=1234 --test=/usr/local/sysbench/sysbench/tests/db/oltp.lua --oltp_tables_count=3 prepare
这个lua是要写库的。又做了读写分离。写不进去，可能出错。
---by 闫
这个lua是要写库的。又做了读写分离。写不进去，可能出错。
---by 闫
#1
以下是atlas中间件的配置情况
1 [mysql-proxy]
2
3 #Atlas加载的模块名称，不需要改
4 plugins = admin, proxy
5
6 #管理接口的用户名
7 admin-username = atroot
8
9 #管理接口的密码
10 admin-password = at123456
11
12 #实现管理接口的Lua脚本所在路径
13 admin-lua-script = /usr/local/mysql-proxy/lib/mysql-proxy/lua/admin.lua
14
15 #Atlas后端连接的MySQL主库的IP和端口，可设置多项，用逗号分隔
16 proxy-backend-addresses = 192.168.186.143:3306
17
19 proxy-read-only-backend-addresses = 192.168.186.145:3306@1
22 daemon = true
24 keepalive = true
25
26 pwds = root:/iZxz+0GRoA=
27
28
31
32 #日志级别，分为message、warning、critical、error、debug五个级别
33 log-level = message
34
35
36 sql-log = REALTIME
37
38 #日志存放的路径
39 log-path = /usr/local/mysql-proxy/log
40
41 #实例名称，用于同一台机器上多个Atlas实例间的区分
42 instance = test
43
44 #Atlas监听的工作接口IP和端口
45 proxy-address = 0.0.0.0:1234
46
47 #Atlas监听的管理接口IP和端口
48 admin-address = 0.0.0.0:2345
49 #连接池的最小空闲连接数，应设为event-threads的整数倍，可根据业务请求量大小适当调大或调小
50 min-idle-connections = 8
51
52 #分表设置，此例中person为库名，mt为表名，id为分表字段，3为子表数量，可设置多项，以逗号分隔，若不分表则不需要> 设置该项
53 #tables = person.mt.id.3
54
55 #用户名与其对应的加密过的MySQL密码，密码使用PREFIX/bin目录下的加密程序encrypt加密，此设置项用于多个用户名同时
访问同一个Atlas实例的情况，若只有一个用户名>则不需要设置该项
56 #pwds = user1:+jKsgB3YAG8=, user2:GS+tr4TPgqc=
57
58 #默认字符集，若不设置该项，则默认字符集为latin1
59 charset = utf8
60
61 #允许连接Atlas的客户端的IP，可以是精确IP，也可以是IP段，以逗号分隔，若不设置该项则允许所有IP连接，否则只允许> 列表中的IP连接
62 #client-ips = 127.0.0.1, 192.168.1
63
64 #Atlas前面挂接的LVS的物理网卡的IP(注意不是虚IP)，若有LVS且设置了client-ips则此项必须设置，否则可以不设置
65 #lvs-ips = 192.168.1.1
#2
sysbench --mysql-table-engine=innodb --oltp-table-size=10000 --debug=on --num-threads=3 --mysql-db=db1 --mysql-user=root --mysql-host=192.168.186.149 --mysql-password=123456 --port=1234 --test=/usr/local/sysbench/sysbench/tests/db/oltp.lua --oltp_tables_count=3 prepare
这个lua是要写库的。又做了读写分离。写不进去，可能出错。
---by 闫
这个lua是要写库的。又做了读写分离。写不进去，可能出错。
---by 闫