How MySQL Adjusts open_files_limit, max_connections, and table_open_cache When System Resources Are Insufficient

1. Cause

When a non‑root user runs MySQL with high configuration, the effective parameter values may differ from the configured ones; this article explains how MySQL adjusts three parameters when system resources are insufficient.

2. Explanation

The three parameters involved are open_files_limit, max_connections, and table_open_cache.

These parameters are related to the maximum number of open files ( ulimit -n), i.e., file descriptors ( fd).

Relationship: each MySQL connection needs a file descriptor; opening a table also consumes descriptors (e.g., MyISAM tables need two).

3. How MySQL Adjusts Parameters

Calculate request_open_files based on configured values (or defaults).

Obtain the effective system limit effective_open_files .

Adjust request_open_files according to effective_open_files .

Compute the actual parameter values using SHOW VARIABLES .

3.1 Calculate request_open_files

Three formulas are used:

# max connections + 2 * table_cache_size + 10
limit_1 = max_connections + table_cache_size * 2 + 10;
# Assume each connection opens 2‑4 tables; source uses:
# We are trying to allocate no less than max_connections*5 file handles
limit_2 = max_connections * 5;
# MySQL default minimum is 5000
limit_3 = open_files_limit ? open_files_limit : 5000;
# Final request_open_files is the maximum of the three limits
request_open_files = max(limit_1, limit_2, limit_3);

3.2 Calculate effective_open_files

MySQL tries to set effective_open_files as large as possible within the system limit.

3.3 Adjust request_open_files

requested_open_files = min(effective_open_files, request_open_files);

4. Recalculate Parameter Values

4.1 Adjust open_files_limit

open_files_limit = effective_open_files

4.2 Adjust max_connections

max_connections

is corrected based on request_open_files:

limit = requested_open_files - 10 - TABLE_OPEN_CACHE_MIN * 2;

If the configured max_connections exceeds limit, it is reduced to limit.

Otherwise, the configured value is kept.

4.3 Adjust table_open_cache (table_cache_size)

table_cache_size

is corrected based on request_open_files:

# Minimum value for MySQL table_cache_size is 400
limit1 = TABLE_OPEN_CACHE_MIN;
# Compute based on requested_open_files
limit2 = (requested_open_files - 10 - max_connections) / 2;
limit = max(limit1, limit2);

If the configured table_cache_size exceeds limit, it is reduced to limit.

Otherwise, the configured value is kept.

5. Example

All examples are run under a non‑root user.

- System resources insufficient and cannot be adjusted
# Parameter settings
mysql max_connections = 1000 // ulimit -n 1024
# Effective values
open_files_limit = 1024
max_connections = 1024 - 10 - 800 = 214
table_open_cache = (1024 - 10 - 214) / 2 = 400

---
- System resources insufficient but can be adjusted
# Parameter settings
mysql max_connections = 1000 // ulimit -S -n 1000 // ulimit -H -n 65535
# Effective values
open_files_limit = 65535
max_connections = 1000
table_open_cache = (1024 - 10 - 214) / 2 = 400

---
- Modify MySQL open_files_limit
# Parameter settings
// mysql max_connections = 1000
max_connections = 1000 // ulimit -n 65535
# Effective values
open_files_limit = 65535
max_connections = 1000
table_open_cache = 2000

6. Other

The Taobao Database Kernel Monthly Report discusses related content in “MySQL Q&A: open file limits”, which explains when MySQL performs file‑opening operations.

Reference: MySQL·答疑解惑·open file limits