Chongqing, China

0%

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参数值的大小

1
[root@rhel6 ~]# cat /etc/my.cnf | grep limit
2
[root@rhel6 ~]# ulimit -n
3
1024
4
5
mysql> show variables like 'table_open_cache';
6
+------------------+-------+
7
| Variable_name    | Value |
8
+------------------+-------+
9
| table_open_cache | 2000  |
10
+------------------+-------+
11
1 row in set (0.72 sec)
12
13
mysql> show variables like 'max_connections';
14
+-----------------+-------+
15
| Variable_name   | Value |
16
+-----------------+-------+
17
| max_connections | 151   |
18
+-----------------+-------+
19
1 row in set (0.00 sec)
20
21
--------------------------------------------------------------------------
22
10 + max_connections + (table_open_cache * 2) = 10 + 151 + 2000 * 2 = 4161
23
max_connections * 5 = 151 * 5 = 755
24
操作系统上限1024
25
--------------------------------------------------------------------------
26
27
根据规律，open_files_limit值应当为5000，验证结果
28
mysql> show variables like 'open_files_limit';
29
+------------------+-------+
30
| Variable_name    | Value |
31
+------------------+-------+
32
| open_files_limit | 5000  |
33
+------------------+-------+
34
1 row in set (0.00 sec)

设置table_open_cache=3000并重启mysqld

1
[root@rhel6 ~]# cat /etc/my.cnf | grep cache
2
table_open_cache=3000
3
[root@rhel6 ~]# service mysqld restart
4
Shutting down MySQL..                                      [  OK  ]
5
Starting MySQL.                                            [  OK  ]
6
7
--------------------------------------------------------------------------
8
10 + max_connections + (table_open_cache * 2) = 10 + 151 + 3000 * 2 = 6161
9
max_connections * 5 = 151 * 5 = 755
10
操作系统上限1024
11
--------------------------------------------------------------------------
12
13
mysql> show variables like 'open_files_limit';
14
+------------------+-------+
15
| Variable_name    | Value |
16
+------------------+-------+
17
| open_files_limit | 6161  |
18
+------------------+-------+
19
1 row in set (0.00 sec)

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

1
[root@rhel6 ~]# cat /etc/my.cnf | grep connections
2
max_connections=2000
3
[root@rhel6 ~]# service mysqld restart
4
Shutting down MySQL..                                      [  OK  ]
5
Starting MySQL.                                            [  OK  ]
6
7
--------------------------------------------------------------------------
8
10 + max_connections + (table_open_cache * 2) = 10 + 151 + 2000 * 2 = 4161
9
max_connections * 5 = 2000 * 5 = 10000
10
操作系统上限1024
11
--------------------------------------------------------------------------
12
13
mysql> show variables like 'open_files_limit';
14
+------------------+-------+
15
| Variable_name    | Value |
16
+------------------+-------+
17
| open_files_limit | 10000 |
18
+------------------+-------+
19
1 row in set (0.00 sec)

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

1
[root@rhel6 ~]# ulimit -n
2
1024
3
[root@rhel6 ~]# ulimit -n 65536
4
[root@rhel6 ~]# ulimit -n
5
65536
6
[root@rhel6 ~]# service mysqld restart
7
Shutting down MySQL..                                      [  OK  ]
8
Starting MySQL.                                            [  OK  ]
9
10
--------------------------------------------------------------------------
11
10 + max_connections + (table_open_cache * 2) = 10 + 151 + 2000 * 2 = 4161
12
max_connections * 5 = 151 * 5 = 755
13
操作系统上限65536
14
--------------------------------------------------------------------------
15
16
mysql> show variables like 'open_files_limit';
17
+------------------+-------+
18
| Variable_name    | Value |
19
+------------------+-------+
20
| open_files_limit | 65536 |
21
+------------------+-------+
22
1 row in set (0.01 sec)

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

1
[root@rhel6 ~]# ulimit -n 1024
2
[root@rhel6 ~]# ulimit -n
3
1024
4
[root@rhel6 ~]# cat /etc/my.cnf | grep limit
5
open_files_limit=10000
6
[root@rhel6 ~]# service mysqld restart
7
Shutting down MySQL..                                      [  OK  ]
8
Starting MySQL.                                            [  OK  ]
9
10
--------------------------------------------------------------------------
11
10 + max_connections + (table_open_cache * 2) = 10 + 151 + 2000 * 2 = 4161
12
max_connections * 5 = 151 * 5 = 755
13
操作系统上限1024
14
open_files_limit=10000
15
--------------------------------------------------------------------------
16
17
mysql> show variables like 'open_files_limit';
18
+------------------+-------+
19
| Variable_name    | Value |
20
+------------------+-------+
21
| open_files_limit | 10000 |
22
+------------------+-------+
23
1 row in set (0.00 sec)

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

1
[root@rhel6 ~]# cat /etc/my.cnf | grep limit
2
open_files_limit=200
3
[root@rhel6 ~]# service mysqld restart
4
Shutting down MySQL..                                      [  OK  ]
5
Starting MySQL.                                            [  OK  ]
6
7
8
--------------------------------------------------------------------------
9
10 + max_connections + (table_open_cache * 2) = 10 + 151 + 2000 * 2 = 4161
10
max_connections * 5 = 151 * 5 = 755
11
操作系统上限1024
12
open_files_limit=200
13
--------------------------------------------------------------------------
14
15
mysql> show variables like 'open_files_limit';
16
+------------------+-------+
17
| Variable_name    | Value |
18
+------------------+-------+
19
| open_files_limit | 4161  |
20
+------------------+-------+
21
1 row in set (0.00 sec)

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

1
[root@rhel6 ~]# ulimit -n 65536
2
[root@rhel6 ~]# ulimit -n
3
65536
4
[root@rhel6 ~]# cat /etc/my.cnf | grep limit
5
open_files_limit=200
6
[root@rhel6 ~]# service mysqld restart
7
Shutting down MySQL..                                      [  OK  ]
8
Starting MySQL.                                            [  OK  ]
9
10
--------------------------------------------------------------------------
11
10 + max_connections + (table_open_cache * 2) = 10 + 151 + 2000 * 2 = 4161
12
max_connections * 5 = 151 * 5 = 755
13
操作系统上限65536
14
open_files_limit=200
15
--------------------------------------------------------------------------
16
17
mysql> show variables like 'open_files_limit';
18
+------------------+-------+
19
| Variable_name    | Value |
20
+------------------+-------+
21
| open_files_limit | 4161  |
22
+------------------+-------+
23
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