如何解决MySQL超过最大连接数问题

2013-02-04 23:36 数据库狂奔的蜗牛

更多是采用了不合理的设计架构或数据结构引起的
最近网站出现 User 数据库名称 has already more than 'max_user_connections' active connections 的报错，网站瘫痪。有必要研究下这个问题。

max_user_connections 是 MySQL 用户连接数的最大值设置，整段语句的意思是：服务器的 MySQL 的最大连接数参数设置不足。解决方法：修改 MySQL 安装目录下 my.ini 或者 my.cnf 文件内的 max_user_connections 参数的数值，重启 MySQL 服务器。

但是正常来说，MySQL默认的100个连接数是足够的。我们需要从程序上去考虑。MySQL的默认最大连接数为100（N），实际给普通用户使用只有N-1个，保留一个连接是留给超级管理员使用的，防止连接占满了不会把管理员也踢出来。很多网站在运行的时候都会出现连接数受限现象，我认为十之八九并非是网站的真实访问量太大导致连接数超标，更多是因为我们在设计网站程序的时候采用了不合理的设计架构或数据结构引起的。非正常连接超限可能原因如下（天缘即时归纳未必完整或无错讹仅供参考）：

类似人数、在线时间、浏览数等统计功能与主程序数据库同属一个数据空间时就很容易出现。
复杂的动态页尤其是用户每次浏览都涉及到多数据库或多表操作时候也很容易出现。
还有就是程序设计的不合理（比如复杂运算、等待等操作放置在数据库交互行为中间进行），或者程序存在释放BUG。
计算机硬件配置太低却安装太高版、太高配置的MySQL。
未采用缓存技术。
数据库未经过优化或表格设计及其复杂。
等等一些原因，都会延长数据库的数据交互时间或增加交互次数。所以，如果大家遇到这类问题，首先要考虑程序是否存在BUG导致连接释放失败，再次就是考虑优化软硬件。当然修改MySQL连接数也是软件优化的操作方法之一，希望大家都能够本着学习的态度通过研究一下自身的原因从而解决这一问题。如果实在是找不到原因，那就只好先修改连接数，暂缓定位真实原因了。

关于PHP的数据库持久连接 mysql_pconnect 
PHP程序员应该都知道连接MySQL数据库可以使用mysql_pconnect（永久连接）函数，使用数据库永久连接可以提高效率，但是实际应用中数据库永久连接往往会导致出现一些问题，通常的表现就是在大访问量的网站上时常发生断断续续的无法连接数据库的情况，出现类似＂Too many connections in ...＂的错误提示信息，重新启动服务器又正常了，但过不了一会儿又出现同样的故障。对于这些问题的成因，恐怕就不是每个人都能说清楚的了，虽然PHP文档里有一些相关资料，但是解释的并不浅显易懂，这里我厚着脸皮试图做一个简单的讨论，所述观点不见得全都正确，欢迎大家反馈意见。

首先看看数据库永久连接的定义：永久的数据库连接是指在脚本结束运行时不关闭的连接。当收到一个永久连接的请求时。PHP 将检查是否已经存在一个（前面已经开启的）相同的永久连接。如果存在，将直接使用这个连接；如果不存在，则建立一个新的连接。所谓"相同"的连接是指用相同的用户名和密码到相同主机的连接。

PHP使用永久连接方式操作MySQL是有前提的：就是PHP必须安装为多线程或多进程Web服务器的插件或模块。最常见的形式是把PHP用作多进程Apache服务器的一个模块。对于一个多进程的服务器，其典型特征是有一个父进程和一组子进程协调运行，其中实际生成Web页面的是子进程。每当客户端向父进程提出请求时，该请求会被传递给还没有被其它的客户端请求占用的子进程。这也就是说当相同的客户端第二次向服务端提出请求时，它将有可能被一个不同的子进程来处理。在开启了一个永久连接后，所有不同子进程请求SQL服务的后继页面都能够重新使用这个已经建立的 SQL服务器连接。它使得每个子进程在其生命周期中只做一次连接操作，而非每次在处理一个页面时都要向 SQL 服务器提出连接请求。每个子进程将对服务器建立各自独立的永久连接。PHP本身并没有数据库连接池的概念，但是Apache有进程池的概念, 一个Apache子进程结束后会被放回进程池, 这也就使得用mysql_pconnect打开的的那个mysql连接资源可以不被释放，而是依附在相应的Apache子进程上保存到了进程池中。于是在下一个连接请求时它就可以被复用。一切看起来似乎都很正常，但是在Apache并发访问量大的时候，如果使用mysql_pconnect，会由于之前的Apache子进程占用的MySQL连接没有close, 很快使MySQL达到最大连接数，使得之后的请求可能得不到响应。

