php MySQL 错误“连接过多”

MySQL Error "Too many connections"

提问by littleK

I am using MySQL 5.0 for a site that is hosted by GoDaddy (linux).

我将 MySQL 5.0 用于由 GoDaddy (linux) 托管的站点。

I was doing some testing on my web app, and suddenly I noticed that the pages were refreshing really slowly. Finally, after a long wait, I got to a page that said something along the lines of "MySQL Error, Too many connections...", and it pointed to my config.php file which connects to the database.

我正在对我的网络应用程序进行一些测试，突然我注意到页面刷新非常缓慢。最后，经过漫长的等待，我到达了一个页面，上面写着“MySQL 错误，连接太多...”，它指向我连接到数据库的 config.php 文件。

It has just been me connecting to the database, no other users. On each of my pages, I include the config.php file at the top, and close the mysql connection at the end of the page. There may be several queries in between. I fear that I am not closing mysql connections enough (mysql_close()).

只是我连接到数据库，没有其他用户。在我的每个页面上，我在顶部包含 config.php 文件，并在页面末尾关闭 mysql 连接。中间可能有多个查询。我担心我没有足够关闭 mysql 连接（mysql_close()）。

However, when I try to close them after running a query, I receive connection errors on the page. My pages are PHP and HTML. When I try to close a query, it seems that the next one won't connect. Would I have to include config.php again after the close in order to connect?

但是，当我在运行查询后尝试关闭它们时，我在页面上收到连接错误。我的页面是 PHP 和 HTML。当我尝试关闭查询时，下一个查询似乎无法连接。关闭后我是否必须再次包含 config.php 才能连接？

This error scared me because in 2 weeks, about 84 people start using this web application.

这个错误让我感到害怕，因为在 2 周内，大约有 84 人开始使用这个 Web 应用程序。

Thanks.

谢谢。

EDIT:

编辑：

Here is some pseudo-code of my page:

这是我页面的一些伪代码：

 require_once('../scripts/config.php');

 <?php
    mysql_query..

    if(this button is pressed){
       mysql_query...
    }
    if(this button is pressed){
       mysql_query...
    }
    if(this button is pressed){
       mysql_query...
    }
 ?>
 some html..
 ..
 ..
 ..
 ..
 <?php
   another mysql_query...
 ?>
 some more html..
 ..
 ..
 <?php mysql_close(); ?>

I figured that this way, each time the page opens, the connection opens, and then the connection closes when the page is done loading. Then, the connection opens again when someone clicks a button on the page, and so on...

我认为这样，每次打开页面时，都会打开连接，然后在页面加载完成后关闭连接。然后，当有人单击页面上的按钮时，连接会再次打开，依此类推...

EDIT:

编辑：

Okay, so I just got off the phone with GoDaddy. Apparently, with my Economy Package, I'm limited to 50 connections at a time. While my issue today happened with only me accessing the site, they said that they were having some server problems earlier. However, seeing as how I am going to have 84 users for my web app, I should probably upgrade to "Deluxe", which allows for 100 connections at a time. On a given day, there may be around 30 users accessing my site at a time, so I think the 100 would be a safer bet. Do you guys agree?

好的，所以我刚刚和 GoDaddy 通了电话。显然，使用我的经济套餐，我一次只能连接 50 个连接。虽然我今天的问题发生在只有我访问该站点的情况下，但他们说他们早些时候遇到了一些服务器问题。但是，考虑到我的 Web 应用程序将有 84 个用户，我可能应该升级到“豪华版”，它一次允许 100 个连接。在某一天，可能有大约 30 个用户一次访问我的网站，所以我认为 100 个将是一个更安全的赌注。你们同意吗？

回答by Pascal MARTIN

Shared-hosting providers generally allow a pretty small amount of simultaneous connections for the same user.

共享主机提供商通常允许同一用户同时进行少量连接。

What your code does is :

您的代码的作用是：

• open a connection to the MySQL server
• do it's stuff (generating the page)
• close the connection at the end of the page.

• 打开与 MySQL 服务器的连接
• 做它的东西（生成页面）
• 在页面末尾关闭连接。

