mysql “too many connections”

今早一起来就发现网站出现 mysql "too many connections"。在登录mysql无效的情况下。只能重启。

查找了些资料,总结一下解决的办法:

1、修改my.cnf配置,增大max_connections的值。mysql默认的最大连接数只有 100

set-variable = max_connections=300

2、检查PHP程序中是否使用了pconnect持久链接。若有的话,最好改成connect。

另外一个CU 网友 ipaddr 说过:“如果使用pconnect的话,那么,你的Apache进程数,应当少于Mysql的最大连接数!!!”具体没有测试过!

3、在给数据库授权的时候,最好不要直接使用root。使用普通授权用户,这样的话,即使达到了最大连接数,也可以使用root连接上,以诊断连接来自何处。

 

==================================

粗略翻译一些mysql手册提到的解决办法:
如果在尝试连接mysql服务器时,你遇到 too many connections error ,这意味着mysqld的所有可用的连接数已被其他进程使用。mysql可配置的最大连接数的系统变量是

max_connections 。从mysql 5.1.15起,在apache web服务器中为增强性能其的默认值是 151(先前的默认值是 100),若你需支持更多连接数,你可对max_connections设置更大的值。

mysqld 实际上允许 max_connections+1个客户端连接。这个附加的连接是为了超级权限的用户而保留。且是授予超级权限的用户而非普通用户(它是不需要的),一个超级管理员可以在无特权用户连接数达到最大时进入server并使用 show processlist 来诊断问题。查看手册12.5.5.31, “SHOW PROCESSLIST Syntax”.

mysqld的最大连接数依赖于上层的系统平台线程库,内存的可用数,每个线程可用内存,每个连接的负载,及请求响应时间。如果你有很多G的可用内存,且每个连接的负载较低或目标响应时间较低的情况下,Linux及Solaris可同时支持500-1000的连接.由于操作系统接口兼容性问题,在windows平台下限制在 (打开表×2+打开的连接数)<2048 。

增大打开文件的限制是有必要的。见Section 2.6, “Installing MySQL on Linux”,怎样提升Mysql操作句柄在操作系统上的限制
 

http://dev.mysql.com/doc/refman/5.1/en/too-many-connections.html

B.5.2.7. Too many connections

If you get a Too many connections error when you try to connect to the mysqld server, this means that all available connections are in use by other clients.

The number of connections allowed is controlled by the max_connections system variable. Beginning with MySQL 5.1.15, its default value is 151 to improve performance when MySQL is used with the Apache Web server. (Previously, the default was 100.) If you need to support more connections, you should set a larger value for this variable.

mysqld

actually allows

max_connections+1

clients to connect. The extra connection is reserved for use by accounts that have the

SUPER

privilege. By granting the

SUPER

privilege to administrators and not to normal users (who should not need it), an administrator can connect to the server and use

SHOW PROCESSLIST

to diagnose problems even if the maximum number of unprivileged clients are connected. See

Section 12.5.5.31, “SHOW PROCESSLIST Syntax”

.

The maximum number of connections MySQL can support depends on the quality of the thread library on a given platform, the amount of RAM available, how much RAM is used for each connection, the workload from each connection, and the desired response time. Linux or Solaris should be able to support at 500–1000 simultaneous connections routinely and as many as 10,000 connections if you have many gigabytes of RAM available and the workload from each is low or the response time target undemanding. Windows is limited to (open tables × 2 + open connections) < 2048 due to the Posix compatibility layer used on that platform.

Increasing open-files-limit may be necessary. Also see Section 2.6, “Installing MySQL on Linux”, for how to raise the operating system limit on how many handles can be used by MySQL.

 

 

分享到QQ空间

Comments are closed.