Troubles with MySQL open_files_limit 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.
It depends on your db architecture. If you have many tables in separate files in the MyISAM, ARIA, or Innodb with files per table you will want a higher open file limit. If you have everything in the Innodb / xtradb in a single tablespace then upping your file limits will not benefit.

Check current system limits

ulimit -a


Temporary limit increase 

ulimit -n 65535


Permanent update /etc/security/limits.conf

The mysql open file limit can't exceed system limits.

* soft nofile 65535
* hard nofile 65535