'max_user_connections' 设置为 200 - 仍然出现错误 [英] 'max_user_connections' set to 200 - still getting error

问题描述

这里是mysql错误:连接失败:用户db2498"已超出max_user_connections"资源(当前值:200).

Here is the mysql error: Connect failed: User 'db2498' has exceeded the 'max_user_connections' resource (current value: 200).

我设置了my.cnf:

I set the my.cnf:

[mysqld]
max_connections = 500
max_user_connections = 200

我也将 mysql 中用户的 max_user_connections 设置为 200.我已经有 1400 人在大约 10-20 分钟内访问了该站点.每个平均停留 14 秒，我收到了大约 1400 条这样的消息.

I set the max_user_connections in mysql for the user to 200 also. I've had 1400 people hit the site in about 10-20 minutes. Each stay on for an average of 14 seconds, and I got about 1400 of these messages.

我正在使用 PHP/Mysql.这是数据库类:

I'm using PHP/Mysql. This is the database class:

class DB{

public function __construct(){
    $this->conn = new mysqli($this->host,$this->user,$this->pass,$this->db);
    /* check connection */      
}
public function selectSomething(){
    /* select data & return */
}
public function __destruct()
{
$this->conn->close();
} 
}

我是这样称呼它的:

$conn = new DB();
$result = $conn->selectSomething();
/* do something */

$result = $conn->selectSomething();
/* do something */

$result = $conn->selectSomething();
/* do something */

用户在网站上的平均停留时间为 14 秒.为什么我收到这个错误?是破坏吗?我的数据库包装器设置错误吗?我迷路了，技术支持也帮不上什么忙.

The users are on the site for an average of 14 seconds. Why am I getting this error? Is it the destruct? Do I have the database wrapper set up wrong? I'm lost, and tech support isn't much help.

推荐答案

至于您的最大连接数"问题，它可能是以下三件事之一:

As for your "max connections" problem, it could be one of three things:

1) 服务器已经有太多打开的连接.MySQL 服务器在拒绝允许更多连接之前只能处理特定数量的打开连接，并且该限制在服务器的所有用户之间共享.它通常设置得相当高，尽管有人很容易通过建立大量连接来有效地对 MySQL 服务器进行 DoS(但见下文)

1) The server has too many open connections already. A MySQL server can only handle a specific number of open connections before refusing to allow any more, and this limit is shared amongst all users of the server. It's usually set quite high, although it's easily possible for someone to effectively DoS a MySQL server by making lots of connections (but see below)

2) 您的用户帐户每小时允许的连接数量有限——该小时内的任何进一步连接都将被拒绝.这是基于每个用户设置的.

2) Your user account has a limited number of connections allowed per hour - any further connections within that hour would be rejected. This is set on a per-user basis.

3) 您的用户帐户具有有限数量的允许打开连接 - 任何进一步的连接都将被拒绝.这是基于每个用户设置的.

3) Your user accounts has a limited number of allowable open connections - any further connections would be rejected. This is set on a per-user basis.

阅读连接尝试返回的错误消息总是很重要的，因为在大多数情况下，这将查明失败的确切原因.

It's always important to read the error message you get returned on the connection attempt, as in most cases this will pinpoint the exact reason for failure.

如果您的帐户有最大连接数限制(场景 #3)，则错误将是:代码:

If your account has a maximum number of connections limit (scenario #3), the error would be: Code:

ERROR 1226 (42000): User 'mysqldba' has exceeded the 'max_user_connections' resource (current value: 1) 

其中mysqldba"是您的用户名，当前值"是该用户允许的最大打开连接数.

Where 'mysqldba' would be your username, and the 'current value' is the maximum number of open connections allowed from this user.

如果您的帐户有每小时最大连接数限制(场景 #2)，则错误将是:代码:

If you account has a maximum number of connections per hour limit (scenario #2), the error would be: Code:

ERROR 1226 (42000): User 'mysqldba' has exceeded the 'max_connections_per_hour' resource (current value: 1) 

同样，mysqldba"将是您的用户名，当前值"是该用户每小时允许的最大连接数.

Where, once again, 'mysqldba' would be your username, and the 'current value' is the maximum number of connections per hour allowed for this user.

如果您收到错误消息(代码 1040)表明整个 MySQL 服务器的连接槽已用完 - 这就是我上面提到的 DoS 场景.

If you got the error message (code 1040) indicates that the entire MySQL server has run out of connection slots - this is the DoS scenario I mention above.

你能做些什么?根据你所说的，你在这台服务器上没有超级用户权限，所以除了向负责该服务器的系统管理员抱怨外，别无他法.他们可能会增加允许的最大连接数，这可以在短期内解决问题，但是如果其他使用服务器的人创建了愚蠢数量的数据库连接，则插槽将再次填满.他们可能应该做的是还强制执行每个用户的最大打开连接限制 - 这将阻止重度用户阻塞服务器.在像您这样的共享服务器情况下，这将是最有意义的 - 高级用户"将/应该拥有自己的服务器，或者可以/应该付费以增加他们的最大开放连接数.

What can you do about it? From what you've said, you don't have superuser privileges on this server, so nothing, apart from complain to the SysAdmin responsible for that server. They might increase the maximum number of connections allowed, which could solve the problem short-term, but if someone else using the server is creating a stupid number of database connections the slots would just fill up again. What they probably should do is to also enforce a per-user maximum open connection limit as well - this would stop the heavy users clogging up the server. In a shared-server situation like yours, this would make the most sense - 'power users' would/should have their own server, or could/should pay to increase their maximum open connections.

这篇关于'max_user_connections' 设置为 200 - 仍然出现错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！