Error
Error Code:
1587
MariaDB Error 1587: Too Many Open Files
Description
Error 1587 indicates that the MariaDB server or the underlying operating system has reached its maximum allowed number of open files. This typically occurs during operations that require access to many files concurrently, such as purging binary logs, handling numerous table accesses, or processing large datasets.
Error Message
Too many files opened, please execute the command again
Known Causes
4 known causesOS File Descriptor Limit
The operating system has a global or per-process limit on the number of files that can be opened simultaneously, which MariaDB has exceeded.
MariaDB Configuration Limit
The `open_files_limit` setting in MariaDB's configuration file (my.cnf) is set too low for the server's current workload.
High Workload or Concurrency
A sudden surge in database connections, complex queries, or intensive operations like binary log purging can temporarily exhaust available file descriptors.
Other Process Contention
Other applications running on the same server might be consuming a significant number of file descriptors, leaving insufficient resources for MariaDB.
Solutions
4 solutions available1. Temporarily Increase Open File Limit easy
Quickly increase the open file limit for the current session or until the next reboot.
1
Identify the MySQL/MariaDB process ID (PID). You can usually find this by looking at the output of `ps aux | grep mariadb` or `ps aux | grep mysqld`.
ps aux | grep mariadb2
Use the `ulimit` command to temporarily increase the open file limit for the specific process. Replace `<PID>` with the actual process ID found in the previous step. A common value to try is 65536.
sudo prlimit --pid <PID> --nofile=65536:655363
After applying the `prlimit` command, try executing your MariaDB command again.
2. Permanently Increase Open File Limit System-Wide medium
Configure the operating system to permanently increase the open file limit for all processes, including MariaDB.
1
Edit the system's limits configuration file. This is typically `/etc/security/limits.conf` or a file within `/etc/security/limits.d/`.
sudo nano /etc/security/limits.conf2
Add or modify the following lines to set the soft and hard limits for open files for the `mysql` user (or the user MariaDB runs as). Replace `mysql` with the correct user if necessary.
* soft nofile 65536
* hard nofile 65536
mysql soft nofile 65536
mysql hard nofile 655363
If you are using systemd, you might also need to configure the limits for the MariaDB service itself. Create or edit a systemd override file for mariadb.service.
sudo systemctl edit mariadb.service4
In the editor that opens, add the following lines to set the `LimitNOFILE` directive. Adjust the values as needed.
[Service]
LimitNOFILE=655365
Save the changes and exit the editor.
6
Reload the systemd manager configuration and restart the MariaDB service for the changes to take effect.
sudo systemctl daemon-reload
sudo systemctl restart mariadb7
Verify the new limits by checking the process limits for MariaDB after it has restarted.
cat /proc/<PID>/limits | grep 'Max open files'3. Tune MariaDB's `open_files_limit` Variable medium
Adjust MariaDB's internal setting for the maximum number of file descriptors it can use.
1
Connect to your MariaDB server using a client like `mysql` or `mariadb`.
mariadb -u root -p2
Check the current value of `open_files_limit`.
SHOW VARIABLES LIKE 'open_files_limit';3
If the current value is low, you can increase it dynamically. Replace `65536` with your desired value. This change is temporary and will reset on restart.
SET GLOBAL open_files_limit = 65536;4
To make this change permanent, add or modify the `open_files_limit` setting in your MariaDB configuration file (e.g., `/etc/my.cnf`, `/etc/mysql/my.cnf`, or a file in `/etc/mysql/conf.d/`).
[mysqld]
open_files_limit = 655365
Restart the MariaDB service for the configuration file changes to take effect.
sudo systemctl restart mariadb4. Identify and Close Unnecessary MariaDB Connections easy
Reduce the number of open files by terminating idle or problematic client connections.
1
Connect to your MariaDB server.
mariadb -u root -p2
View all active client connections and their associated process IDs (PIDs). Look for connections that have been idle for a long time or are consuming resources unnecessarily.
SHOW PROCESSLIST;3
If you identify a connection that can be safely terminated, use the `KILL` command with the `Id` from the `SHOW PROCESSLIST` output. Replace `<CONNECTION_ID>` with the actual ID.
KILL <CONNECTION_ID>;4
After killing connections, check if the error persists. This is a good practice to free up resources if the issue is due to an excessive number of connections.