When creating a large number of partitions or tables, MySQL may mysteriously stop working and you can see the following errors on your /var/lib/mysql/hostname.err: file.
[ERROR] /usr/sbin/mysqld: Can’t open file: ‘./database/table.frm’ (errno: 24)
errno: 24 simply means that too many files are open for the given process.
There is a read-only mysql variable called “open_files_limit” that will show how many open files are allowed by the mysqld:
SHOW VARIABLES LIKE ‘open%’;
In most of the web hosting servers it may set to a low value like 1024. Unfortunately, it will not work.
By increasing the “open_files_limit” in the MySQL configuration file will fix this issue. Add the following parameter in /etc/my.cnf file and restart mysql service.
open_files_limit = 100000