Ubuntu 16.04.2. MySQL 5.7.17.
During peek hours, my PHP sites suffer fatal error trying to connect to MySQL. Apache says
Error 24 Out of resources when opening file
Ok makes sense, I get lots of traffic. I read up, and seems my open_files_limit
var of 1,024
is too small (it really is seeing 5000
is default).
So after some reading, I ran mysql --verbose --help | less
to find which config files I should go to. It gave me:
Default options are read from the following files in the given order: /etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf
But, /etc/my.cnf
and ~/.my.cnf
didn't exist.
/etc/mysql/my.cnf
did, and it looks exactly like this:
#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
#
# * IMPORTANT: Additional settings that can override those from this file!
# The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/
When I appended open_files_limit = 2084
to the above /etc/mysql/my.cnf
file. I was unable to sudo service mysql start
MySQL. I had to remove the line, then I could start MySQL.
The only other MySQL configuration file I could find was /etc/mysql/mysql.conf.d/mysqld.cnf
and I tried adding the open_files_limit
line there and restarted, it restarted but the variable didn't chang.
What am I doing wrong? What do I do to increase my open_files_limit
?
UPDATE
Seems the place to change the value was in /lib/systemd/system/mysql.service
Had to add LimitNOFILE=infinity
save, then systemctl daemon-reload
There's lots of readings on the reasonings for this for some setups, and reasons not to use infinity.