MySQL Bugs: #70754: open_files

Bug #70754	open_files_limit not honoured
Submitted:	28 Oct 2013 17:14	Modified:	30 Oct 2013 16:45
Reporter:	Chris Alemany	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Options	Severity:	S2 (Serious)
Version:	5.6.14	OS:	MacOS (10.8.x)
Assigned to:		CPU Architecture:	Any
Tags:	FILES, my.cnf, Options

Description:
When trying to address persistent dropped connections from 10000+ table database I have come across this behaviour which does not allow me to increase the open_files_limit passed 10000 (10025 reported).  If a value of 50000 is used in my.cnf server ignores with warning:

"[Warning] Buffered warning: Could not increase number of max_open_files to more than 256 (request: 50000)"

And open_files_limit subsequently set to 256.

If open_files_limit set to 10,000... limit is properly set to 10025.

How to repeat:
Create database (user case is Wordpress multi-site database) with more than 100 sites and 10000 tables.

Set open_files_limit to >10000

MySQL server can allocate only as many file handles as the operating system allows. Setting of this variable is very simple. There is only a call to one standard OS C API function. So, this is all entirely upon the operating system.

On my OS X, file limit is set to 2560. It can be increased so that your request is satisfied and for the OS X, I recommend the following procedure:

http://docs.basho.com/riak/latest/ops/tuning/open-files-limit/#Mac-OS-X

I must note very strongly that I have not tried this procedure and that it might not be applicable for your version of OS X, but most likely it should work.