Is the MySQL open_files_limit variable creating errors? We can help you.

In MySQL, open_files_limit is the number of file descriptors available to mysqld from the operating system.

At Bobcares, we often get requests to set MySQL variables, as a part of our Server Management Services.

Today, our Support Engineers will give an idea about how open_files_limit in MySQL relates to max_connections.

What is open_files_limit in MySQL?

A file descriptor is the number of open files in the server. In MySQL, we have a variable that stores the number of file descriptors available for mysqld and that is, open_files_limit.

MySQL server needs file descriptors to open new connections, store tables in the cache, create temporary tables, etc.

The open_files_limit is a soft limit set by MySQL. The operating system determines the hard limit on file descriptors.

That is why, even if the open_files_limit variable is set to a high value, mysqld service shows the warning,

[Warning] Could not increase number of max_open_files to more than xxxx

So, let’s see how to set this limit in MySQL.

How to set open_files_limit in MySQL?

Many operating systems permit us to increase the open_files_ limit. But the method varies widely from system to system. For systemd based systems, we edit it as follows,

Firstly, edit the MySQL configuration file in the server. That is, /etc/my.cnf,

Then add the following lines:

[Service]
LimitNOFILE=8192

And finally, restart the server.

systemctl restart mysql

Later we check the variable in MySQL.

How is this related to max_connections?

Recently, one of our customers approached us saying that he is not able to increase his max_connections value above 214.

He had specified the value in MySQL configuration and it is automatically decreased to the default value 151.

So, our Support Engineers checked in detail and found the exact problem.

Usually, increasing the value of max_connections increases the number of file descriptors that mysqld requires.

If the open_files_limit is not available, the server reduces the value of max_connections.

The table_open_cache and max_connections system variables affect the maximum number of files the server keeps open.

If we increase one of these values, we may end up at the limit imposed by the OS on the number of open file descriptors per process.

So, at first, we increased the open_files_limit and tried increasing the value of max_connections.

Finally, this helped to fix the problem.

[Having trouble with MySQL servers? Our Engineers can fix it.]

Conclusion

In short, open_files_limit in MySQL is the number of file descriptors available for the mysqld process. Today, we saw our Support Engineers increased this limit to increase the max_connections variable in MySQL.