Too many open files mysql/mariadb

In order to make use of partitioning, mysql/mariadb requires to be able to have a lot of files open at the same time. There are three different mechanisms that can stop mysql/mariadb from opening files:

1. The operating system has a global max number of open files at the file system level.
2. The operating system imposes a per user (technically per process) limit on the number of open files.
3. mysql has a self-restriction on the number of open files it will even try to open.

Global max number of open files at the OS file-system level

Check it

$ cat /proc/sys/fs/file-max
70048

Change it

Change it by adding a row in /etc/sysctl.conf

fs.file-max=811219

Reload the new configuration:

# sysctl --system

The user limit imposed per process by the OS.

Check it

To check the current value of the relevant limit, become the `mysql` user. This is a bit tricky since `mysql` doesn't have a shell defined in /etc/passwd.

$ sudo su
# su -s /bin/bash mysql
su: Authentication failure
(Ignored)
$ whoami
mysql
$ ulimit -a
...
max locked memory       (kbytes, -l) 64
max memory size         (kbytes, -m) unlimited
open files                      (-n) 1024
pipe size            (512 bytes, -p) 8
...

Change it

The file to edit is /etc/security/limits.conf and the item to add is nofile.

# HE mod START
mysql           hard    nofile          300000
mysql           soft    nofile          300000
# HE mod STOP

These changes applies for new logins/sessions, so I suppose restarting the server is enough, but a reboot might be safer.

The self-restriction of mysql/mariadb

Check it

in mysql/mariadb

MariaDB [(none)]> show global variables like 'open_files_limit';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| open_files_limit | 300000 |
+------------------+--------+
1 row in set (0.01 sec)

Change it

Method 1

Add a row like this

open_files_limit = 300000

Under the section mysqld in a file under /etc/mysql/mariadb.conf.d/, e.g. /etc/mysql/mariadb.conf.d/50-server.cnf.

Method 2

Create a file /etc/systemd/system/mariadb.service.d/MY_SPECIAL.conf and set the key LimitNOFILE under the section Service to whatever you need.

[Service]
# LimitNOFILE=16364
LimitNOFILE=200000

comments powered by Disqus

Back to the index

Blog roll

R-bloggers, Debian Weekly

---

Last modified: augusti 14, 2020