Description of table_cache configuration parameters in mysql

This article mainly introduces the table_cache configuration parameters of MySQLPerformance Optimization. This article introduces its cache mechanism, parameter optimization and clearing For cached commands, friends in need can refer to

table_cache is a very important MySQL performance parameter. It is called table_open_cache in versions after 5.1.3. table_cache is mainly used to set the number of table caches. Since each client connection accesses at least one table, the value of this parameter is related to max_connections.

Caching mechanism

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

Before performing a cache operation, table_cache is used to limit the maximum number of cached tables: if the currently cached table does not reach table_cache, a new table will be added; if this value has been reached, MySQL will cache it based on The last query time, query rate and other rules of the table release the previous cache.

Parameter tuning

Generally speaking, you can view the values ​​​​of Open_tables and Opened_tables in phpmyadmin, or you can execute

mysql> show global status like 'open%_tables';

to view the current open_tables The situation is as shown in the figure:

# to view the values ​​of these two parameters. Among them, Open_tables is the number of tables currently being opened, and Opened_tables is the number of all open tables.

If the value of Open_tables is close to the value of table_cache, and Opened_tables continues to grow, it means that mysql is releasing the cached table to accommodate the new table. At this time, the value of table_cache may need to be increased. For most situations,

is a more suitable value:

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

If you are not very sure about this parameter, VPS Management Encyclopedia gives a very conservative setting suggestion: MySQL databasePut it in the production environment for a trial run 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 with relatively high load.

Clear cache

Executing the

mysql > flush tables;

command will clear all currently cached tables.

The above is the detailed content of Description of table_cache configuration parameters in mysql. For more information, please follow other related articles on the PHP Chinese website!