【MySQL】mysql关于max_connections

一、MySQL报错

ERROR 1040 (HY000): Too many connections

二、原因

用户连接数达到了max_connections的值，再连接就会报此错。

三、关于max_connections

实际上允许max_connections + 1个客户端连接，额外的那一个连接是需要 SUPER权限才可以登录的，即如果usera有super权限，则它可以额外有一个连接额度，但如果root已经登录上了，则usera即使有super也是登不上的了。

四、示例如下

mysql> show global variables like ‘%max_connections%’;
±----------------±------+
| Variable_name | Value |
±----------------±------+
| max_connections | 3 |
±----------------±------+

mysql> show processlist;
±—±--------±--------------------±-----±--------±-----±---------±-----------------+
| Id | User | Host | db | Command | Time | State | Info |
±—±--------±--------------------±-----±--------±-----±---------±-----------------+
| 33 | mydbmon | 192.168.56.57:22141 | NULL | Sleep | 320 | | NULL |
| 34 | mydbmon | 192.168.56.57:22143 | NULL | Query | 0 | starting | show processlist |
| 40 | mydbmon | 192.168.56.57:22153 | NULL | Sleep | 328 | | NULL |
±—±--------±--------------------±-----±--------±-----±---------±-----------------+
3 rows in set (0.00 sec)

若mydbmon没有super权限，则此时只能保持3个连接，剩下一个连接只能其它super登录（比如root）。
但若root已经先登录了，然后开始连接mydbmon，则mydbmon只能连接2个（因为root已经先占了一个名额）。

若mydbmon有super权限，则可以保持4个连接，如下：
mysql> show processlist;
±—±--------±--------------------±-----±--------±-----±---------±-----------------+
| Id | User | Host | db | Command | Time | State | Info |
±—±--------±--------------------±-----±--------±-----±---------±-----------------+
| 33 | mydbmon | 192.168.56.57:22141 | NULL | Query | 0 | starting | show processlist |
| 34 | mydbmon | 192.168.56.57:22143 | NULL | Sleep | 20 | | NULL |
| 40 | mydbmon | 192.168.56.57:22153 | NULL | Sleep | 348 | | NULL |
| 42 | mydbmon | 192.168.56.57:22159 | NULL | Sleep | 6 | | NULL |
±—±--------±--------------------±-----±--------±-----±---------±-----------------+
4 rows in set (0.00 sec)

五、官方说明

https://dev.mysql.com/doc/refman/5.7/en/connection-interfaces.html

mysqld actually permits max_connections + 1 client connections. The extra connection is reserved for use by accounts that have the SUPER privilege. By granting the privilege to administrators and not to normal users (who should not need it), an administrator who also has the PROCESS privilege can connect to the server and use SHOW PROCESSLIST to diagnose problems even if the maximum number of unprivileged clients are connected. See Section 13.7.5.29, “SHOW PROCESSLIST Statement”.

六、增加max_connections的注意事项

增加max_connections会增加mysqld对文件描述符的需要，所以也需要适当增大文件描述符；
可能还需要增加open_files_limit 参数。

8.4.3.1 How MySQL Opens and Closes Tables
https://dev.mysql.com/doc/refman/5.7/en/table-cache.html

（1）open_files_limit

设置open_files_limit 值参考以下公式：
（1）10 + max_connections + (table_open_cache * 2)

（2）max_connections * 5

（3）Operating system limit if that limit is positive but not Infinity

（4）If operating system limit is Infinity: open_files_limit value if specified at startup, 5000 if not

The server attempts to obtain the number of file descriptors using the maximum of those values. If that many descriptors cannot be obtained, the server attempts to obtain as many as the system will permit.
----服务器尝试获取以上公式中的最大值。

The effective value is 0 on systems where MySQL cannot change the number of open files.

On Unix, the value cannot be set greater than ulimit -n.

（2）table_open_cache

参数解释：
The number of open tables for all threads. Increasing this value increases the number of file descriptors that mysqld requires.
所有线程打开的表的数量。增加这个值会增加mysqld需要的文件描述符的数量。

table_open_cache is related to max_connections. For example, for 200 concurrent running connections, specify a table cache size of at least 200 * N, where N is the maximum number of tables per join in any of the queries which you execute. You must also reserve some extra file descriptors for temporary tables and files.
—table_open_cache与max_connections有关，一般来说，设置table_open_cache=max_connections * N，N是执行SQL中关联的表的最大数量。同时还要为临时表和文件预留一些额外的文件描述符。

Also take into account that the MyISAM storage engine needs two file descriptors for each unique open table. For a partitioned MyISAM table, two file descriptors are required for each partition of the opened table. (When MyISAM opens a partitioned table, it opens every partition of this table, whether or not a given partition is actually used. See MyISAM and partition file descriptor usage.) To increase the number of file descriptors available to MySQL, set the open_files_limit system variable. See Section B.4.2.17, “File Not Found and Similar Errors”.
—还要考虑到，MyISAM存储引擎需要为每个惟一的打开表提供两个文件描述符。对于一个分区的MyISAM表，打开的表的每个分区都需要两个文件描述符。(当MyISAM打开一个分区表时，它会打开该表的每个分区，不管是否实际使用了给定的分区。参见MyISAM和分区文件描述符用法。)要增加MySQL可用的文件描述符数量，请设置open_files_limit系统变量。参见B.4.2.17节，“文件未找到和类似错误”

mysql> SHOW GLOBAL STATUS LIKE ‘Opened_tables’;
±--------------±------+
| Variable_name | Value |
±--------------±------+
| Opened_tables | 2741 |
±--------------±------+
If the value is very large or increases rapidly, even when you have not issued many FLUSH TABLES statements, increase the table_open_cache value at server startup.
—如果Opened_tables值非常大且增长很快，甚至当你还没执行flush tables时。
则在启动mysql时增加table_open_cache的值。