Analysis of table_cache configuration parameters for MySQL Performance Optimization

Table_cache is a very important MySQL performance parameter. It is called table_open_cache in Versions later than 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.

Cache Mechanism

When a connection accesses a table, MySQL checks the number of cached tables. If the table has been opened in the cache, it will directly access the table in the cache to speed up the query. If the table is not cached, it will add the current table to the cache for query.

Before caching, table_cache is used to limit the maximum number of cached tables. If the number of cached tables does not reach table_cache, a new table is added. If this value is reached, mySQL releases the cache based on the last query time and query rate of the cache table.

Parameter Optimization

In general, you can view the values of Open_tables and Opened_tables in phpmyadmin, or execute

Copy codeThe Code is as follows: mysql> show global status like 'open % _ tables ';

View the current open_tables status,

To view the values of these two parameters. Open_tables indicates the number of opened tables and Opened_tables indicates the number of opened tables.

If the value of Open_tables is close to the value of table_cache and Opened_tables continues to grow, mysql is releasing the cached table to accommodate new tables. In this case, you may need to increase the value of table_cache. In most cases,

Applicable values:
Copy codeThe Code is as follows:
Open_tables/Opened_tables> = 0.85
Open_tables/table_cache <= 0.95

If this parameter is not accurate, the VPS management encyclopedia provides a very conservative setting suggestion: Put the MySQL database in the production environment for a period of time, then, adjust the parameter value to a value greater than the value of Opened_tables, and ensure that it is still slightly larger than Opened_tables in extreme conditions with relatively high load.

Clear Cache

Run
Copy codeThe Code is as follows: mysql> flush tables;
The command clears all cached tables.