ubuntu - 如何优化 nginx 负载均衡器组中 mysql vm 的性能？

Question

我用 3 个 ubuntu 虚拟机建立了一个 mysql HA 组。一个是负载均衡器，另外两个是 mysql vms。每个 mysql vm 有 8 个 mysql 实例，对应 8 个端口，负载均衡器上有 8 个端口，对应 mysql vms 中的端口。两个 mysql vm 是相互主控复制的。但是当我用 sysbench 测试 mysql 的性能时，负载均衡器的性能很差，而如果我在其中一个 mysql vm 中测试它就可以了。

我使用了命令

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 中测试时

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
}

我认为性能不佳的原因是nginx负载均衡，但我不知道如何优化它。有没有人可以帮助我？谢谢你。

标签： ubuntunginxload-balancinghigh-availabilitymultiple-instances