< All posts | Fediverse | RSS | GitHub | Talks

Apr 17 2014

Dealing with MySQL resource shortage

You may in life while working with mysql get the following errors:

ERROR 1016 (HY000) at line 1: Can't open file: './blah/table.frm' (errno: 24)


SQL Error (23): Out of resources when opening file

This happens normally because by default the mysql user on most distro’s has a really short limit of 1024 open files, mysql can freak out if you have a large amount of databases or tables.

I triggered this by activating innodb_file_per_table on my setup and having lots of InnoDB tables.

To increase this you need to add the following to /etc/security/limits.conf

mysql soft nofile 10000
mysql hard nofile 20000

and then add the following to the my.inf mysql config (Normally found in /etc/mysql/my.inf)

open_files_limit = 10000

Reboot the mysql server and it should be fixed!

If this is not fixed then please check if the server itself has not ran out of space.