MariaDB/MySQL – Table Open Cache & Table Definition Cache

To improve performance, each concurrent session in MariaDB/MySQL independently access the same table. In other words, many clients could be executing queries for a given table simultaneously. This is also because MySQL/MariaDB is multithreaded. This, however, uses additional memory, but normally increases performance.

table_open_cache indicates the maximum number of tables the server can keep open in any one table cache instance.

View the current setting of table_open_cache by running the command below, or by checking the value in my.cnf

select @@table_open_cache;

There is a variable called “Open_tables”, which indicates the number of table-opening operations since the server started. This is the variable that you can check to determine whether your table cache is too small.

SHOW GLOBAL STATUS LIKE 'Opened_tables';

If the value is very large or increases rapidly, even when you have not issued many FLUSH TABLES statements, increase the table_open_cache value at server startup.

FLUSH TABLES is to force all tables to be closed to ensure that if someone adds a new table outside of MySQL, all threads will start using the new table.

Table Definition Cache

This parameter shows the number of table definitions (SHOW CREATE TABLE \G) that can be stored. This is to speed up opening of tables and only one entry per table. You should consider increasing this parameter if you have large number of tables (>400) in your DB instance.

Values between 400 and 2000 are good for table_definition_cache. Do take note that a bigger table definition cache also means that your database uses more RAM.

To check table definition cache value, run the command below.

select @@table_definition_cache;

Add this parameter in your configuration file as follows:

[mysqld]
table_definition_cache = 16384

Cheers!