MySQL Bugs: #78102: open_files_limit gets stuck at 1024

Bug #78102	open_files_limit gets stuck at 1024
Submitted:	17 Aug 2015 9:20	Modified:	18 Aug 2015 10:13
Reporter:	Stéphane Lambert	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.6.25-0ubuntu0.15.04.1	OS:	Ubuntu (15.04)
Assigned to:		CPU Architecture:	Any
Tags:	open_files_limit

Description:
open_files_limit gets stuck at 1024

Whatever I try, the value stays the same. 

The server regularly crashes with the message : "Error in accept: Too many open files"

How to repeat:
1/ in /etc/security/limits.conf

nofile 3200

soft nofile 32000
hard nofile 32000
soft nproc 10240
hard nproc 10240

mysql hard nofile 32000
mysql soft nofile 32000

then systemctl daemon-reload + service mysql restart => the open_files_limit value stays the same

2/in /etc/init/mysql.conf at the beginning of the file : 

limit nofile 32000 32000
limit nproc 32000 32000

Then, service mysql restart => the value stays the same

3/ in /etc/mysql/mysql.conf.d/mysqld.cnf  , at the end of the file : 

open_files_limit = 32000

Then, service mysql restart => the value stays the same

--------------------------------

The check the value, I use : 

show variables like 'open_files_limit' 

Suggested fix:

=> make the engine READ the value defined in the configuration files

Hi,

Ubuntu 15.04 is using systemd, here mysql is a systemd service.

Systemd does resource control by its own config, not reading 

/etc/security/limits.conf

Please read: 

 https://dev.mysql.com/doc/refman/5.7/en/server-management-using-systemd.html

to increase LimitNOFILE when using systemd.

Note: even if urls/docs is about MySQL 5.7, it works for MySQL 5.6 too.

Please report if anything is unclear in documentation.

See prior comment. Thanks.

Thank you very much. It worked. 

But why isn't this documented in the official documentation ?
=> http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_open_files_limi...

The web is quite full of people having the same problem as me....

Thank you very much anyway, it helped :)

Thank you for the feedback.

The method described in the link did not work for me on Ubuntu 15.04.  What did work kwas overriding the /lib/systemd/system/mysql.service config by creating the file /etc/systemd/system/mysql.service with the contents:

.include /lib/systemd/system/mysql.service
[Service]
LimitNOFILE=500000

And restarting the daemon and mysql server.

I found this method here: https://books.google.ca/books?id=QJNqCQAAQBAJ&pg=PT283&lpg=PT283&dq=ubuntu++/lib/systemd/s....