Errno 24, Too many Files Open - MySQL V 5.7.33

Question

I have an issues with my MySQL server where we are getting consistent "Too Many Files Open" errors. Our current "Open Files Limit" field is set to 1024 and our Open Files never gets above 500.

I think the issue is because of the innodb_open_files variable which is stuck at 400 and I'm unable to find a way to increase that value. I seem to always be getting close too, or reaching this limit every time I have this issue.

I often have a lot of users connecting to this server.

This is my my.cnf file

open_files_limit = 1024
max_connections = 1024
innodb_open_files = 1023

Any suggestions to increase this value?

Answer 1

In some OSs, the number of files allowed for a process is only 1024, as reflected by this inside MySQL

open_files_limit = 1024

The fix is to change the OS setting via ulimit before starting mysqld:

ulimit -n 4096

Or... Append these two lines to /etc/security/limits.conf

mysql hard nofile 65535
mysql soft nofile 65535

The "500" may be because of more than one "file" needing to be opened for each "table".