// you’re reading...

MySQL

MySQL Partitioning and open_file_limit

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;

Discussion

One comment for “MySQL Partitioning and open_file_limit”

  1. Another quick addition to this post: you should check your OS open file limits as well. In linux, do a:
    > ulimit -a # shows open file limit
    To set this to accomodate:
    > ulimit -n 2500 # then restart mysqld

    Posted by randy melder | May 13, 2010, 9:29 am

Post a comment

Help support my site and buy a domain name at http://domainsemailhosting.com/