mysqld: errno: 24 - Too many open files

[postadelmaga]

After upgrading to 15.04 mysql cannot set the file limit and so it doesn't work well.

/var/log/mysql/error.log shows the following:

[Warning] Buffered warning: Changed limits: max_open_files: 1024 (requested 5000)
[Warning] Buffered warning: Changed limits: table_cache: 431 (requested 2000)
[ERROR] /usr/sbin/mysqld: Can't open file: ... (errno: 24 - Too many open files)

The issue looks to be related to the recent switch to systemd indeed if I start using upstart everything work fine.

I found out similar issues reported on Debian and the general suggestion is to add following line to the `mysqld.service` anyway this file is not present on my installation :

LimitNOFILE=infinity
LimitMEMLOCK=infinity

Other distro stores this file in `/usr/lib/systemd/system/mysqld.service`
I'm not sure how Ubuntu is managing this, maybe with file in dir /etc/default/ ?

I have also tried adding following lines in /etc/security/limits.conf, but the problem persist:

mysql soft nofile 65535
mysql hard nofile 65535

ProblemType: Bug
DistroRelease: Ubuntu 15.04
Package: mysql-server (not installed)
Uname: Linux 3.19.2-031902-generic x86_64
ApportVersion: 2.16.2-0ubuntu4
Architecture: amd64
CurrentDesktop: Unity
Date: Sat Mar 21 10:17:00 2015
InstallationDate: Installed on 2014-03-10 (375 days ago)
InstallationMedia: Ubuntu 14.04 LTS "Trusty Tahr" - Alpha amd64 (20140309)
SourcePackage: mysql-5.5
UpgradeStatus: Upgraded to vivid on 2015-03-20 (0 days ago)

[postadelmaga (postadelmaga)]

I think this should be a milestone for ubuntu 15.04

[Didier Roche (didrocks)]

Hey postadelmaga: the file is in /lib/systemd/system/mysqld.service in debian and ubuntu. Those services are shipped in the mysql package, so closing the systemd tag (adding the tag though).

I looked at mysql upstream, and their service files doesn't let the limit of files to opened up. Do you have a particular use case with a very large database? I really vary on the idea we disable the limit checks on mysql.

