Managing Thousands of Databases and Tables in MySQL - Addressing Open Files Limit Issues

When deploying a Percona Cluster with three nodes, importing hundreds of databases, each containing approximately 110 tables in our case, resulted in thousands of tables being managed by MySQL. In such environments, you may encounter issues related to the open files limit, which can impact the ability to restart cluster nodes and synchronize with xtrabackup (we were given Broken pipe error in our case).

Understanding the Problem

MySQL requires a file descriptor for each open table, database, and internal temporary file. With thousands of tables, the default open files limit can quickly be exhausted. When this occurs, you may see errors during node restarts or while running xtrabackup, as the system cannot open additional files required for these operations.

Symptoms of reaching the open files limit include:

• MySQL errors indicating too many open files.
• Inability to restart a cluster node.
• xtrabackup failing to complete synchronization.

Checking the Open Files Limit

You can check the current open files limit for MySQL by executing the following SQL statement:
SHOW VARIABLES LIKE 'open_files_limit';

This will return the current value MySQL is using. You can also check the system-level limits by running:
ulimit -n

from the shell under which the MySQL process is running.

Increasing the Open Files Limit

To resolve these issues, you will need to increase the open files limit for MySQL. In our case we had to increase it from 30K to 1M. This can be accomplished in several ways, depending on your operating system and how MySQL is started.

1. Update my.cnf

Add or modify the following line in your my.cnf (usually under the [mysqld] section):

open_files_limit = 65535

Set the value according to your environment’s needs. 65535 is a common high value but may need adjustment based on your hardware and workload.

2. Systemd Service File (if applicable)

If MySQL is started using systemd, you may need to set the limit in the systemd service configuration:

• Create or edit the file /etc/systemd/system/mysql.service.d/override.conf (or the equivalent for your installation) and add:

  [Service]
  LimitNOFILE=65535

• Reload systemd and restart MySQL:

  sudo systemctl daemon-reload
sudo systemctl restart mysql

3. OS-Level Limits

Ensure that the operating system’s limits are also set appropriately, especially for the user running the MySQL process. You can edit /etc/security/limits.conf and add:

mysql soft nofile 65535
mysql hard nofile 65535

After making these changes, restart the MySQL service to apply the new limits. Now, confirm the new open files limit:

SHOW VARIABLES LIKE 'open_files_limit';

You should see the increased value.