Analysis of the Table_Cache Configuration Parameters of Mysql Performance Optimization
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.Since each client connection will access 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 current 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, the current table will be added to the cache and be queried.
Before performing the 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 the table according to the last Query time, query rate and other rules before releasing the cache.
Parameter tuning
Generally speaking, you can view the value of Open_tables and Opened_tables in phpmyadmin, or you can execute it
The code is as follows:
mysql> show global status like'open%_tables';
Check the current open_tables situation, 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 tables that have been opened.
If the value of Open_tables is close to the value of table_cache, and Opened_tables is still getting bigger, it means that mysql The cached table is being released to accommodate the new table.At this time, the value of table_cache may need to be increased.For most cases,
More suitable value:
The code is as follows:
Open_tables/Opened_tables >=0.85
Open_tables/table_cache <=0.95
If you are not sure about this parameter, VPS Management Encyclopedia gives a very conservative setting suggestion: put the MySQL database in a production environment for trial operation for a period of time, and then adjust the value of the parameter 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.
Empty the cache
Execute
The code is as follows:
mysql > flush tables;
The command will clear all currently cached tables.
Tags
Technical otaku
Sought technology together
0 Comments