A Guide to Max Open Files for MySQL

Requires ClusterControl 1.2.11 or later. Applies to MySQL single instances, replications and Galera clusters.

You have created a large database with thousands of tables (> 5000 in MySQL 5.6). Then you want to create a backup using xtrabackup. Or, if it is a Galera cluster, you have to recover a galera node using wsrep_sst_method=xtrabackup[-v2].

Unfortunately it fails and the following is emitted in the Job Logs messages:

xtrabackup: Generating a list of tablespaces
2015-11-03 19:36:02 7fdef130a780  InnoDB: Operating system error number 24 in a file operation.
InnoDB: Error number 24 means 'Too many open files'.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/operating-system-error-codes.html
InnoDB: Error: could not open single-table tablespace file ./DB75/t69.ibd

In this case you can simply increase the open_files_limit of the MySQL server(s) by going to Manage > Configurations, click on Change Parameter.

We want to make the change on all MySQL hosts, and add the open_files_limit to the ‘MYSQLD’ group. We then have to type in ‘open_files_limit’ in the “Parameter” field, since the parameter has not yet been set in the my.cnf file of the selected servers. Then it is simply to set the New Value to something appropriate. We have > 80000 tables in the database, so we’ll set the new value to 100000.

Next, press Proceed, and then you will be presented with the Config Change Log dialog:

The parameter change was successful, and the next step is to restart the MySQL servers as indicated in the dialogs. Please note that many operating systems impose an upper limit on how many open files can be set on a process.

PS.: To get started with ClusterControl, click here!