Mysql with thousands of databases and tables - open files limit problem

We have deployed Percona Cluster with 3 nodes and started importing hundreds of databases to it.
Each database has 110 tables, so within few hours we had thousands of tables in the server.
 
Once we reached the limit of open files, it was not possible to restart the node and xtrabackup synchronization failed with strange error (Broken pipe). It was not clear on the first look what caused the problem on the restarted node.
Things cleared after we have checked the log innobackup.backup.log on the Donor node.
We have reached the limit of maximum opened files.
 
To resolve the problem we had to extend the limit (in our case we have set the limit to 1M from 30k).
 
How to check your limit:
  • in command line execute: ulimit -n
  • in mysql status variables find variable open_files_limit
 
To set the limit in the OS you can run command:
  • ulimit -n <number of files>
  • set the open_files_limit variable in the my.cnf and than restart your mysql service
  • To set the limit in the OS permanently, edit file /etc/security/limits.conf. After reboot will be started the machine with limits you have set in the limits.conf file
×