1

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.

Improve this question

2 Answers 2

Reset to default
1

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
Improve this answer
4
  • I not 100% sure why, but in some cases * not help for me, changing * for mysql resolve situation
    – a_vlad
    Mar 24, 2017 at 3:03
  • Thank you for your reply. I did that, restarted everything, and ulimit -a showed proper new 2048 number. However, open_files_limit did not change, it's still caped at 1024. I received in mysql/error.log: [Warning] Could not increase number of max_open_files to more than 1024 (request: 4161)
    – David
    Mar 24, 2017 at 15:24
  • 1
    More reading. Seems like I have to next edit /lib/systemd/system/mysql.service adding LimitNOFILE=infinity then systemctl daemon-reload and restart mysql. I will try that this evening.
    – David
    Mar 24, 2017 at 16:05
  • Confirmed. Above finally changed that number. I guess infinity = 65,536, which is what open_files_limit now is
    – David
    Mar 25, 2017 at 2:19
0

Once you have changed the ulimit in the OS, change/add

[mysqld]
open_file_slimit = 2048

and restart.

Note: it must be in the [mysqld] section of my.cnf (or whichever file).

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.