Author: Hong Hong

The test director of DBLE project leads the test of distributed middleware, and constantly finds products and their own bug s in the test. Iterative verification, enjoy it.

Source: original contribution

*It is produced by aikesheng open source community. The original content cannot be used without authorization. For reprint, please contact Xiaobian and indicate the source.

This paper will do a forward test on the following three scenarios

• Case1. When the MySQL configuration file and mysqld.service both declare the maximum number of files that can be opened, which takes effect?
• Case2. How can the maximum number of files that can be opened be configured in the MySQL configuration file take effect?
• case3. If the limit on the maximum number of files in mysqld service is deleted, who is controlling it?

Test environment information:

1. docker container, host kernel: 4.15.0-36-generic, systemd version: systemd-219-42.el7_ 4.4.x86_ sixty-four

2. mysql-5.7.25

Test preparation:

Install MySQL instance through DMP operation and maintenance platform,

The file descriptors in the MySQL configuration file are limited to:

[root@udp2 3306]# grep -i open_files_limit /opt/mysql/etc/3306/my.cnf
open_files_limit = 65535

The file descriptors configured in the registered mysqld service are limited to:

[root@udp2 3306]# grep -i limitnofile /etc/systemd/system/mysqld_3306.service
LimitNOFILE = 65535

View the resource limit of mysqld through / proc / {replace with PID of MySQL} / limits:

The current resource limit of mysqld process is 65535, but I don't know which limit is working.

Case1. When the MySQL configuration file and mysqld.service both declare the maximum number of files that can be opened, which takes effect?

Step 1. Modify the configuration of the restriction and restart mysqld

Modify configuration
mysql configuration: configure open under [mysqld] of my.cnf_ files_ limit = 65536 In mysqld's Service file, configure under [Service]: LimitNOFILE = 65534

Step 2. Restart mysqld service:

Restart service
systemctl daemon-reload systemctl restart mysqld_3306.service

step3. Check the value of resource limit again through / proc / {replace with PID of MySQL} / limits:

[root@udp2 system]# pgrep mysql
16054
[root@udp2 system]# cat /proc/16054/limits | grep -i "max open files"
Max open files            65534                65534                files

Test conclusion: when both mysqld my.cnf and mysqld service are configured with the maximum number of openable files, the configuration of mysqld.service takes effect.

Case2. How can the maximum number of files that can be opened be configured in the MySQL configuration file take effect?

outline

Some students have doubts about the conclusion of case1, because it is found on the MySQL official website:

Open in MySQL configuration file_ files_ Limit doesn't work. The conclusion of case1 doesn't seem to match the description on the official website. Let's confirm

Step 1. First, let's read the description carefully:

using the value requested at by setting this variable directly or by using the --open-files-limit option to mysqld_safe，
 
To whom is this parameter passed? mysqld_safe. 

Step 2. Let's take a look at the mysqld of the mysqld service started through the DMP platform_ safe :

[root@udp2 system]# ps -ef | grep mysql
root       922 14557  0 16:01 pts/1    00:00:00 grep --color=auto mysql
actiont+ 12178     1  4 15:59 ?        00:00:05 /opt/mysql/base/5.7.25/bin/mysqld --defaults-file=/opt/mysql/etc/3306/my.cnf --daemonize --pid-file=/opt/mysql/data/3306/mysqld.pid --user=actiontech-mysql --socket=/opt/mysql/data/3306/mysqld.sock --port=3306

You can see that there is no mysqld_safe process. mysqld_safe mainly does some guard work for mysqld process. DMP platform provides more powerful high availability, so mysqld is not used_ Safe to start mysqld.

Step 3. Let's test mysqld again_ Safe to start mysqld:

Start command: nohup /opt/mysql/base/5.7.25/bin/mysqld_safe --defaults-file=/opt/mysql/etc/3306/my.cnf --user=actiontech-mysql &

step4. After startup, let's look at the resource constraints:

[root@udp2 system]# ps -ef | grep mysql
root      9267 14557  2 16:01 pts/1    00:00:00 /bin/sh /opt/mysql/base/5.7.25/bin/mysqld_safe --defaults-file=/opt/mysql/etc/3306/my.cnf --user=actiontech-mysql
actiont+ 11437  9267 17 16:01 pts/1    00:00:01 /opt/mysql/base/5.7.25/bin/mysqld --defaults-file=/opt/mysql/etc/3306/my.cnf --basedir=/opt/mysql/base/5.7.25 --datadir=/opt/mysql/data/3306 --plugin-dir=/opt/mysql/base/5.7.25/lib/plugin --user=actiontech-mysql --log-error=/opt/mysql/data/3306/mysql-error.log --open-files-limit=65536 --pid-file=/opt/mysql/data/3306/mysqld.pid --socket=/opt/mysql/data/3306/mysqld.sock --port=3306
root     13082 14557  0 16:02 pts/1    00:00:00 grep --color=auto mysql
[root@udp2 system]#
[root@udp2 system]# cat /proc/11437/limits | grep -i "max open files"
Max open files            65536                65536                files
 
