MySQL/MariaDB – ulimit open files & MySQL open_files_limit

The amount of resources that can be used can be controlled from the operating system perspective. Each user has limits that are set, but for that particular user, the limits are applied individually to each of its processes.

Limits can either be hard or soft. Only the root user can set the Hardlimits. Other users can set Soft limits, but it cannot be more than the hard limit.

The default value of ulimit open files limit is 1024. This is very low for a typical web server environment that hosts many have database-driven sites.

MySQL/MariaDB also uses this setting. The open_files_limit is set by MySQL/MariaDB to the system’s ulimit. Default is 1024.

NOTE: MySQL/MariaDB can only set its open_files_limit lower than what is specified under ulimit ‘open files’. It cannot be set higher than that.

Examine Current Limits

To inspect current limits

ulimit -a

# -a will show all current limits including hard, soft, open files, etc.

To inspect the current hard and soft limits.

# Hard Limits
ulimit -Ha

# Soft Limits
ulimit -Sa

# H for hard limits, or S for soft limits.

To check current open file limits.

ulimit -n

# –n for number of open files limits

Set ‘open files’ Limit Persistently

Open  /etc/security/limits.conf using the text editor of your choice, and add the following lines, then save it.

* soft nofile 102400
* hard nofile 102400
* soft nproc 10240
* hard nproc 10240

Edit the file /etc/security/limits.d/90-nproc.conf using the text editor of your choice, and add the following lines, then save it.

* soft nofile 1024000
* hard nofile 1024000
* soft nproc 10240
* hard nproc 10240
root soft nproc unlimited

Set open_files_limit in my.cnf (MySQL)

Open and edit /etc/my.cnf

Insert the lines below under [mysql], then save it.

[mysqld]
open_files_limit = 102400

Run the command below to see if there are any .conf files being used by MySQL that overrides the values for open limits.

systemctl status mysqld

Below is something you will see after running the command above.

/etc/systemd/system/mariadb.service.d
└─limits.conf

This means that there is a  /etc/systemd/system/mariadb.service.d/limts.conf  file, whicv is loaded with MySQL. Now, edit that file as well.

[Service]
LimitNOFILE=102400

Execute the command below for the changes to take effect.

systemctl daemon-reload && /scripts/restartsrv_mysql

Perform Server Reboot

Run the command below in MySQL to see the value of open_files_limit.

SHOW VARIABLES LIKE 'open_files_limit';

Output:

+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| open_files_limit | 102400 |
+------------------+--------+
1 row in set (0.00 sec)

Cheers!