Could not increase number of max_open_files to more than 12000

Exploring the MySQL error log file on a cPanel server I found this odd message: Could not increase number of max_open_files to more than 12000.  This was happening on a CentOS 7 + cPanel + CloudLInux server.

What is MySQL open files limit directive?

In simple words: open_files_limit directive from MYSQL is the maximum # of files allowed to be opened by mysqld, this limit is set by the operating system.

How can I  increase number of max_open_files for MySQL?

After taking a look at my /etc/my.cnf configuration, I found that there was a MySQL variable that was indeed causing this issue:

The exact error on MYSQL log was this:

170516 0:16:20 [ERROR] Error in accept: Too many open files
170516 0:20:36 [ERROR] Error in accept: Too many open files
170516 0:24:52 [ERROR] Error in accept: Too many open files
170516 0:25:16 [Warning] Could not increase number of max_open_files to more than 10000 (request: 47632)

Started investigating and took a look at the open_files_limit directive from MySQL stats:

[[email protected]:~]mysql -e "show variables like '%open_%'"
+-------------------+----------+
| Variable_name | Value |
+-------------------+----------+
| have_openssl | DISABLED |
| innodb_open_files | 300 |
| open_files_limit | 12000 |
| table_open_cache | 23536 |
+-------------------+----------+

It was set to 12000 as you see in there.

I even tried to see what was the value for open files using ulimit command for mysql:

[[email protected]:~]ulimit -a mysql
core file size (blocks, -c) 1000000
data seg size (kbytes, -d) unlimited
scheduling priority (-e) 0
file size (blocks, -f) unlimited
pending signals (-i) 63347
max locked memory (kbytes, -l) 64
max memory size (kbytes, -m) unlimited
open files (-n) 4096
pipe size (512 bytes, -p) 8
POSIX message queues (bytes, -q) 819200
real-time priority (-r) 0
stack size (kbytes, -s) 8192
cpu time (seconds, -t) unlimited
max user processes (-u) 14335
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited

But that showed a different value than 12000, it was not related, discarded.

Later I’ve found that 12000 value in another file:

[[email protected]:~]cat /etc/systemd/system/mysql.service.d/limits.conf
[Service]
LimitNOFILE=65535

After changing that value, I went back to edit /etc/my.cnf and set my new value:

[[email protected]:~]grep open_files /etc/my.cnf
open_files_limit=65535
[[email protected]:~]

Applied changes:

systemctl daemon-reload
service mysql restart

That did the trick.

Another possible solution I’ve found on some forums, in case it can help you:

nano -w /etc/systemd/system/mysql.service

Then set the number higher, as you see below:

[[email protected]:~]cat /etc/systemd/system/mysql.service
[Unit]
Description=MySQL Server
ConditionPathExists=!/etc/mysqldisable

[Service]
Type=simple
ExecStart=/usr/bin/mysqld_safe
User=mysql
Group=mysql
WorkingDirectory=/usr
LimitNOFILE=65536

[Install]
WantedBy=multi-user.target

After that, run this reload and restart to apply changes:

systemctl daemon-reload
service mysql restart

Let’s take a look again on how MySQL is now using the open files limit directive:

[[email protected]:~]mysql -e "show variables like '%open_%'"
+-------------------+----------+
| Variable_name | Value |
+-------------------+----------+
| have_openssl | DISABLED |
| innodb_open_files | 300 |
| open_files_limit | 65535 |
| table_open_cache | 23536 |
+-------------------+----------+

As you see, the value increased, no more errors found on the logs.

Further reading:

• MySQL open files limit