open_files_limit参数真实值测试

参数说明

open_files_limit参数限制了mysqld进程能够打开的操作系统文件描述符(fd)的最大数量，如果没有显式设置这个参数值，它的默认值取如下四种值中的最大值(版本>=5.6.8)：

1. 10 + max_connections + (table_open_cache * 2)
2. max_connections * 5
3. 操作系统设置的open files的上限值(启动mysqld的操作系统用户的ulimit -n)
4. 5000

如果显式设置了open_files_limit参数的值，则它的真实值为下面三种值中的最大值(版本>=5.6.8)：

1. 10 + max_connections + (table_open_cache * 2)
2. max_connections * 5
3. open_files_limit显式设置的值

事实上，mysqld内部是使用setrlimit系统调用函数来设置自己的资源使用限制，如果它设置的值超过操作系统limit限制，则setrlimit会报错，上限即以OS的limit限制为准；如果它低于OS的limit限制，则以它设置的值为准。但是如果mysqld是使用root启动的，则不会发生此类情况

open_files_limit是全局静态参数，因此即使修改了动态参数table_open_cache或max_connections，open_files_limit的值也不会立即变化，只有重启mysqld以后才生效

注意使用ulimit -n XXX修改操作系统limit仅对当前会话生效，该命令可以写在/etc/profile中，使每个会话登录时自动执行，也可以在/etc/security/limits.conf文件中进行配置(nofile，最大1048576)

---

测试

初始环境：未显式设置open_files_limit参数值的大小

[root@rhel6 ~]# cat /etc/my.cnf | grep limit
[root@rhel6 ~]# ulimit -n
1024

mysql> show variables like 'table_open_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| table_open_cache | 2000  |
+------------------+-------+
1 row in set (0.72 sec)

mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.00 sec)

--------------------------------------------------------------------------
10 + max_connections + (table_open_cache * 2) = 10 + 151 + 2000 * 2 = 4161
max_connections * 5 = 151 * 5 = 755
操作系统上限1024
--------------------------------------------------------------------------

根据规律，open_files_limit值应当为5000，验证结果
mysql> show variables like 'open_files_limit';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 5000  |
+------------------+-------+
1 row in set (0.00 sec)

设置table_open_cache=3000并重启mysqld

[root@rhel6 ~]# cat /etc/my.cnf | grep cache
table_open_cache=3000
[root@rhel6 ~]# service mysqld restart
Shutting down MySQL..                                      [  OK  ]
Starting MySQL.                                            [  OK  ]

--------------------------------------------------------------------------
10 + max_connections + (table_open_cache * 2) = 10 + 151 + 3000 * 2 = 6161
max_connections * 5 = 151 * 5 = 755
操作系统上限1024
--------------------------------------------------------------------------

mysql> show variables like 'open_files_limit';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 6161  |
+------------------+-------+
1 row in set (0.00 sec)

设置max_connections=2000并重启mysqld(table_open_cache恢复成默认值2000)

[root@rhel6 ~]# cat /etc/my.cnf | grep connections
max_connections=2000
[root@rhel6 ~]# service mysqld restart
Shutting down MySQL..                                      [  OK  ]
Starting MySQL.                                            [  OK  ]

--------------------------------------------------------------------------
10 + max_connections + (table_open_cache * 2) = 10 + 151 + 2000 * 2 = 4161
max_connections * 5 = 2000 * 5 = 10000
操作系统上限1024
--------------------------------------------------------------------------

mysql> show variables like 'open_files_limit';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 10000 |
+------------------+-------+
1 row in set (0.00 sec)

设置操作系统ulimit -n 65536并重启mysqld(max_connections恢复成默认值151)

[root@rhel6 ~]# ulimit -n
1024
[root@rhel6 ~]# ulimit -n 65536
[root@rhel6 ~]# ulimit -n
65536
[root@rhel6 ~]# service mysqld restart
Shutting down MySQL..                                      [  OK  ]
Starting MySQL.                                            [  OK  ]

--------------------------------------------------------------------------
10 + max_connections + (table_open_cache * 2) = 10 + 151 + 2000 * 2 = 4161
max_connections * 5 = 151 * 5 = 755
操作系统上限65536
--------------------------------------------------------------------------

mysql> show variables like 'open_files_limit';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 65536 |
+------------------+-------+
1 row in set (0.01 sec)

恢复操作系统ulimit -n 1024，同时恢复table_open_cache和max_connections为默认值，显式设置open_files_limit=10000

[root@rhel6 ~]# ulimit -n 1024
[root@rhel6 ~]# ulimit -n
1024
[root@rhel6 ~]# cat /etc/my.cnf | grep limit
open_files_limit=10000
[root@rhel6 ~]# service mysqld restart
Shutting down MySQL..                                      [  OK  ]
Starting MySQL.                                            [  OK  ]

--------------------------------------------------------------------------
10 + max_connections + (table_open_cache * 2) = 10 + 151 + 2000 * 2 = 4161
max_connections * 5 = 151 * 5 = 755
操作系统上限1024
open_files_limit=10000
--------------------------------------------------------------------------

mysql> show variables like 'open_files_limit';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 10000 |
+------------------+-------+
1 row in set (0.00 sec)

显式设置open_files_limit=200，其它均保持默认值

[root@rhel6 ~]# cat /etc/my.cnf | grep limit
open_files_limit=200
[root@rhel6 ~]# service mysqld restart
Shutting down MySQL..                                      [  OK  ]
Starting MySQL.                                            [  OK  ]


--------------------------------------------------------------------------
10 + max_connections + (table_open_cache * 2) = 10 + 151 + 2000 * 2 = 4161
max_connections * 5 = 151 * 5 = 755
操作系统上限1024
open_files_limit=200
--------------------------------------------------------------------------

mysql> show variables like 'open_files_limit';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 4161  |
+------------------+-------+
1 row in set (0.00 sec)

显式设置open_files_limit=200，同时设置操作系统ulimit -n 65536并重启mysqld

[root@rhel6 ~]# ulimit -n 65536
[root@rhel6 ~]# ulimit -n
65536
[root@rhel6 ~]# cat /etc/my.cnf | grep limit
open_files_limit=200
[root@rhel6 ~]# service mysqld restart
Shutting down MySQL..                                      [  OK  ]
Starting MySQL.                                            [  OK  ]

--------------------------------------------------------------------------
10 + max_connections + (table_open_cache * 2) = 10 + 151 + 2000 * 2 = 4161
max_connections * 5 = 151 * 5 = 755
操作系统上限65536
open_files_limit=200
--------------------------------------------------------------------------

mysql> show variables like 'open_files_limit';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 4161  |
+------------------+-------+
1 row in set (0.00 sec)

reference

https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_open_files_limit