MySQL 错误“连接太多"

2022-01-24 00:00:00 connection php mysql

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

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

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

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.

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

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

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

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?

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

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

谢谢.

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

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

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

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.

您的代码所做的是:

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

最后一步，在页面末尾完成时不是强制 : (引用 mysql_close 的手册):

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.

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

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

两个提示:

  • 在 mysql_pconnect 中使用 mysql_connect (你已经可以了)
  • 将mysql_connect的第四个参数设置为false(已经可以了，因为它是默认值):(引用手册):
  • 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) :

如果第二次调用mysql_connect() 同论点，不会有新的链接建立，但相反，链接已打开链接的标识符将被退回.

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.

新链接参数修改此行为并使 mysql_connect() 总是打开一个新链接，即使 mysql_connect() 是之前用同样的方法调用过参数.

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 ?

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

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

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

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


好吧，想想连接太多"和max_connections"...

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

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

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.

在 Too many connections :

如果您的连接过多当您尝试连接到mysqld服务器，这意味着所有可用的连接正在由其他客户.

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.

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

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.

所以，实际上，问题可能不是来自您或您的代码(实际上看起来不错):它可能只是"是您不是唯一一个试图连接到该代码的人MySQL 服务器(记住，共享主机")，而且有太多人同时使用它...

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

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

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


作为旁注:在回到 GoDaddy 询问他们之前，如果有人可以验证我刚才所说的话，那就太好了^^


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

相关文章