Shulou(Shulou.com)05/31 Report--

This article shares with you the content of an example analysis of the maximum number of files open by mysql system users. Xiaobian thinks it is quite practical, so share it with everyone for reference. Let's follow Xiaobian and have a look.

mysql is installed under mysql users, although the database parameters are tuned when installed, but the operating system level has not been adjusted, so mysql users are limited to the maximum number of open files by default 1024, with ulimit -n can query. Then when I logged in to the database with mysql root account, I also logged in under mysql this system user, and then looked at the load of the server at that time, cpu and memory, which were normal, but there were a lot of connections applied to the database.

The problem should be clear here, the system user mysql file open number may have reached the maximum limit, of course, can not open more connections.

However, I didn't think of this at the time. What I thought of was not to change the system user login, not to stop the application, but to restart the database... And this database runs more than just this business, although it is not an important business...

So I'm ready to restart the database, still executing mysqladmin -uroot -p shutdown under mysql. There is no doubt that this is definitely no response, the reason is the same as the previous root account can not connect to the database, ctrl+C after the following error

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

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

When I logged on to the database again today, I found that several parameters were different from those written in my configuration file, such as max_connections, table_open_cache, etc., all of which were set to default values. I looked at the last startup log, and there were indeed alarms.

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 for the current system user, so it did not use this parameter and used the default value. Of course, starting up the database is also available, and you can manually set the parameters after starting up.

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

However, it is very likely that the problem that appeared before me will occur, that is, the number of database connections has not reached the max_connections limit, and the user is still not connected. It should be noted that under normal circumstances, even if the number of connections is full, mysql will still reserve a connection for root user, that is, root user can log in to the database to view problems.

To solve this problem, simply increase the operating system user mysql limit and add the new limit to the configuration file/etc/security/limits.conf.

mysql soft nofile 32768mysql hard nofile 65535 Thank you for reading! About "mysql system user maximum file open number limit example analysis" This article is shared here, I hope the above content can have a certain help to everyone, so that everyone can learn more knowledge, if you think the article is good, you can share it to let more people see it!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.