Detailed explanation of table_open_cache parameter optimization and reasonable configuration under MySQL 5.6

1. Introduction

table_cache is a very important MySQL performance parameter, which is called table_open_cache in versions after 5.1.3. table_cache is mainly used to set the number of table caches. Because each client connection accesses at least one table, the value of this parameter is related to max_connections.
For example, for 1000 connections running in parallel, you should have a table cache of at least 1000 × N, where N is the maximum number of tables in a join of queries that the application can execute. In addition, some additional file descriptors need to be reserved for temporary tables and files.

2. Cache mechanism

When a connection accesses a table, MySQL checks the number of currently cached tables. If the table has been opened in the cache, the table in the cache will be accessed directly to speed up the query; if the table is not cached, the current table will be added to the cache and queried.

Before performing a cache operation, table_open_cache is used to limit the maximum number of cache tables: if the currently cached tables do not reach table_open_cache, new tables will be added; if this value has been reached, MySQL will release the previous cache based on the last query time, query rate and other rules of the cache table. Whenever MySQL accesses a table, if there is room in the table buffer, the table is opened and placed into it, which allows faster access to the table contents.

3. How to judge

By checking the status values ​​Open_tables and Opened_tables during peak times, you can decide whether you need to increase the value of table_open_cache.

If you find that open_tables is equal to table_open_cache, and opened_tables is growing, then you need to increase the value of table_open_cache (the above status values ​​can be obtained using SHOW GLOBAL STATUS LIKE 'Open%tables').

Note that you cannot blindly set table_open_cache to a large value. If it is set too high, you may run out of file descriptors, resulting in unstable performance or connection failures.

Open_tables / Opened_tables >= 0.85

Open_tables / table_cache <= 0.95

4. Suggestions

If you are not sure at the beginning, put the MySQL database in the production environment for a period of time, and then adjust the parameter value to be larger than the value of Opened_tables, and ensure that it is still slightly larger than Opened_tables under extreme conditions of relatively high load.
In the default installation of MySQL, the value of table_cache is 256 to 512 on machines with less than 2G memory. If the machine has 4G memory, the default value is 2048. However, this does not mean that the larger the machine memory, the larger this value should be. Because increasing table_cache makes MySQL respond to SQL faster, it will inevitably produce more dead locks, which will slow down the entire set of database operations and seriously affect performance. Therefore, during routine maintenance, you still need to make judgments based on the actual situation of the database and find the table_cache value that best suits the database you are maintaining.

mysql>flush tables; can clear open_tables

# service mysqld restart can clear opened_tables

The following are instructions for MySQL 5.6

table_open_cache specifies the size of the table cache. Whenever MySQL accesses a table, if there is room in the table buffer, the table is opened and placed into it, which allows faster access to the table contents.
By checking the status values ​​Open_tables and Opened_tables during peak times, you can decide whether you need to increase the value of table_open_cache.
If you find that open_tables is equal to table_open_cache, and opened_tables is growing, then you need to increase the value of table_open_cache (the above status values ​​can be obtained through SHOW GLOBAL STATUS LIKE 'Open%tables').
Note that you cannot blindly set table_open_cache to a large value. If the value is too large, it will exceed the shell's file descriptors (check with ulimit -n), resulting in insufficient file descriptors, unstable performance or connection failure.

Test environment: Tencent Cloud CDB, memory 4000M, check table_open_cache=512 in the console, monitor whether the table_open_cache setting is reasonable and whether it needs to be optimized.

show variables like '%table_open_cache%'; 

show global status like 'Open%tables';

It is found that open_tables is equal to table_open_cache, both are 512, indicating that MySQL is releasing the cached table to accommodate the new table. At this time, you may need to increase the value of table_open_cache. For a machine with 4G memory, it is recommended to set it to 2048

More suitable values:

Open_tables / Opened_tables >= 0.85
Open_tables / table_open_cache <= 0.95

If you are not sure about this parameter, here is a very conservative setting suggestion: put the MySQL database in the production environment for a period of trial operation, and then adjust the parameter value to be larger than the value of Opened_tables, and ensure that it is still slightly larger than Opened_tables under extreme conditions of relatively high load.