我建立了一个数据库高可用性服务器组,使用一台vm运行nginx作为负载均衡,两台mysql vm组成master-master repolication的高可用性结构,每台mysql vm上各自运行八个mysql实例。仅load balancer保留外网ip,其余都通过内网进行连接,各vm之间端口对应。如下图:
但当我使用sysbench测试mysql性能的时候,发现在load balancer上运行测试与直接在mysql vm 1上测试相比,性能下降很大。
在load balancer上测试,使用指令
sysbench --test=oltp --mysql-host=192.168.1.11 --mysql-port=3307 --mysql-user=xxx --mysql-password=xxx --mysql-db=test1 --num-threads=32 --max-time=1800 --max-requests=0 --oltp-table-size=10000000 run>> /var/tmp/mysql3307.txt
获得结果
OLTP test statistics:
queries performed:
read: 3522974
write: 1258168
other: 503270
total: 5284412
transactions: 251629 (139.78 per sec.)
deadlocks: 12 (0.01 per sec.)
read/write requests: 4781142 (2655.95 per sec.)
other operations: 503270 (279.57 per sec.)
Test execution summary:
total time: 1800.1653s
total number of events: 251629
total time taken by event execution: 57596.9970
per-request statistics:
min: 42.53ms
avg: 228.90ms
max: 3086.55ms
approx. 95 percentile: 407.86ms
Threads fairness:
events (avg/stddev): 7863.4062/381.89
execution time (avg/stddev): 1799.9062/0.05
在mysql vm 1上测试,使用指令
sysbench --test=oltp --mysql-host=127.0.0.1 --mysql-port=3307 --mysql-user=xxx --mysql-password=xxx --mysql-db=test1 --num-threads=32 --max-time=1800 --max-requests=0 --oltp-table-size=10000000 run>> /var/tmp/mysql3307.txt
获得结果
OLTP test statistics:
queries performed:
read: 140070
write: 50010
other: 20005
total: 210085
transactions: 10000 (317.15 per sec.)
deadlocks: 5 (0.16 per sec.)
read/write requests: 190080 (6028.38 per sec.)
other operations: 20005 (634.46 per sec.)
Test execution summary:
total time: 31.5308s
total number of events: 10000
total time taken by event execution: 1007.3012
per-request statistics:
min: 13.73ms
avg: 100.73ms
max: 589.49ms
approx. 95 percentile: 184.95ms
Threads fairness:
events (avg/stddev): 312.5000/6.02
execution time (avg/stddev): 31.4782/0.04
nginx的设置如下
stream {
server {
listen 3307;
proxy_pass db3307;
}
upstream db3307 {
server 192.168.1.11:3307 weight=1;
server 192.168.1.12:3307 weight=1;
}
server {
listen 3308;
proxy_pass db3308;
}
upstream db3308 {
server 192.168.1.11:3308 weight=1;
server 192.168.1.12:3308 weight=1;
}
//... to port 3314
}
请问是否有人知道需要如何改善?谢谢