The last step, when done at the end of the page is not mandatory: (quoting mysql_close's manual) :

最后一步，在页面末尾完成时不是强制性的：（引用mysql_close手册）：

Using mysql_close() isn't usually necessary, as non-persistent open links are automatically closed at the end of the script's execution.

通常不需要使用 mysql_close()，因为非持久打开的链接会在脚本执行结束时自动关闭。

But note you probably shouldn't use persistent connections anyway...

但请注意，您可能无论如何都不应该使用持久连接......

Two tips :

两个提示：

• use mysql_connectinsead of mysql_pconnect(already OK for you)
• Set the fourth parameter of mysql_connect to false (already OK for you, as it's the default value): (quoting the manual) :

• 使用mysql_connectinsead of mysql_pconnect（对你来说已经可以了）
• 将mysql_connect的第四个参数设置为false （已经可以了，因为它是默认值）：（引用手册）：

If a second call is made to mysql_connect() with the same arguments, no new link will be established, but instead, the link identifier of the already opened link will be returned.

The new_link parameter modifies this behavior and makes mysql_connect() always open a new link, even if mysql_connect() was called before with the same parameters.

如果使用相同的参数对 mysql_connect() 进行第二次调用，则不会建立新链接，而是返回已打开链接的链接标识符。

new_link 参数修改此行为并使 mysql_connect() 始终打开新链接，即使之前使用相同参数调用 mysql_connect() 也是如此。

---

---

What could cause the problem, then ?

那么什么可能导致问题？

Maybe you are trying to access several pages in parallel (using multiple tabs in your browser, for instance), which will simulate several users using the website at the same time ?

也许您正在尝试并行访问多个页面（例如，使用浏览器中的多个选项卡），这将模拟多个用户同时使用该网站？

If you have many users using the site at the same time and the code between mysql_connectand the closing of the connection takes lots of time, it will mean many connections being opened at the same time... And you'll reach the limit :-(

如果您有许多用户同时使用该站点，并且mysql_connect连接和关闭连接之间的代码需要很多时间，这将意味着同时打开许多连接......并且您将达到限制：- (

Still, as you are the only user of the application, considering you have up to 200 simultaneous connections allowed, there is something odd going on...

尽管如此，由于您是该应用程序的唯一用户，考虑到您最多允许 200 个同时连接，因此发生了一些奇怪的事情......

---

---

Well, thinking about "too many connections" and "max_connections"...

嗯，想想“太多的联系”和“ max_connections”......

If I remember correctly, max_connectionsdoes not limit the number of connections youcan open to the MySQL Server, but the total number of connectionsthat can bo opened to that server, by anyone connecting to it.

如果我没记错的话，max_connections不限制您可以打开到 MySQL 服务器的连接数，而是限制任何连接到它的人可以打开到该服务器的连接总数。

Quoting MySQL's documentation on Too many connections:

引用 MySQL 关于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. Its default value is 100. If you need to support more connections, you should set a larger value for this variable.

如果在尝试连接到 mysqld 服务器时出现连接过多错误，这意味着所有可用连接都被其他客户端使用。

允许的连接数由 max_connections 系统变量控制。它的默认值是100。如果需要支持更多的连接，这个变量应该设置一个更大的值。

So, actually, the problem might not come from you nor your code (which looks fine, actually): it might "just" be that you are not the only one trying to connect to that MySQL server (remember, "shared hosting"), and that there are too many people using it at the same time...

因此，实际上，问题可能不是来自您，也不是您的代码（实际上看起来不错）：它可能“只是”您不是唯一一个尝试连接到该 MySQL 服务器的人（请记住，“共享托管”），而且有太多人同时使用它......

... And if I'm right and it's that, there's nothing you can do to solve the problem : as long as there are too many databases / users on that server and that max_connectionis set to 200, you will continue suffering...

...如果我是对的，就是这样，您无能为力解决问题：只要该服务器上的数据库/用户太多并且max_connection设置为 200，您就会继续受苦...

As a sidenote : before going back to GoDaddy asking them about that, it would be nice if someone could validate what I just said ^^

作为旁注：在回到 GoDaddy 询问他们之前，如果有人可以验证我刚刚说的内容会很好^^

回答by icc97

I had about 18 months of dealing with this (http://ianchanning.wordpress.com/2010/08/25/18-months-of-dealing-with-a-mysql-too-many-connections-error/)

我有大约 18 个月的时间来处理这个问题（http://ianchanning.wordpress.com/2010/08/25/18-months-of-dealing-with-a-mysql-too-many-connections-error/）

The solutions I had (that would apply to you) in the end were:

我最终拥有（适用于您）的解决方案是：

1. tune the database according to MySQLTuner.
2. defragment the tables weekly based on this post

1. 根据MySQLTuner调整数据库。
2. 根据这篇文章每周对表进行碎片整理

Defragmenting bash script from the post:

对帖子中的 bash 脚本进行碎片整理：

#!/bin/bash

# Get a list of all fragmented tables
FRAGMENTED_TABLES="$( mysql -e `use information_schema; SELECT TABLE_SCHEMA,TABLE_NAME
FROM TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema','mysql') AND
Data_free > 0` | grep -v '^+' | sed 's,t,.,' )"

for fragment in $FRAGMENTED_TABLES; do
  database="$( echo $fragment | cut -d. -f1 )"
  table="$( echo $fragment | cut -d. -f2 )"
  [ $fragment != "TABLE_SCHEMA.TABLE_NAME" ] && mysql -e "USE $database;
  OPTIMIZE TABLE $table;" > /dev/null 2>&1
done

回答by Evert

Make sure you are not using persistent connections. This is usually a bad idea..

确保您没有使用持久连接。这通常是一个坏主意..

If you've got that .. At the very most you will need to support just as much connections as you have apache processes. Are you able to change the max_connections setting?

如果你有那个.. 最多你需要支持与你有 apache 进程一样多的连接。您可以更改 max_connections 设置吗？

回答by MarkR

Are you completely sure that the database server is completely dedicated to you?

您是否完全确定数据库服务器是完全专供您使用的？

Log on to the datbase as root and use "SHOW PROCESSLIST" to see who's connected. Ideally hook this into your monitoring system to view how many connections there are over time and alert if there are too many.

以 root 用户身份登录数据库并使用“SHOW PROCESSLIST”查看谁已连接。理想情况下，将其连接到您的监控系统中，以查看随着时间的推移有多少连接并在连接过多时发出警报。

The maximum database connections can be configured in my.cnf, but watch out for running out of memory or address space.

可以在 my.cnf 中配置最大数据库连接数，但要注意内存或地址空间不足。

回答by Wouter van Nifterick

If you have shell access, use netstat to see how many sockets are opened to your database and where they come from.

如果您有 shell 访问权限，请使用 netstat 来查看向您的数据库打开了多少个套接字以及它们来自何处。

On Linux, type:

在 Linux 上，键入：

netstat -n -a |grep 3306

On windows, type:

在 Windows 上，键入：

netstat -n -a |findstr 3306

回答by Nitesh

The solution could one of these, i came across this in a MCQA test, even i did not understood which one is right!

解决方案可能是其中之一，我在 MCQA 测试中遇到了这个问题，即使我不明白哪个是正确的！

Set this in my.cnf "set-variable=max_connections=200"

在 my.cnf 中设置“set-variable=max_connections=200”

Execute the command "SET GLOBALmax_connections = 200"

执行命令“SET GLOBALmax_connections = 200”

Use always mysql_connect() function in order to connect to the mysql server

始终使用 mysql_connect() 函数以连接到 mysql 服务器

Use always mysql_pconnect() function in order to connect to the mysql server

始终使用 mysql_pconnect() 函数以连接到 mysql 服务器

回答by dilraj singh

Followings are possible solutions:

以下是可能的解决方案：

1)Increase the max connection setting by setting the global variable in mysql.

1）通过在mysql中设置全局变量来增加max connection设置。

set global max_connection=200;

Note:It will increase the server load.

注意：它会增加服务器负载。

2)Empty your connection pool as below :

2)清空您的连接池，如下所示：

FLUSH HOSTS;

FLUSH HOSTS;

3)check your processList and kill specific processlist if you don't want any of them.

3）如果您不想要任何进程列表，请检查您的进程列表并杀死特定的进程列表。

You may refer this :-

你可以参考这个：-

article link

文章链接