Bug #77365 mysqld_safe sets ulimit too low
Submitted: 15 Jun 2015 21:18
Reporter: Ike Walker Email Updates:
Status: Open Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.6.22 OS:Any
Assigned to: CPU Architecture:Any

[15 Jun 2015 21:18] Ike Walker 
Description:
mysqld_safe does this:
ulimit -n $open_files

However, $open_files is only ever the value of --open-files-limit, whereas mysqld can attempt to open as many as (innodb_open_files + open_files_limit) files. Therefore, mysqld will sometimes get the error "ERROR 23 (HY000) at line 2: Out of resources when opening file '/mnt/logs/mysql-tmp/#sql_3550_3.MYI' (Errcode: 24 - Too many open files)".

I'm sure the particular file that's attempting to be opened will change, but we tend to see this on temporary files.

How to repeat:
innodb_files_per_table and tons of dbs

Suggested fix:
We have worked around this by setting our open_files_limit to the max we expect to ever need to use including the number of open InnoDB files. The proper fix is to do:

ulimit -n $(( $open_files + $innodb_open_files ))