请教如何优化HA组中,mysql虚拟机的表现?

我建立了一个数据库高可用性服务器组,使用一台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
}

请问是否有人知道需要如何改善?谢谢

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问
相关内容推荐