MySQL / MariaDB – Changing the open_files_limit

If you’ve seen errors such as either of these:

SQLSTATE[HY000]: General error:1205: Lock wait timeout exceeded; 
try restarting transaction

Or

[ERROR] /usr/sbin/mysqld: Can't open file: './database/table.frm' (errno: 24)

Error 24 basically means there are too many files open for the given process so you may be hitting the limit your database can open at once, the default is set to 1024. If you have a lot of tables you could easily hit this limit.

To see your current limit run this SQL statement:

SHOW VARIABLES LIKE 'open%';

You may see output like:

Normally you would be able to change that by running a command

SET open_files_limit=20000;

However this can’t be changed at runtime so you’ll need to edit your configuration file which you’ll probably find located at /etc/my.cnf or inside the directory /etc/my.cnf.d

Under the mysqld heading you can set the limit as below:

[mysqld]
open_files_limit = 20000

After saving your configuration file you’ll need restart the service

sudo /etc/init.d/mysql restart

(alternatively try service mysqld restart or service mariadb restart).

Hopefully now when you run SHOW VARIABLES LIKE ‘open%’; you’ll see the new limit. However if it’s still showing 1024 keep reading…

Changing the limit to allow the service to open more files.

Take a look in the following folder /usr/lib/systemd/system/ you should see a file named mysqld.service or mariadb.service. Rather than editing those files is good practice to create another folder (named mysqld.service.d or mariadb.service.d) with your changes in so they don’t get overwritten if there’s a software upgrade. Create a file inside that directory with an .conf extension and enter the following:

[service]
LimitNOFILE=infinity
LimitMEMLOCK=infinity

Save the file and run systemctl daemon-reload followed by restarting the service as above.