MySQL Bugs: #77365: mysqld_safe sets ulimit too low

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

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 ))

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.