参数说明
open_files_limit参数限制了mysqld进程能够打开的操作系统文件描述符(fd)的最大数量,如果没有显式设置这个参数值,它的默认值取如下四种值中的最大值(版本>=5.6.8):
- 10 + max_connections + (table_open_cache * 2)
- max_connections * 5
- 操作系统设置的open files的上限值(启动mysqld的操作系统用户的ulimit -n)
- 5000
如果显式设置了open_files_limit参数的值,则它的真实值为下面三种值中的最大值(版本>=5.6.8):
- 10 + max_connections + (table_open_cache * 2)
- max_connections * 5
- 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