In this blog, we’ll look at a mystery around setting the open_file_limit variable in MySQL and Percona Server for MySQL.

MySQL Server needs file descriptors to run. It uses them to open new connections, store tables in the cache, create temporary tables to resolve complicated queries and access persistent ones. If mysqld is not able to open new files when needed, it can stop functioning correctly. A common symptom of this issue is error 24: “Too many open files.”

The number of file descriptors

mysqld

 can open simultaneously is defined by the configuration

open_files_limit

 option. You would expect it to work like any other MySQL Server option: set in the configuration file, …

MySQL variables open_files_limit, table_open_cache and max_connections are
inter-related, and this is for obvious reasons: all deal with file descriptors
one way or another.

If one of the value is provided but others are left out, mysqld calculates
others using a formula and in some cases, emits a warning if not possible.

The whole calculation behind obtaining the final file descriptor limit is a bit
byzantine and is as follows (for Linux):

EDIT: This applies to MySQL 5.5, in 5.6, as Daniël in comments pointed out,
few things have changed, check comment for details. I will probably make a
followup post on the differences.

1. First it tries to calculate a minimum requirement based on max_connections …

Recently I was discussing with some colleagues the possibility of consolidating some MySQL servers. While the servers are not heavily loading (averaging less than 1,000 queries a second) they are pretty large in terms of storage requirements. Each server has roughly 200 databases on each with approximatley 50 tables. Thats 10,000 tables per server.  Each server contains up to 1 terabyte of data so if you consolidated servers at a 10:1 ratio you would have 10 terabytes of data, 2,000 databases and 100,000 tables with 10,000 queries per second average load.

Alright, that's a lot. And without testing I don't know if it would work. It probably wouldn't.  But it might. And if it did, it would save the company a significant amount of money.  But, while discussing this,  someone brought up that open files limit might be a problem. Open files limit is the maximum number of files the …

If you use innodb_file_per_table = 1 and innodb_open_files = X (whatever amount is suitable for your server) there’s no way internal to MySQL for finding out how many IBD files InnoDB actually has open. Neither SHOW GLOBAL STATUS LIKE ‘innodb%’ nor SHOW ENGINE INNODB STATUS provide this information.

Many sites do have a growing number of tables, so you’ll want to know when it’s time to up the number (and potentially also open-files-limit). Solution: sudo lsof | grep -c “\.ibd$”

Last year, Erik Ljungstrom (sensei in the #ourdelta IRC channel on Freenode) created patch for a bug Arjen identified with the handling of (among others) the open-files-limit option; the patch was first included in the OurDelta build of MySQL 5.0.67.

Now, Sun engineer Guilhem Bichot has committed the (modified) patch into the 6.0-maria tree, so it should appear in 6.0 and potentially 5.4. That’s good.

See http://bugs.mysql.com/40368 for details and history.