We are benchmarking one of our sites with ab (apachebench) on a page that creates a MySQL connection, queries and then closes the connection (through PHP).
我们在一个页面上用ab(apachebench)对我们的一个站点进行基准测试,该页面创建一个MySQL连接,查询然后关闭连接(通过PHP)。
We have hard coded our my.cnf with a max_connections limit of 500.
我们对my.cnf进行了硬编码,其max_connections限制为500。
When we run the stress test, MySQL never seems to hit the connection limit we specify although we DO HAVE "unable to connect to the database" type errors returned from our script. It's as if MySQL can't open any more connections than around ~237 at one time.
当我们运行压力测试时,MySQL似乎永远不会达到我们指定的连接限制,尽管我们已经“无法连接到数据库”类型错误从我们的脚本返回。这就好像MySQL无法再打开大约237次的连接。
Here is our current my.cnf:
这是我们目前的my.cnf:
[mysqld]
max_connections = 500
port = 3306
socket = /var/mysql/mysql.sock
skip-external-locking
key_buffer_size = 384M
max_allowed_packet = 1M
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 128M
thread_concurrency = 8
max_heap_table_size = 512M
tmp_table_size = 512M
table_cache = 2048
User Limits:
$ ulimit -a
core file size (blocks, -c) 0
data seg size (kbytes, -d) unlimited
file size (blocks, -f) unlimited
max locked memory (kbytes, -l) unlimited
max memory size (kbytes, -m) unlimited
open files (-n) 16384
pipe size (512 bytes, -p) 1
stack size (kbytes, -s) 8192
cpu time (seconds, -t) unlimited
max user processes (-u) 1250
virtual memory (kbytes, -v) unlimited
The problem seems to be either with PHP or MySQL (or of course misconfiguration by us).
问题似乎是PHP或MySQL(或者当然是我们的错误配置)。
Any help, suggestions and tips are greatly appreciated - thanks in advance.
任何帮助,建议和提示都非常感谢 - 提前感谢。
1 个解决方案
#1
2
Okay, so I have found the culprit for this problem.
好的,所以我找到了这个问题的罪魁祸首。
When running the stress load, it was not gradual - all of the connections were opened at once flooding the server.
当运行压力负载时,它不是渐进的 - 所有连接都立即打开服务器。
MySQL has a default back_log of 50 - which is the number of outstanding connections MySQL can have before processing them. We were hitting this limit when flooding the server and further connections over the 50 threshold were refused.
MySQL的默认back_log为50 - 这是MySQL在处理它们之前可以拥有的未完成连接数。当洪泛服务器时,我们达到了这个限制,并且拒绝了超过50阈值的更多连接。
To fix this, we edited my.cnf with the following line, increasing the listen backlog to a reasonable 128 which works for our setup:
为了解决这个问题,我们使用以下行编辑了my.cnf,将listen backlog增加到合理的128,这对我们的设置有效:
back_log = 128
Please be aware that the maximum value you can enter here is dependant on your OS and the limits set within your OS. MySQL can have a maximum listen backlog of 65535 however your OS may have a figure less than that.
请注意,您可以在此输入的最大值取决于您的操作系统和操作系统中设置的限制。 MySQL可以拥有最大的监听积压65535,但是你的操作系统的数字可能会低于此值。
With the new back_log of 128 our problem is fixed and we are no longer getting Connection refused error message under load.
使用新的back_log 128我们的问题已修复,我们不再在加载时收到Connection refused错误消息。
#1
2
Okay, so I have found the culprit for this problem.
好的,所以我找到了这个问题的罪魁祸首。
When running the stress load, it was not gradual - all of the connections were opened at once flooding the server.
当运行压力负载时,它不是渐进的 - 所有连接都立即打开服务器。
MySQL has a default back_log of 50 - which is the number of outstanding connections MySQL can have before processing them. We were hitting this limit when flooding the server and further connections over the 50 threshold were refused.
MySQL的默认back_log为50 - 这是MySQL在处理它们之前可以拥有的未完成连接数。当洪泛服务器时,我们达到了这个限制,并且拒绝了超过50阈值的更多连接。
To fix this, we edited my.cnf with the following line, increasing the listen backlog to a reasonable 128 which works for our setup:
为了解决这个问题,我们使用以下行编辑了my.cnf,将listen backlog增加到合理的128,这对我们的设置有效:
back_log = 128
Please be aware that the maximum value you can enter here is dependant on your OS and the limits set within your OS. MySQL can have a maximum listen backlog of 65535 however your OS may have a figure less than that.
请注意,您可以在此输入的最大值取决于您的操作系统和操作系统中设置的限制。 MySQL可以拥有最大的监听积压65535,但是你的操作系统的数字可能会低于此值。
With the new back_log of 128 our problem is fixed and we are no longer getting Connection refused error message under load.
使用新的back_log 128我们的问题已修复,我们不再在加载时收到Connection refused错误消息。