What is the Maximum Value for max_connections on Linux?

Oracle Linux Server release 5.7 上安装5.5.20 调整max_connections折腾了很长时间。

MySql 版本：

mysql> select version();
+------------+
| version()  |
+------------+
| 5.5.20-log |
+------------+
1 row in set (0.00 sec)

在my.cnf 中调整了max_connections=1024 ，重启mysql后发现max_connections 居然为214

mysql> show variables like 'max_c%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_connect_errors | 10    |
| max_connections    | 1024  |
+--------------------+-------+
2 rows in set (0.00 sec)

 

为什么是这么个奇怪的数值呢？最后在MOS上找到了答案。

对于linux中的普通用户，这个参数和操作系统的open files  有关。如果是root用户启动mysql就没有这个限制了。

可以用ulimit  -a 查看普通用户 open files 默认值是1024

如果要想增大linux普通用户下启动MySql的 max_connections    可以修改/etc/security/limits.conf

加入如下一行

  mysql hard nofile 32000

参数说明：
mysql is the user for which the limit applies.
hard is whether it is the hard or soft limit.
nofile is the limit to change (number of files).
32000 is the value of the limit.

 

 

详见：MySQL Server: What is the Maximum Value for max_connections on Linux? [ID 1385400.1]

The upper limit for how large you can configure max_connections is largely determined by the operating system in addition to the limit of 100,000 that MySQL sets. On Linux you can make an estimation in the following way:

Linux has a limit called max open files, this is defined "per login" and says the maximum number of files a process can open. The default is 1024 (which you can see using ulimit -n). Using the 1024 as an example you get:

 

MySQL takes the 1024 limit and subtracts the table_open_cache (table_cache in MySQL versions prior to 5.1.3) variable. For example with table_open_cache = 512, this leaves 512 handles left for connections.

Halve this number as you usually require at least 2 file descriptors per connection, so 512/2 = 256
One additional file descriptor is reserved for each connection

The value is lowered a little more as some file descriptors are required for other non-connection specific files and to account for overheads, arriving at a maximum of 214

However when starting MySQL as root, MySQL can raise this limit before changing the user to a non-privileged user, whereas as for a non-root user Linux does not let you raise it for security reasons.