MySQL错误“太多连接” [英] MySQL Error "Too many connections"

问题描述

我使用MySQL 5.0的GoDaddy（linux）托管的网站。

我在我的网络应用程序上做了一些测试，突然我注意到这些页面刷新真的很慢。最后，经过漫长的等待，我得到一个页面，说的东西沿着MySQL错误，太多的连接...，它指向我的config.php文件连接到数据库。

它刚刚我连接到数据库，没有其他用户。在我的每个页面上，我在顶部包括config.php文件，并关闭页面末尾的mysql连接。其中可能有几个查询。我恐怕我没有关闭mysql连接足够（mysql_close（））。

但是，当我尝试在运行查询后关闭它们，我收到连接错误页。我的页面是PHP和HTML。当我尝试关闭一个查询时，似乎下一个不会连接。

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

感谢。

编辑：

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

  require_once（'../ scripts / config.php'）; 
 
<？php 
 mysql_query .. 
 
 if（按下此按钮）{
 mysql_query ... 
} 
 if（按下此按钮）{
 mysql_query ... 
} 
 if（按下此按钮）{
 mysql_query ... 
} 
？> 
 some html .. 
 .. 
 .. 
 .. 
 .. 
<？php 
另一个mysql_query ... 
？> 
一些更多的html .. 
 .. 
 .. 
<？php mysql_close（）; ？> 
  

我想这样，每当页面打开时，连接打开，然后连接关闭当页面完成加载时。然后，当有人点击网页上的按钮时，连接再次打开...

编辑：

好吧，所以我刚刚和GoDaddy下了电话。显然，在我的经济包，我一次限制在50个连接。虽然我今天的问题只发生在我访问该网站，他们说，他们之前有一些服务器问题。然而，看到我将有84个用户为我的网络应用程序，我应该升级到豪华，它允许一次100个连接。在某一天，可能有大约30个用户一次访问我的网站，所以我认为100将是一个更安全的赌注。你们同意吗？

解决方案

共享托管服务提供商通常允许为同一用户提供少量的并发连接。 p>

您的代码所做的是：

• 打开与MySQL服务器的连接
• 
  
• 做它的东西（生成页面）


• 关闭页面末尾的连接。

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

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

但请注意，您可能不应该使用持续连接...

两个提示：

• 使用 mysql_connect insead mysql_pconnect


• 将mysql_connect的第四个参数设置为false （已经为您确定，因为它是默认值）：（引用手册）：

如果第二次调用
mysql_connect （）与相同的
参数，没有新的链接将
建立，但相反，已经打开的链接
的链接
标识符将被返回。

new_link
参数修改此行为，
使mysql_connect（）总是打开
新链接，即使mysql_connect
之前调用相同的
参数。

---

可能会导致问题的原因是什么？

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

许多用户在同一时间使用网站，并且 mysql_connect 之间的代码和连接的关闭需要很多时间，这意味着许多连接同时被打开。 ..您将达到上限： - （

但是，由于您是应用程序的唯一用户，允许200个同时连接，有一些奇怪的事情发生...

---

好，考虑太多连接和 max_connections ...

如果我没记错， max_connections 不会限制连接数你可以打开到MySQL服务器，但连接到该服务器的任何人 可以打开的总连接数。

在连接数过多上引用MySQL文档：

如果您尝试连接到
mysqld服务器时出现太多连接
错误，所有
可用连接都被
其他客户端使用。

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

因此，实际上，问题可能不是来自你或你的代码（看起来不错，实际上）：它可能只是是你不是唯一的试图连接到该MySQL服务器（记住，共享主机），并且有太多的人在同一时间使用...

...并且如果我是对的，这是，没有什么可以做解决这个问题：只要有太多的数据库/用户在该服务器和 max_connection 设置为200，您将继续受苦...

作为旁注：回到GoDaddy问他们，这将是很好，如果有人可以验证我刚才说的^^

I am using MySQL 5.0 for a site that is hosted by 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.

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()).

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?

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

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?

解决方案

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.

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

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

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

Two tips :

• use mysql_connect insead 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) :

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.

---

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_connect and 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 :-(

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...

---

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

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

Quoting MySQL's documentation on 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.

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...

... 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_connection is set to 200, you will continue suffering...

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

这篇关于MySQL错误“太多连接”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！