How to use table_cache configuration parameters to perform performance optimization on Mysql

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 is already open 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 the query will be performed.

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: put the MySQL database in Test run for a period of time in the production environment, 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 How to use table_cache configuration parameters to perform performance optimization on Mysql. For more information, please follow other related articles on the PHP Chinese website!