Increase open-files-limit in MariaDB on CentOS 7 with systemd

In the new age of linux known as systemd, a word that many still prepend colorful words to, I stumbled across another gotcha. Changing MariaDb’s open files limit.

Typically you would configure this in /etc/my.cnf or in /etc/security/limits.conf, but this no longer appears to be the case as systemd wants to control it.

I ran across having to do this cause I was receiving the following errors in the database when running mysqldump, mysqlcheck, etc:

mysqldump: Error: 'Out of resources when opening file '/tmp/#sql_7f96_2.MAI' (Errcode: 24)' when trying to dump tablespaces

mysqldump: Couldn't execute 'SHOW TRIGGERS LIKE 'EXAMPLE'': Can't read dir of './DB_NAME/' (errno: 24) (1018)

mysqldump: Got error: 23: "Out of resources when opening file './DB_NAME/TABLE.MYD' (Errcode: 24)" when using LOCK TABLES

So to increase the open files limit which is the accepted fix for this, you need to do the following with OS’s that use systemd:

First, check to see what your current open files is set to in MariaDB:

MariaDB [(none)]> SHOW VARIABLES LIKE 'Open_files_limit';
| Variable_name    | Value |
| open_files_limit | 1024  |
1 row in set (0.00 sec)

Create a directory to store the MariaDB service changes for systemd:

mkdir -p /etc/systemd/system/mariadb.service.d/
cat /etc/systemd/system/mariadb.service.d/limits.conf

Now reload the systemd daemon so it is aware of the changes applied to systemd. This does not restart any service:

systemctl daemon-reload

Finally, restart the MariaDB service so it will apply the new setting:

systemctl stop mariadb
systemctl start mariadb

You can now verify that you setting went into place by:

MariaDB [(none)]> SHOW VARIABLES LIKE 'Open_files_limit';
| Variable_name    | Value |
| open_files_limit | 20000 |
1 row in set (0.00 sec)

More details and the reason behind this is found here:

cat /usr/lib/systemd/system/mariadb.service
# 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

# 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