Bug #77365 mysqld_safe sets ulimit too low
Submitted: 15 Jun 2015 21:18 Modified: 27 Jan 2020 13:48
Reporter: Ike Walker Email Updates:
Status: Not a Bug 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 ))
[27 Jan 2020 13:48] MySQL Verification Team
Hi Mr. Walker,

Thank you for your bug report.

However, this is not a bug. First of all, InnoDB is a part of our server, so it will use a part of the  handles that are allocated to the server.

Second, if you get that message, you have to increase your limit for the handles. This is fully described in our Reference Manual.