How to Optimize MariaDB table_open_cache & open_files_limit on cPanel Servers in Pakistan

On multi-tenant cPanel & WHM hosting servers in Pakistan hosting hundreds of active WordPress, WooCommerce, and custom Laravel databases, MariaDB performance frequently degrades under peak traffic. Sysadmins observe high I/O wait, CPU spikes on mysqld, and sluggish page load times, yet the InnoDB buffer pool seems adequately sized.

When inspecting database telemetry, the culprit is almost always table cache exhaustion.

Every concurrent query executed by a client connection requires MariaDB to open file handles for the table definition (.frm) and the InnoDB tablespace (.ibd). If table_open_cache is undersized relative to active connections, MariaDB is forced to evict older table descriptors to open new ones. This triggers table cache thrashing, causing the global status counter Opened_tables to skyrocket and generating thousands of redundant filesystem open() and close() system calls per second.

Furthermore, increasing table_open_cache without adjusting Linux file descriptor limits results in Error 24: Too many open files crashes.

In this deep-dive guide, we configure optimal table caching, eliminate systemd file descriptor constraints, and stabilize MariaDB on enterprise bare-metal Dedicated Servers and Dedicated Servers in Pakistan.

---

1. How MariaDB Manages Table Caches

MariaDB employs two distinct in-memory table descriptor caches:

+--------------------------------------------------------------+
|                 Client Connection Threads                    |
|   Thread 1: SELECT wp_posts    Thread 2: SELECT wp_posts     |
+------------------------------+-------------------------------+
                               |
                               v
+--------------------------------------------------------------+
|         table_open_cache (Per-Thread Table Instances)        |
|  - Holds open file handles for actively queried tables       |
|  - If 5 threads query wp_posts, 5 table instances are cached |
+------------------------------+-------------------------------+
                               |
                               v
+--------------------------------------------------------------+
|       table_definition_cache (Global Table Metadata)         |
|  - Holds parsed schema metadata (.frm structure)             |
|  - Shared globally across all threads (1 entry per table)    |
+--------------------------------------------------------------+

1. table_definition_cache: Stores the parsed schema definition of each table. Because this structure is thread-shared, sizing it to match the total number of physical tables on your server ensures schema parsing occurs only once at startup.
2. table_open_cache: Stores file descriptors for active table instances. If 10 concurrent threads query wp_posts simultaneously, MariaDB requires 10 distinct cached table handles in table_open_cache.

---

2. Diagnosing Table Cache Thrashing via SQL

Connect to your database via MySQL CLI as root and query the cache efficiency ratio:

SHOW GLOBAL STATUS LIKE 'Open%tables';
SHOW GLOBAL STATUS LIKE 'Uptime';
SHOW GLOBAL VARIABLES LIKE 'table_open_cache';

Example output from an un-tuned server:

+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| Open_tables   | 4000   |
| Opened_tables | 894210 |
+---------------+--------+

Calculating Cache Hit Rate

Compute table cache misses per second:

$$\text{Miss Rate} = \frac{\text{Opened_tables}}{\text{Uptime}}$$

• If Opened_tables / Uptime is greater than 10 tables per second, or if Open_tables is pinned against your configured table_open_cache limit while Opened_tables continues to climb rapidly, your server is suffering from severe cache eviction churn.

---

3. Resolving the File Descriptor Chain (Systemd & Kernel)

Before increasing table_open_cache, you must ensure the operating system permits MariaDB to open sufficient file descriptors.

Linux Kernel fs.file-max (System-wide)
       |
       v
Systemd LimitNOFILE (mariadb.service)
       |
       v
MariaDB open_files_limit (/etc/my.cnf)
       |
       v
table_open_cache * 2 + max_connections * 5

Step 1: Systemd LimitNOFILE Drop-In

On modern systemd systems (AlmaLinux, CloudLinux, Ubuntu), MariaDB’s file limits are governed by systemd service limits, not /etc/security/limits.conf.

Create a systemd override directory:

mkdir -p /etc/systemd/system/mariadb.service.d/
nano /etc/systemd/system/mariadb.service.d/limits.conf

Add the following override:

[Service]
LimitNOFILE=1048576

Reload systemd daemon:

systemctl daemon-reload

---

4. Production /etc/my.cnf Configuration

Now edit your global MySQL configuration /etc/my.cnf:

nano /etc/my.cnf

Sizing Formula for High-Density cPanel Hosting:

• max_connections: 300 to 500.
• table_open_cache: $4000\text{ to }16384$ (depending on RAM).
• table_definition_cache: Equal to total tables on server (typically $4000\text{ to }8000$).
• open_files_limit: Must be at least (table_open_cache * 2) + (max_connections * 5).

Add or update the [mysqld] section:

[mysqld]
# Connection Limits
max_connections = 400
max_user_connections = 40

# File Descriptors
open_files_limit = 65536

# Table Cache Sizing
table_open_cache = 8192
table_open_cache_instances = 16
table_definition_cache = 6000

# Thread Pool / Caching
thread_cache_size = 64

[!TIP] Setting table_open_cache_instances = 16 partitions the table cache into multiple independent mutex pools, eliminating CPU lock contention on multi-core AMD EPYC and Intel Xeon processors.

Restart MariaDB cleanly:

/scripts/restartsrv_mariadb

---

5. Verifying the Optimized Runtime Variables

Verify that MariaDB recognized the elevated OS file limits:

SHOW GLOBAL VARIABLES LIKE 'open_files_limit';
SHOW GLOBAL VARIABLES LIKE 'table_open_cache%';

You should observe:

• open_files_limit: 65536 (or higher).
• table_open_cache: 8192.
• table_open_cache_instances: 16.

Monitor the miss rate again after 24 hours. Opened_tables will remain virtually static, freeing thousands of IOPS for database writes and query execution.

For complementary cPanel infrastructure optimizations, explore our guides on cPanel Exim Smarthost Relay and cPanel ChkServd Auto-Healing.

---