mySQL 1024 open file limit

There should be an easy answer but this one had got me stumped. The mariadb.log files indicated mySQL is crashing because of a file limit. Nothing I have done has been able to change that limit. It looks like this: (errno: 24) mysqld: Can't open file I believe it is the 1024 file open limit that is the issue. Or so that is where I am right now on this.

Editing the /etc/security/limits.conf and/or the /etc/my.cnf does nothing. I have tried it all, except apparently the one thing that would work, of which I have no clue what it is. And if on a default install the 1024 limit is set, how come everyone else in the world is not breaking? It can't just be my mySQL servers that break with this file limit right? What am I missing? Am I really on the right track? As far as I can tell the error 24 always means your have a file open limit and it is not large enough.

Status: 
Active

Comments

Howdy -- it sounds like you've tried some of this already, but just a few things to check --

In your /etc/my.cnf, make sure that in the "mysqld" section, that you have a line like this:

open_files_limit = 100000

Next, you may end up needing to edit this file:

/usr/lib/systemd/system/mariadb.service

There, look for the "Service" section, and add this to the end of it:

LimitNOFILE=infinity

Once you do that, run these two commands:

systemctl daemon-reload
/sbin/service mariadb restart

Does that help with your problem?

That worked! Thank you so much. open_files_limit 65536

The difference is in that mariadb.service file I attempted to create another file to load and did not use the "Service" section in that file. Which I tried just now and then looked at the MySQL System Variables and there was the 65536 limit. Perhaps that number comes from my own earlier efforts in some config file - I have lost all track now.

Your are my hero. Now... if this is such a deal breaker on a new install, why in the hell is it there to begin with? The entire webroot is 6GB on this server and hosts about 60 websites. The server has 16GB of DDR4 so it could put in memory all the websites and all the mySQL databases. I am just wondering how many unsuspecting admins have to figure this problem out? In what decade will someone that is not an expert at CentOS be able to build a server that works and does not break so easy? OK, enough whining.

I'll be honest, this is a bit of a puzzlement to me too.

Of all the folks we have using CentOS 7, you are only the second one to come to us about that issue since CentOS 7 was released in June 2014.

And the other fellow seeing that issue certainly doesn't have more domains than the average user.

So why is that happening to you guys? I'm not quite sure!

However, you're certainly not alone, as I do see some other folks with that issue as I Google about it (which is how I stumbled upon a solution).

So we'll certainly look deeper into that, but I'm a bit confused too!

However, I'm glad to hear that's working for you, thanks for letting us know!

calderwood's picture
Submitted by calderwood on Mon, 09/16/2019 - 10:38

I had the same issue on Virtualmin GPL CentOS Linux 7.6 and was following your suggested fix above, when I saw this written in the mariadb.service file that you recommend updating. The message says it will be overwritten (see full message below).

I fixed the issue with the following from fedoraproject and I can confirm it has fixed the issue with the file limit and errno: 24 errors. The ulimit -a still shows 1024, but that is a conflict between ulimit and Systemd not showing correctly.

Running # cat /proc/$(pgrep mysqld_safe$)/limits | grep "Max open" I get: Max open files            100000               100000               files

# cat /proc/`pidof mysqld`/limits
Limit                     Soft Limit           Hard Limit           Units    
Max cpu time              unlimited            unlimited            seconds  
Max file size             unlimited            unlimited            bytes    
Max data size             unlimited            unlimited            bytes    
Max stack size            8388608              unlimited            bytes    
Max core file size        0                    unlimited            bytes    
Max resident set          unlimited            unlimited            bytes    
Max processes             23137                23137                processes
Max open files            100000               100000               files    
Max locked memory         65536                65536                bytes    
Max address space         unlimited            unlimited            bytes    
Max file locks            unlimited            unlimited            locks    
Max pending signals       23137                23137                signals  
Max msgqueue size         819200               819200               bytes    
Max nice priority         0                    0                   
Max realtime priority     0                    0                   
Max realtime timeout      unlimited            unlimited            us

Notes that are inside the /usr/lib/systemd/system/mariadb.service file:

It's not recommended to modify this file in-place, because it will be
# overwritten during package upgrades.  If you want to customize, the
# best way is to create a file "/etc/systemd/system/mariadb.service",
# containing
# .include /lib/systemd/system/mariadb.service
# ...make your changes here...
# or create a file "/etc/systemd/system/mariadb.service.d/foo.conf",
# which doesn't need to include ".include" call and which will be parsed
# after the file mariadb.service itself is parsed.
#
# For more info about custom unit files, see systemd.unit(5) or
# http://fedoraproject.org/wiki/Systemd#How_do_I_customize_a_unit_file.2F_add_a_custom_unit_file.3F

# For example, if you want to increase mariadb's open-files-limit to 10000,
# you need to increase systemd's LimitNOFILE setting, so create a file named
# "/etc/systemd/system/mariadb.service.d/limits.conf" containing:
# [Service]
# LimitNOFILE=10000

# Note: /usr/lib/... is recommended in the .include line though /lib/...
# still works.
# Don't forget to reload systemd daemon after you change unit configuration:
# root> systemctl --system daemon-reload

Example of modify an existing service. For example, this procedure modifies the httpd service.

Prerequisites
You are logged in as a user with administrator-level permissions.
You have a configured httpd server running through systemd.
Procedure
Create a directory for the service modification in the following format: [SERVICE NAME].service.d. For example, the directory for the httpd.service modification is httpd.service.d:
# mkdir /etc/systemd/system/httpd.service.d/
Create a configuration file within this directory:
# vi /etc/systemd/system/httpd.service.d/custom.conf
Add your custom configuration. For example:
[Service]
Restart=always
RestartSec=30
Save the file.
Restart the httpd service:
# systemctl restart httpd