MariaDB table_open_cache Explained: Table Cache, Performance, and Best Practices

Introduction

The table_open_cache system variable specifies the maximum number of open table definitions that MariaDB keeps in memory.

Every time MariaDB accesses a table, it must open the table before executing queries. Frequently opening and closing tables creates additional overhead, especially on busy servers with many databases or tables.

By keeping commonly used tables open in memory, MariaDB reduces disk operations, improves query efficiency, and lowers CPU usage.

This guide explains how table_open_cache works, how to configure it, recommended values, and common tuning mistakes.

Test Environment

Operating System : AlmaLinux 10
PHP Version      : PHP 8.3
Web Server       : Nginx
PHP-FPM          : Enabled
Architecture     : x86_64
Last Tested      : July 2026

What Is table_open_cache?

The table_open_cache variable determines how many open table instances MariaDB can keep in memory.

View the current value:

SHOW VARIABLES LIKE 'table_open_cache';

Example output:

+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| table_open_cache | 2000  |
+------------------+-------+

When the cache becomes full, MariaDB closes less frequently used tables to make room for newly opened ones.

How It Works

Without table caching:

Query
  │
  ▼
Open Table
  │
  ▼
Read Data
  │
  ▼
Close Table

With table caching:

Query
  │
  ▼
Table Already Cached
  │
  ▼
Read Data

Keeping frequently accessed tables open avoids repeated open and close operations.

Check the Current Configuration

View the current setting:

SHOW VARIABLES LIKE 'table_open_cache';

Monitor cache activity:

SHOW GLOBAL STATUS

LIKE 'Open%tables';

Typical output:

Open_tables

Opened_tables

Open_table_definitions

These values help determine whether the cache is appropriately sized.

Understanding the Statistics

The following status variables are useful:

| Variable                 | Description                       |
| ------------------------ | --------------------------------- |
| `Open_tables`            | Tables currently open             |
| `Opened_tables`          | Total tables opened since startup |
| `Open_table_definitions` | Cached table definitions          |

If Opened_tables increases rapidly during normal operation, the cache may be too small.

Change table_open_cache

Edit the configuration file.

sudo nano /etc/my.cnf

Example:

[mysqld]

table_open_cache = 4000

Save the file.

Restart MariaDB

Apply the changes.

sudo systemctl restart mariadb

Verify:

sudo systemctl status mariadb

Expected output:

Active: active (running)

Verify the New Value

SHOW VARIABLES LIKE 'table_open_cache';

Recommended Values

| Server Type         | Suggested Value |
| ------------------- | --------------- |
| Development         |             512 |
| Personal Website    |            1000 |
| WordPress           |            2000 |
| Medium Production   |            4000 |
| High-Traffic Server |      8000–16000 |

The appropriate value depends on the number of databases, tables, and concurrent queries.

Relationship with open_files_limit

Every open table may require one or more file descriptors.

For this reason, table_open_cache is closely related to:

open_files_limit

If the operating system limits the number of available file descriptors, increasing table_open_cache alone may have little effect.

Common Issues

Opened_tables Increases Continuously

Check:

SHOW GLOBAL STATUS

LIKE 'Opened_tables';

A rapidly increasing value often indicates that MariaDB repeatedly opens and closes tables because the cache is too small.

Setting table_open_cache Too High

Example:

table_open_cache = 50000

An excessively large cache consumes additional memory and file descriptors without necessarily improving performance.

Increase the value gradually while monitoring server behavior.

Ignoring File Descriptor Limits

If open_files_limit is too low, MariaDB may not be able to keep as many tables open as configured.

Always ensure the operating system and MariaDB are configured consistently.

Best Practices

Monitor Opened_tables regularly.

Increase the cache gradually rather than making large changes.

Configure open_files_limit appropriately.

Consider the number of databases and tables hosted on the server.

Monitor memory usage after increasing the cache.

Related Articles

MariaDB Explained

MariaDB max_connections Explained

MariaDB thread_cache_size Explained

MariaDB innodb_buffer_pool_size Explained

MariaDB open_files_limit Explained

Conclusion

The table_open_cache variable determines how many table instances MariaDB keeps open in memory. A properly sized cache reduces the overhead of repeatedly opening and closing tables, improving efficiency for busy database servers.

Rather than simply choosing the largest possible value, monitor table cache statistics such as Opened_tables and adjust the configuration based on your workload. Combined with appropriate file descriptor limits and memory management, table_open_cache helps deliver consistent database performance.