MySQL资源暂时不可用 [英] Mysql resource temporarily unavailable

问题描述

在高负载时间内，我看到了一些此类错误:

I'm seeing a few of these errors during high load times:

mysql_connect() [<a
href='function.mysql-connect'>function.mysql-connect</a>]: [2002] Resource
temporarily unavailable (trying to connect via
unix:///var/lib/mysql/mysql.sock)

据我所知，mysql服务器没有达到最大连接数限制，但是还有其他一些原因阻止了它为查询服务. MySQL将达到什么其他限制?

From what I can tell the mysql server isn't hitting its max connections limit, but there's something else stopping it from serving the query. What other limits would MySQL be hitting?

我正在使用MySQL 5.5.21运行RHEL 6.2 64位

I'm running RHEL 6.2 64bit with MySQL 5.5.21

推荐答案

我仍然没有找到达到它的限制，但是我设法解决了这个问题.使用MEMORY引擎的会话表(在vbulletin中)出现问题.该表的索引是HASH，因此当vbulletin每小时清除一次该表时，它将锁定该表足够长的时间以容纳其他查询并将mysql推到其资源极限.

I still haven't found which limits it was hitting, but I did manage to work around the problem. There was a problem with our session table (in vbulletin) which uses the MEMORY engine. The indexes for this table were HASH and thus when vbulletin purged this table once an hour it would lock the table just long enough to hold up other queries and push mysql to the limit of its resources.

通过将索引更改为BTREE，这使MySQL可以更快地从会话表中删除行，并避免以前达到的任何限制.这些错误仅在将主数据库服务器升级到MySQL 5.5时才开始出现，因此我猜测在最新版本中对MEMORY表的处理方式有所不同.

By changing the indexes to BTREE this allowed MySQL to delete the rows from the session table a lot quicker and avoid any limits there were reached previously. The errors only started when we upgraded our master db server to MySQL 5.5, so I'm guessing MEMORY tables are handled differently in the latest release.

请参见 http://www. mysqlperformanceblog.com/2008/02/01/performance-gotcha-of-mysql-memory-tables/获取有关通过在MEMORRY上使用BTREE索引来提高速度的信息.

See http://www.mysqlperformanceblog.com/2008/02/01/performance-gotcha-of-mysql-memory-tables/ for information on speed increases from using BTREE indexes over HASH For MEMORY.

这篇关于MySQL资源暂时不可用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！