A brief discussion on the maximum number of open files for MySQL system users

What you learn from books is always shallow, and you will never know that this will cause more downtime... I will record a stupid troubleshooting process of mine.

Last week, a system that had just been launched started to report that it could not be logged in again. Because this system has been having this problem recently and the developers have been looking for the problem, we didn't pay much attention to it. So I logged into the operating system, used mysql -uroot -p to log into the database, and then nothing happened and I couldn't log in...

To explain, MySQL is installed under the mysql user. Although the database parameters are tuned during installation, no adjustments are made at the operating system level. Therefore, the maximum number of open files for the mysql user is limited to the default 1024, which can be queried using ulimit -n. Then when I logged into the database with the mysql root account, I also logged in under the mysql system user, and then looked at the server load at the time. The CPU and memory were all normal, but there were a large number of applications connecting to the database.

At this point the problem should be clear. The number of mysql files opened by the system user may have reached the maximum limit, and of course no more connections can be opened.

However, I didn't think of this at the time. What I thought of was not to change the system user to log in, not to stop the application, but to restart the database. . . Moreover, this database runs more than just this one business, although none of them are important businesses. . .

So I prepared to restart the database, still executing mysqladmin -uroot -p shutdown under the mysql user. There is no doubt that this will definitely not respond. The reason is the same as the previous root account cannot connect to the database. After ctrl+C, the following error is reported

^Cmysqladmin: connect to server at 'localhost' failed
error: 'Lost connection to MySQL server at 'waiting for initial communication packet', system error: 4'

Then I did something even more stupid, even though I thought I might lose data, and I killed the mysql process. . . Then restart mysql and the system will be available. It was really stupid. After I finished it, I immediately remembered that there were many better ways to deal with it, but I chose the stupidest one.

When I logged into the database again today, I found that several parameters were different from what I wrote in the configuration file, such as max_connections, table_open_cache, etc., which were all set to the default values. I looked at the last startup log and there were indeed warnings.

2019-03-15T08:14:03.038750Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 12010)
2019-03-15T08:14:03.038911Z 0 [Warning] Changed limits: max_connections: 214 (requested 2000)
2019-03-15T08:14:03.038916Z 0 [Warning] Changed limits: table_open_cache: 400 (requested 5000)

Obviously, mysql calculated the maximum number of files that the instance needs to open based on the parameter setting, which exceeds the maximum limit of the current system user, so the parameter is not used and the default value is used. Of course, the database is available after startup, and you can also manually set the parameters after startup.

set global max_connections=2000;
set global table_open_cache=5000;

However, the problem I encountered before is likely to occur, that is, the number of database connections does not reach the max_connections limit, and the user still cannot connect. It should be noted that under normal circumstances, even if the number of connections is full, MySQL will still reserve a connection for the root user, which means that the root user can log in to the database to view the problem.

The solution is also very simple. Just increase the limit value of the operating system user mysql and add the new limit value after the configuration file /etc/security/limits.conf.

mysql soft nofile 32768
mysql hard nofile 65535

The above is a detailed explanation of the maximum number of open files for MySQL system users that I introduced to you. I hope it will be helpful to you. If you have any questions, please leave me a message and I will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!