MySQL: partition watchout

When designing partitions keep in mind that it will be required to have the number of partitions and open file limit system variable in harmony.

Thus each partition creates an overhead of extra two files (#p.myd and #p.myi). I.e. partition by hash of month will produce 2 * 12 months = 24 extra files. Not too bad for the default MySQL settings. However if there is plan for much more partitions, the open file limit has to be tuned respectively. Otherwise the error “mysql out of resources when opening file errcode 24” may occur.

To change the number of file descriptors available to MySQL, you can set open-files=2048 (or to any other reasonable number) in the MySQL configuration file.

No Comment

No comments yet

Leave a reply