We ran into an interesting problem recently and wanted to share the research.
MySQL partitioning is a great way to improve performance in some circumstances, but it has a behavior that’s not widely noted. That is specifically with the MyISAM storage engine. MySQL seems to open two (2) file pointers for each table or partition referenced. These stay open permanently.
So say you have 100 plain MyISAM tables and 10 partitioned MyISAM tables with 100 partitions each. You will then need 2200 file pointers. Many systems are defaulted to 1024.
So the formula I developed for open_file_limit for my own use was as follows:
Plain myisam tables (t) = 100
MyISAM partions (p) = 1000
Buffer for table copies and incidentals (b) = 128
(( t + p ) * 2) + b = 2328 = open_file_limit
So the my.cnf setting is now:
open_file_limit = 2328
How does one find quantity of partitions and MyISAM tables?
mysql> SELECT count(*) FROM INFORMATION_SCHEMA.PARTITIONS; mysql> SELECT ENGINE, count(*) FROM INFORMATION_SCHEMA.TABLES GROUP BY ENGINE;