MariaDB Change Open Files Limit

When using systemctl, you can set value for open_files_limit in my.cnf file.

Default installation of MariaDB 10 have open_files_limit set to 16384.

MariaDB open_files_limit

To increase value for open_files_limit, create file

mkdir /etc/systemd/system/mariadb.service.d/
vi /etc/systemd/system/mariadb.service.d/limitnofile.conf

1 2	mkdir /etc/systemd/system/mariadb.service.d/ vi /etc/systemd/system/mariadb.service.d/limitnofile.conf

Add

[Service]
LimitNOFILE=1048576

1 2	[Service] LimitNOFILE=1048576

Reload systemctl

systemctl daemon-reload

1	systemctl daemon-reload

Restart mariadb

systemctl restart mariadb

1	systemctl restart mariadb

After doing this, it get changed to 32184 instead of 1048576 we specified in limitnofile.conf

MariaDB open_files_limit

To fix this edit

On Cpanel/RHEL

vi /etc/my.cnf.d/server.cnf

1	vi /etc/my.cnf.d/server.cnf

On Ubuntu/Debian

vi /etc/mysql/mariadb.conf.d/50-server.cnf

1	vi /etc/mysql/mariadb.conf.d/50-server.cnf

Under [mysqld], add

open_files_limit = 102400

1	open_files_limit = 102400

Now after restarting MariaDB, i get 1048576 for open_files_limit.

This value depends on kernals fs.nr_open parameter. If the value is low, you can increase by editing

vi /etc/sysctl.conf

1	vi /etc/sysctl.conf

Add

fs.nr_open=1048576‬

1	fs.nr_open=1048576‬

Then make it active with command

sysctl -p

sysctl -p

To see current value, you can run

sysctl -a | grep fs.nr_open

1	sysctl -a \| grep fs.nr_open

To set the value for the current session, run

sysctl -w fs.nr_open=1048576‬

1	sysctl -w fs.nr_open=1048576‬