Analysis of Table_cache configuration parameters of Mysql performance optimization _mysql

Table_cache is a very important MySQL performance parameter, which is called Table_open_cache in the 5.1.3 version. Table_cache is primarily 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.

Caching mechanism

When a connection accesses a table, MySQL checks the number of currently cached tables. If the table is already open in the cache, direct access to the table in the cache is faster; If the table is not cached, the current table is added to the cache and queried.

Table_cache is used to limit the maximum number of cached tables before performing a cache operation: if the currently cached table does not reach Table_cache, the new table will be added, and if this value is reached, MySQL will release the previous cache based on rules such as the last query time of the cached table, query rate, and so on.

Parameter tuning

In general, you can view the values of Open_tables and opened_tables in phpMyAdmin, or you can perform

Copy Code code as follows:

Mysql> show global status like ' Open%_tables ';

View the current open_tables situation, as shown in figure:

To see the values of these two parameters. Where Open_tables is the number of tables currently being opened, Opened_tables is the number of all open tables.

If the value of the open_tables is already close to the Table_cache value and the opened_tables is growing, MySQL is releasing the cached table to accommodate the new table, which may require a greater table_cache value. For most cases,

Values that are more appropriate:

Copy Code code as follows:

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

If the grasp of this parameter is not very accurate, the VPS management Encyclopedia gives a very conservative suggestion: the MySQL database in the production environment for a trial run for a period of time, and then adjust the value of the parameter is greater than the number of opened_tables, It is also guaranteed to be slightly larger than opened_tables under the extreme conditions of high load.

Empty cache

Perform

Copy Code code as follows:

mysql > Flush tables;

command clears all currently cached tables.