Guidance:
Author: Wei Xinping, a student in the 5th MySQL battle class of Zhishutang and a student in the 10th MySQL optimization class, is currently a teaching assistant.
1. Official Interpretation
The maximum number of file descriptors the mysqld process can use, and the actual value of mysql will be the largest of the following four values.
10 + maxconnections + (tableopen_cache * 2)
max_connections * 5
operating system limit if positive
if operating system limit is Infinity:
open_files_limit value specified at startup, 5000 if none
Let's next test mysql to take each of these four values as actual values.
2. Testing mysql version and system version
The operating system version is CentOS Linux release 7.4.1708 (Core), which is newly installed without any configuration.
mysql version Percona-Server-5.7.21-20-Linux.x86_64
Now the test begins.
No open_files_limit parameter configured (root user logon operation)
mysql configuration files such as
[mysqld] user = mysql port = 5721 socket = /tmp/mysql_sandbox5721.sock basedir = /root/opt/mysql/5.7.21 datadir = /opt/msb_5_7_21/data tmpdir = /opt/msb_5_7_21/tmp pid-file = /opt/msb_5_7_21/data/mysql_sandbox5721.pid bind-address = 127.0.0.1
The table_open_cache default value is 2000, and the max_connections default value is 151+1, because there is also an extra_max_connections, and ulimit-n has a value of 1024.
give the result as follows
[root@mysqlmaster ~]# ps -ef | grep mysqld root 3047 1 0 09:52 pts/2 00:00:00 /bin/sh bin/mysqld_safe --defaults-file=/opt/msb_5_7_21/my.sandbox.cnf mysql 3253 3047 0 09:52 pts/2 00:00:02 /root/opt/mysql/5.7.21/bin/mysqld --defaults-file=/opt/msb_5_7_21/my.sandbox.cnf --basedir=/root/opt/mysql/5.7.21 --datadir=/opt/msb_5_7_21/data --plugin-dir=/root/opt/mysql/5.7.21/lib/mysql/plugin --user=mysql --log-error=/opt/msb_5_7_21/data/msandbox.err --pid-file=/opt/msb_5_7_21/data/mysql_sandbox5721.pid --socket=/tmp/mysql_sandbox5721.sock --port=5721 root 10583 5177 0 09:57 pts/4 00:00:00 grep --color=auto mysqld [root@mysqlmaster ~]# cat /proc/3253/limits |grep files Max open files 5000 5000 files -------------------------------------------------------------------------------- mysql [localhost:5721] {root} ((none)) > SELECT @@open_files_limit ; +--------------------+ | @@open_files_limit | +--------------------+ | 5000 | +--------------------+ 1 row in set (0.00 sec)
Whether at the system level or mysql, the value is 5000, which fits the fourth scenario
- if operating system limit is Infinity:
openfileslimit value specified at startup, 5000 if none
The values of the other formulas are:
1,select @@max_connections + @@extra_max_connections + 10 + @@table_open_cache*2 = 4162
2,select (@@max_connections+@@extra_max_connections)*5 = 760
3,ulimit -n = 1024
Then let's increase the values of the other formulas one by one to see how the results change.
10 + maxconnections + (tableopen_cache * 2) Max
Set table_open_cache=3000, other unchanged values as follows:
1,select @@max_connections + @@extra_max_connections + 10 + @@table_open_cache*2 = 6162
2,select (@@max_connections+@@extra_max_connections)*5 = 760
3,ulimit -n = 1024
4,5000
The result is
[root@mysqlmaster msb_5_7_21]# ps -ef | grep mysqld root 80009 1 0 10:43 pts/2 00:00:00 /bin/sh bin/mysqld_safe --defaults-file=/opt/msb_5_7_21/my.sandbox.cnf mysql 80227 80009 11 10:43 pts/2 00:00:01 /root/opt/mysql/5.7.21/bin/mysqld --defaults-file=/opt/msb_5_7_21/my.sandbox.cnf --basedir=/root/opt/mysql/5.7.21 --datadir=/opt/msb_5_7_21/data --plugin-dir=/root/opt/mysql/5.7.21/lib/mysql/plugin --user=mysql --log-error=/opt/msb_5_7_21/data/msandbox.err --pid-file=/opt/msb_5_7_21/data/mysql_sandbox5721.pid --socket=/tmp/mysql_sandbox5721.sock --port=5721 root 80555 1129 0 10:43 pts/2 00:00:00 grep --color=auto mysqld [root@mysqlmaster msb_5_7_21]# [root@mysqlmaster msb_5_7_21]# cat /proc/80227/limits |grep files Max open files 6162 6162 files ------------------------------------------------------------------------------------------------ mysql [localhost:5721] {root} ((none)) > SELECT @@open_files_limit ; +--------------------+ | @@open_files_limit | +--------------------+ | 6162 | +--------------------+ 1 row in set (0.00 sec)
Max_connections* 5 Max
Set the value of max_connections to 2000. The other values default to the following:
1,select @@max_connections + @@extra_max_connections + 10 + @@table_open_cache*2 =6011
2,select (@@max_connections+@@extra_max_connections)*5 = 10005
3,ulimit -n = 1024
4,5000
give the result as follows
[root@mysqlmaster msb_5_7_21]# ps -ef | grep mysqld root 87914 1 0 10:48 pts/2 00:00:00 /bin/sh bin/mysqld_safe --defaults-file=/opt/msb_5_7_21/my.sandbox.cnf mysql 88132 87914 21 10:48 pts/2 00:00:01 /root/opt/mysql/5.7.21/bin/mysqld --defaults-file=/opt/msb_5_7_21/my.sandbox.cnf --basedir=/root/opt/mysql/5.7.21 --datadir=/opt/msb_5_7_21/data --plugin-dir=/root/opt/mysql/5.7.21/lib/mysql/plugin --user=mysql --log-error=/opt/msb_5_7_21/data/msandbox.err --pid-file=/opt/msb_5_7_21/data/mysql_sandbox5721.pid --socket=/tmp/mysql_sandbox5721.sock --port=5721 root 88300 1129 0 10:48 pts/2 00:00:00 grep --color=auto mysqld [root@mysqlmaster msb_5_7_21]# cat /proc/88132/limits | grep files Max open files 10005 10005 files ---------------------------------------------------------------------------------------------- mysql [localhost:5721] {root} ((none)) > SELECT @@open_files_limit ; +--------------------+ | @@open_files_limit | +--------------------+ | 10005 | +--------------------+ 1 row in set (0.00 sec)
Operating system limit if positivemax
My understanding is the value ulimit-n displays.max_connections and table_open_cache take the default value, then ulimit-n 6000.Restart mysql.
The formula is as follows:
1,select @@max_connections + @@extra_max_connections + 10 + @@table_open_cache*2 = 4162
2,select (@@max_connections+@@extra_max_connections)*5 = 760
3,ulimit -n = 6000
The results are as follows:
[root@mysqlmaster msb_5_7_21]# ps -ef | grep mysql root 93825 1 0 10:52 pts/2 00:00:00 /bin/sh bin/mysqld_safe --defaults-file=/opt/msb_5_7_21/my.sandbox.cnf mysql 94031 93825 16 10:52 pts/2 00:00:01 /root/opt/mysql/5.7.21/bin/mysqld --defaults-file=/opt/msb_5_7_21/my.sandbox.cnf --basedir=/root/opt/mysql/5.7.21 --datadir=/opt/msb_5_7_21/data --plugin-dir=/root/opt/mysql/5.7.21/lib/mysql/plugin --user=mysql --log-error=/opt/msb_5_7_21/data/msandbox.err --pid-file=/opt/msb_5_7_21/data/mysql_sandbox5721.pid --socket=/tmp/mysql_sandbox5721.sock --port=5721 root 94254 1129 0 10:52 pts/2 00:00:00 grep --color=auto mysql [root@mysqlmaster msb_5_7_21]# cat /proc/94031/limits | grep files Max open files 6000 6000 files --------------------------------------------------------------------------------------- mysql [localhost:5721] {root} ((none)) > SELECT @@open_files_limit ; +--------------------+ | @@open_files_limit | +--------------------+ | 6000 | +--------------------+ 1 row in set (0.00 sec)
3. Configure the open_files_limit parameter (root user logon exercise****as)
The open_files_limit parameter is configured, or the maximum value will be taken.But the difference is that the value of the third formula, ulimit-n, is not taken into account.Instead, the maximum value is obtained from the other three values.
Execute ulimit-n 6000 increase limit, then max_connections,table_open_cache are all defaults.The open_files_limit is set to 4200.Restart mysql
The results are as follows:
[root@mysqlmaster msb_5_7_21]# ps -ef | grep mysqld root 104048 1 0 10:58 pts/2 00:00:00 /bin/sh bin/mysqld_safe --defaults-file=/opt/msb_5_7_21/my.sandbox.cnf mysql 104269 104048 19 10:58 pts/2 00:00:01 /root/opt/mysql/5.7.21/bin/mysqld --defaults-file=/opt/msb_5_7_21/my.sandbox.cnf --basedir=/root/opt/mysql/5.7.21 --datadir=/opt/msb_5_7_21/data --plugin-dir=/root/opt/mysql/5.7.21/lib/mysql/plugin --user=mysql --log-error=/opt/msb_5_7_21/data/msandbox.err --open-files-limit=4200 --pid-file=/opt/msb_5_7_21/data/mysql_sandbox5721.pid --socket=/tmp/mysql_sandbox5721.sock --port=5721 root 104460 1129 0 10:58 pts/2 00:00:00 grep --color=auto mysqld [root@mysqlmaster msb_5_7_21]# cat /proc/104269/limits | grep files Max open files 4200 4200 files ------------------------------------------------------------------ mysql [localhost:5721] {root} ((none)) > SELECT @@open_files_limit ; +--------------------+ | @@open_files_limit | +--------------------+ | 4200 | +--------------------+ 1 row in set (0.00 sec)
The value displayed becomes 4200, obviously 6000 is not taken into account, even if it is greater than 4200.The other cases are the same as when you do not configure open_files_limit, get the maximum value, and don't dwell on the length reasons.
4. Summary
Without the configured open_files_limit, the maximum value of the following formula is obtained.
10 + maxconnections + (tableopen_cache * 2)
max_connections * 5
operating system limit if positive
if operating system limit is Infinity:
open_files_limit value specified at startup, 5000 if none
With open_files_limit configured, the size of the third value is ignored.
Another question here is why ulimit-n did not play a limiting role when it showed 1024.I think the reason is that the user who started mysqld_safe is root and is not restricted by this value.Tested regardless of mysqld's startup user, as long as mysqld_safe startup user is root, it is not restricted.This restriction applies only when mysqld_safe is started by a non-root user.