MySql Proccesslist充满"Sleep"导致“连接过多"的条目? [英] MySql Proccesslist filled with "Sleep" Entries leading to "Too many Connections"?
问题描述
我想就长期存在的php/mysql连接问题向您寻求帮助.
I'd like to ask your help on a longstanding issue with php/mysql connections.
每次执行"SHOW PROCESSLIST"命令时，它都会向我显示从5个Web服务器到数据库服务器的大约400个空闲(状态:睡眠)连接.
Every time I execute a "SHOW PROCESSLIST" command it shows me about 400 idle (Status: Sleep) connections to the database Server emerging from our 5 Webservers.
这从来不是什么大问题(直到最近流量增加，而且从那时起MySQL反复报告许多连接"问题，我才找到了一个快速解决方案，即使这些连接中有350+个在睡眠"状态.另外，即使与该服务器的睡眠连接，服务器也无法获得MySQL连接.
That never was much of a problem (and I didn't find a quick solution) until recently traffic numbers increased and since then MySQL reports the "to many connections" Problems repeatedly, even so 350+ of those connections are in "sleep" state. Also a server can't get a MySQL connection even if there are sleeping connection to that same server.
当重新启动apache服务器时，所有这些连接都会消失.
All those connections vanish when an apache server is restated.
用于创建数据库连接的PHP代码使用常规的"mysql"模块，"mysqli"模块，PEAR :: DB和Zend Framework Db适配器. (不同的项目).没有项目使用持久连接.
The PHP Code used to create the Database connections uses the normal "mysql" Module, the "mysqli" Module, PEAR::DB and Zend Framework Db Adapter. (Different projects). NONE of the projects uses persistent connections.
提高连接限制是可能的，但似乎不是一个好的解决方案，因为它现在是450，而且每次一次只有20-100个真实"连接.
Raising the connection-limit is possible but doesn't seem like a good solution since it's 450 now and there are only 20-100 "real" connections at a time anyways.
我的问题:
为什么处于睡眠状态的连接如此之多，如何防止这种情况发生?
Why are there so many connections in sleep state and how can I prevent that?
-更新:
一次运行的Apache请求数量永远不会超过50个并发请求，因此我想关闭连接或apache会在没有phpscript或其他东西(?)的情况下保持端口打开状态有问题
The Number of Apache requests running at a time never exceeds 50 concurrent requests, so i guess there is a problem with closing the connection or apache keeps the port open without a phpscript attached or something (?)
my.cnf，如果有帮助的话:
my.cnf in case it's helpful:
innodb_buffer_pool_size = 1024M
max_allowed_packet = 5M
net_buffer_length = 8K
read_buffer_size = 2M
read_rnd_buffer_size = 8M
query_cache_size = 512M
myisam_sort_buffer_size = 128M
max_connections = 450
thread_cache = 50
key_buffer_size = 1280M
join_buffer_size = 16M
table_cache = 2048
sort_buffer_size = 64M
tmp_table_size = 512M
max_heap_table_size = 512M
thread_concurrency = 8
log-slow-queries = /daten/mysql-log/slow-log
long_query_time = 1
log_queries_not_using_indexes
innodb_additional_mem_pool_size = 64M
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 2
innodb_file_per_table
推荐答案
基本上，在以下情况下，您会在睡眠"状态下获得连接:
Basically, you get connections in the Sleep state when :
- PHP脚本连接到MySQL
- 执行了一些查询
- 然后，PHP脚本执行一些需要时间的工作
- 无需断开与数据库的连接
- a PHP script connects to MySQL
- some queries are executed
- then, the PHP script does some stuff that takes time
- without disconnecting from the DB
- 这意味着它与MySQL服务器断开连接
因此，当您有许多保持连接的PHP进程而没有在数据库端实际进行任何操作时，通常会使许多进程处于Sleep状态.
So, you generally end up with many processes in a Sleep state when you have a lot of PHP processes that stay connected, without actually doing anything on the database-side.
一个基本思路，因此:请确保您没有运行太长时间的PHP进程-或在不再需要访问数据库时强制它们断开连接.
A basic idea, so : make sure you don't have PHP processes that run for too long -- or force them to disconnect as soon as they don't need to access the database anymore.
当服务器上有一些负载时，我经常会看到另一件事:
Another thing, that I often see when there is some load on the server :- 越来越多的请求发送给Apache
- 这意味着要生成许多页面
- There are more and more requests coming to Apache
- which means many pages to generate
一种有助于解决问题的解决方案是减少查询所花费的时间-优化最长的查询.
A solution that can help is to reduce the time your queries take -- optimizing the longest ones.
这篇关于MySql Proccesslist充满"Sleep"导致“连接过多"的条目?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