上面的部分文字是摘抄自PHP文档，看起来可能还是有些文绉绉的不好理解，那么我就用大白话再举一个例子来说明问题：

假设Apache配置最大连接数为1000，MySQL配置最大连接数为100，当Apache服务器接到200个并发访问的时候，其中100个涉及到数据库访问，剩下的100个不涉及数据库访问，因为这个时候还不存在可用的数据库连接，所以这里面涉及到数据库访问的100个并发会同时产生100个数据库永久连接，达到了数据库最大连接数，当这些操作没有结束的时候，任何其他的连接都无法再获得数据库连接，当这些操作结束了，相应的连接会被放入进程池，此时Apache的进程池里就有了200个空闲的子进程，其中100个是带有数据库连接的，由于Apache会为访问请求随机的挑选空闲子进程，所以你得到的子进程很可能是不包含数据库连接的那100个中的一个，而数据库连接已经达到了最大值，你也不可能成功的建立新的数据库连接，唉，你便只好不停的刷新页面，哪个时候运气好，碰巧分配到了带有数据库连接的子进程，才能正常浏览页面。如果是大访问量的网站来说，任何时候都可能存在大量的并发，所以浏览者可能就会不停的发现无法连接数据库的现象了。

或许你会说，我们把Apache和MySQL的最大连接数调成一样大不就可以了么？是的，合理的调整这个最大连接数某种程度上会避免这个问题的发生，但是Apache和MySQL的负载能力是不同的，如果按照Apache的负载能力来设置，对于MySQL来说，这个最大连接数就偏大，会产生大量的MySQL数据库永久连接，打个比方，就好像和平时代还要养活一个几百万的军队一样，其开销得不偿失；而如果按照Mysql的负载能力设置，对于Apache来说，这个最大连接数就偏小，有点杀鸡牛刀的感觉，无法发挥Apache的最大效率。

所以按照PHP手册上的介绍，只适合在并发访问不大的网站上使用数据库永久连接，但对于一个并发访问不大的网站来说，使用数据库永久连接带来的效率提高似乎没有太大的意义，从这个角度上来看，我觉得PHP中的数据库永久连接基本上是一个鸡肋的角色，如果你一定要使用数据库连接池的概念，可以尝试一下sqlrelay或者Apache本身提供的mod_dbd，说不定会有惊喜。

关于mysql_free_result和mysql_close 
之前用mysql的时候一直是在用短链接，调用mysql_store_result获取一次数据之后就直接调用：

1	mysql_free_result(m_result);
2	mysql_close(m_Database);
但是有两个问题：

当使用长连接时（即connect之后一直不close），如果最后会调用mysql_close，需不需要每次都调用mysql_free_result呢?
当mysql_close调用之后，m_result的数据是否还可以用。
先说一下结论：

必须每次调用。因为经过测试，每次mysql_store_result的指针都是不同的，可见并不是共享了同一块buf。
还是可以使用。经过valgrind扫描，只调用mysql_close的扫描结果是：

1	==9397== 16,468 (88 direct, 16,380 indirect) bytes in 1 blocks are definitely lost in loss record 4 of 5
	2	==9397==    at 0x40219B3: malloc (vg_replace_malloc.c:195)
	3	==9397==    by 0x8053EA2: my_malloc (in /data/home/dantezhu/appbase/application/platform/openqqcom/share/db_openright/test/test)
	4	==9397==    by 0x806D314: mysql_store_result (in /data/home/dantezhu/appbase/application/platform/openqqcom/share/db_openright/test/test)
	5	==9397==    by 0x804BB04: CMySQLCppClient::Result(st_mysql_res*&) (mysql_cpp_client.cpp:127)
	6	==9397==    by 0x804AB58: CDBOpenRight::GetUinsByApp(unsigned int, std::set<unsigned int, std::less<unsigned int>, std::allocator<unsigned int> >&) (db_openright.cpp:58)
	7	==9397==    by 0x8049F10: main (test.cpp:27)

以后再慢慢研究。。

作者：狂奔的蜗牛 出处： http://www.host900.com/

本文版权归作者所有，欢迎转载。未经作者同意下，必须在文章页面明显标出原文链接及作者，否则保留追究法律责任的权利。

如果您认为这篇文章还不错或者有所收获，可以分享给您的小伙伴，因为你的支持是我继续写作，分享的最大动力！

上一篇Slave_SQL_Running: No mysql同步故障解决方法

下一篇mysql建立数据库