Also, did you look at other distros, did they change the limit as well as distro-patch (it doesn't seem for me)? Maybe you can try opening an upstream bug to engage the discussion?

[postadelmaga (postadelmaga)]

I'm a developer and I have 3/5 db on my machine, each one with many table ( 100+ ) however the main point is that using upstart I don't have issues, ideally the systemd behavior should be the same ...

On my machine, I'm on 15.04, /lib/systemd/system/mysqld.service is missing, which package provide it ?
- I have searched the file with apt-file and dpkg -l (mysql-server-5.6 and mysql-sever-core-5.6) but there is no track of it.

I have manually created that file with the following content, but I still have the issue:

[Service]
LimitNOFILE=infinity
LimitMEMLOCK=infinity

It will be great if you can share the content of your `mysqld.service` so I can add it manually.
Does I need to link this file some where ?

[postadelmaga (postadelmaga)]

I re-added systemd because It affect the systemd boot option so I think it is good to have it listed with other problems with systemd and vivid. ( maybe it is bad idea in that case remove it )

[Martin Pitt (pitti)]

It's already tagged systemd-boot, which is enough to make it appear on our radar (https://bugs.launchpad.net/ubuntu/+bugs?field.tag=systemd-boot)

[cooolman (coolman)]

the same problem, I can not change

in my.cnf
max_connections = 65535
open_files_limit = 65535

in /etc/sysctl.conf
fs.file-max = 2459688

in /etc/security/limits.conf

www-data soft nofile 65535
www-data hard nofile 65535
mysql soft nofile 65535
mysql hard nofile 65535
root soft nofile 65535
root hard nofile 65535

cat / proc / `pidof mysqld` / limits | egrep 'Limit | open files'
Limit Soft Limit Hard Limit Units
Max open files 1024 4096 files

max_connections 214

why so?

[cooolman (coolman)]

uname -a
Linux ubuntu 3.19.0-10-generic #10-Ubuntu SMP Mon Mar 23 16:16:45 UTC 2015 i686 i686 i686 GNU/Lin

 mysql -V
mysql Ver 14.14 Distrib 5.6.23, for debian-linux-gnu (i686) using EditLine wrapper

[postadelmaga (postadelmaga)]

@cooolman

do you have this file on your system `/lib/systemd/system/mysqld.service` ?

I'm not sure why but if you boot with systemd you are supposed to set the limits in service file first:

LimitNOFILE=infinity
LimitMEMLOCK=infinity

After that mysqld service will be allowed to open more connection ( as per your setting in my.cnf )

On my system `/lib/systemd/system/mysqld.service` is missing.

[cooolman (coolman)]

@postadelmaga (postadelmaga)

file is not present, that this file should be?
these are:
LimitNOFILE = infinity
LimitMEMLOCK = infinity
what else?

[cooolman (coolman)]

/lib/systemd/system/mysql.service

[Unit]
Description=MySQL Community Server
After=network.target
After=syslog.target

[Install]
WantedBy=multi-user.target
Alias=mysql.service

[Service]
User=mysql
Group=mysql
PermissionsStartOnly=true
ExecStartPre=/usr/share/mysql/mysql-systemd-start pre
ExecStart=/usr/bin/mysqld_safe
ExecStartPost=/usr/share/mysql/mysql-systemd-start post
TimeoutSec=600
Restart=on-failure

[cooolman (coolman)]

added below
LimitNOFILE = infinity
LimitMEMLOCK = infinity

and made the team
systemctl daemon-reload
  helped, thanks for the tip

[Robie Basak (racb)]

I'm reluctant to raise the default limit to infinity because this may introduce a security vulnerability due to select() macro overflow. See https://lists.ubuntu.com/archives/ubuntu-devel/2010-September/031446.html for details. If someone from upstream can confirm definitely that MySQL definitely does handle higher number file descriptors correctly though, then I have no objection.

I understand though that users do want to be able to tune the value. So, what else can we do? It seems to me that now that we're using systemd there are two different places where the setting can be made - from my.cnf, or from the systemd service definition - whereas previously users tended to do it in just my.cnf. Having it set in two places is confusing. my.cnf cannot raise beyond the systemd setting, but we cannot make the systemd setting "infinity" because of the security concern above.

So given that I don't want to change systemd's default to "infinity", I think it makes sense to expect users to change the systemd service definition (copy /lib/systemd/system/mysql.service to /etc/systemd/system/ and set LimitNOFILE etc there) if they want to change the default value, and to not set open_files_limit in my.cnf at all.

If this is reasonable, then this bug is Invalid. Is it reasonable? Comments appreciated.

[Akhil Mohan (akhil-mohan)]

Hi Robie,

The internal assessment here is that most appropriate solution would be
the one you have mentioned as the last option.

It would be easier if user owns the responsibility for creating and
managing the MySQL service file under /etc with following settings or
more as needed:
[Service]
LimitNOFILE=xxxxx
LimitMEMLOCK=xxxxx

This will help in avoiding any conflict between user settings and
package settings. We may add a comment to explain this to the users.

Please let me know if you need more information.

Regards,
Akhil

On Monday 30 March 2015 05:23 PM, Robie Basak wrote:
> I'm reluctant to raise the default limit to infinity because this may
> introduce a security vulnerability due to select() macro overflow. See
> https://lists.ubuntu.com/archives/ubuntu-
> devel/2010-September/031446.html for details. If someone from upstream
> can confirm definitely that MySQL definitely does handle higher number
> file descriptors correctly though, then I have no objection.
>
> I understand though that users do want to be able to tune the value. So,
> what else can we do? It seems to me that now that we're using systemd
> there are two different places where the setting can be made - from
> my.cnf, or from the systemd service definition - whereas previously
> users tended to do it in just my.cnf. Having it set in two places is
> confusing. my.cnf cannot raise beyond the systemd setting, but we cannot
> make the systemd setting "infinity" because of the security concern
> above.
>
> So given that I don't want to change systemd's default to "infinity", I
> think it makes sense to expect users to change the systemd service
> definition (copy /lib/systemd/system/mysql.service to
> /etc/systemd/system/ and set LimitNOFILE etc there) if they want to
> change the default value, and to not set open_files_limit in my.cnf at
> all.
>
> If this is reasonable, then this bug is Invalid. Is it reasonable?
> Comments appreciated.
>
> ** Changed in: mysql-5.6 (Ubuntu)
> Importance: Undecided => Medium
>
> ** Changed in: mysql-5.6 (Ubuntu)
> Status: New => Triaged
>

[Robie Basak (racb)]

Thanks Akhil. I'll mark this bug as Invalid then, although we should mention this in our release notes.

[David Favor (davidfavor)]

This is broken again.

net4-dev# sysctl fs.file-max
fs.file-max = 3280263

Changing /lib/systemd/system/mysql.service has no effect on file limits.

Setting...

[Service]
... ... ...
LimitNOFILE=infinity
LimitMEMLOCK=infinity

Then...

systemctl daemon-reload
service mysql restart

net4-dev# cat /proc/$(cat /var/run/mysqld/mysqld.pid)/limits | grep files
Max open files 1024 4096 files