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 [Service] LimitNOFILE=20000
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 # 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