[root@udp2 system]# cat /proc/9267/limits | grep -i "max open files"
Max open files            65536                65536                files

You can see that you are using mysqld_ When safe starts mysqld, the configuration in my.cnf works, which is consistent with the official file description.

Test conclusion: mysqld is not used_ If the resource limit of the mysql instance started by safe is not manually involved, the limit value in the configuration will not take effect.

Test suggestion: there are two points worth noting in passing the test:

1.systemd's service is aimed at the resource limitation of a service level. A service can have one process or multiple processes. Mysqld's service only has mysqld processes. Therefore, the design improvement here is: in mysqld's service, configure the value set in my.cnf to LimitNOFILE. At this time, it can reflect the advantages of the operation and maintenance platform. By changing the MySQL configuration through the operation and maintenance platform, the platform can bring the configuration changes into the mysqld service. If you change it manually, you have to keep in mind the relationship between the two. If you are not careful, you may miss the change.

2. In production, there are often scenarios where services need to be restarted. There are some differences between restarting through the startup command and restarting through the service registered with systemd. For example, the effective configuration of mysqld resource limit here, different startup methods directly affect the actual effective resource limit.

case3. If the limit on the maximum number of files in mysqld service is deleted, who is controlling it?

outline

Now we know that if it's mysqld_ Mysqld started by safe, open_files_limit is limited by mysql configuration. If a mysql D service registered with systemd starts mysql D, the configuration in the service will work.

Registering services with systemd is the mainstream way of service management. If LimitNOFILE is not set in mysqld service, which configuration will control the resource limit?

step1. Add # comments at the beginning of the line for LimitNOFILE = 65534 in mysqld service, and then restart mysqld service:

systemctl daemon-reload
 
systemctl restart mysqld_3306.service

Step 2. After the startup is completed, let's look at the limit of the number of open files:

[root@udp2 system]# pgrep mysql
6700
[root@udp2 system]# cat /proc/6700/limits  | grep -i "max open files"
Max open files            1048576              1048576              files

Now the effective value of mysqld's open files limit is 1048576. Where does this value come from?

Step 3. System D limits resources through cgroup. The hierarchical structure of resource restriction can be viewed through the command SYSTEMd CGLS. Let's take a look at the current resource restriction structure (only the hierarchical structure related to mysqld is displayed, and others are deleted manually):

[root@udp2 system]# systemd-cgls
├─    1 /usr/sbin/init
├─ 7000 systemd-cgls
├─ 7002 less
├─14557 bash
└─system.slice
  ├─mysqld_3306.service
  │ └─6700 /opt/mysql/base/5.7.25/bin/mysqld --defaults-file=/opt/mysql/etc/3306/my.cnf --daemonize --pid-file=/opt/mysql/data/3306/mysqld.pid --user=actiontech-mysql --socket=/opt/mysql/data/3306/mysqld.sock --port=3306

You can see that mysqld is directly restricted by system.slice

step4. We have introduced it at the beginning. We tested it in the docker environment. Exit the container and view the hierarchy of resource restrictions of docker container for testing on the host computer:

Where udp-2 is the container name for testing

mysqld_ 3306. The parent level of the service is system.slice, and the upper level is the container id. these two are generated by docker and are not controlled by us. We should not overwrite the resource limit for no reason. Therefore, let's look at the upper level: the upper level is docker, which has a registration service and can be set by users. Let's take a look at the limit value of open flights limit in this configuration:

root@ubuntu:~# grep -i "limitnofile" /lib/systemd/system/docker.service
LimitNOFILE=1048576

At this point, we can preliminarily guess that the resource limitation of docker service is working

step5. The conclusion of the previous step is inferred. Now let's verify it:

step5.1 modify docker service Maximum number of open files in:
set up LimitNOFILE=1048577，
  
step5.2 Reload configuration and restart service:
systemctl daemon-reload
systemctl restart docker.service

step5.3 Then restart mysqld Container and mysqld After service, view resource limits:
[root@udp2 opt]# pgrep mysql
22339
[root@udp2 opt]# cat /proc/22339/limits | grep -i "max open files"
Max open files 1048577 1048577 files

At this point, when we found and confirmed that the maximum number of open files is not configured in mysqld service, the source of magic 1048576 is the docker.service guessed in the previous step.

Test conclusion: if it is not set in the service, it depends on the setting of the parent of the hierarchical structure of resource restriction or the parent at a higher level to determine who is controlling the maximum number of files that can be opened.

Note: cgroup is a function provided by red hat 6. These test conclusions in this paper may be related to a specific system

reference:

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

https://access.redhat.com/documentation/en-us/red_hat_enterprise_linux/7/html-single/resource_management_guide/index