Shulou(Shulou.com)05/31 Report--

This article mainly introduces the example analysis of table_open_cache parameter optimization and reasonable configuration under MySQL 5.6. what is introduced in this article is very detailed and has a certain reference value. Interested friends must read it!

1. Introduction

Table_cache is a very important MySQL performance parameter, which was called table_open_cache in versions after 5.1.3. Table_cache is mainly 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.

For example, for 1000 joins running in parallel, you should have the table cache at least 1000 × N, where N is the maximum number of tables in a join that can be executed by the application. In addition, some additional file descriptors need to be reserved for temporary tables and files.

2. Caching mechanism

When a connection accesses a table, MySQL checks the current number of cached tables. If the table is already open in the cache, the table in the cache is accessed directly to speed up the query; if the table is not cached, the current table is added to the cache and queried.

Before performing the caching operation, table_open_cache is used to limit the maximum number of cached tables: if the currently cached table does not reach table_open_cache, a new table will be added; if this value has been reached, MySQL will release the previous cache according to the rules such as the last query time and query rate of the cached table. Whenever MySQL accesses a table, if there is space in the table buffer, the table is opened and placed in it, allowing faster access to the table contents.

3. How to judge

By checking the state values of peak time Open_tables and Opened_tables, you can decide whether you need to increase the value of table_open_cache.

If you find that open_tables equals table_open_cache and opened_tables is growing, then you need to increase the value of table_open_cache (the above state values can be obtained using SHOW GLOBAL STATUS LIKE 'Open%tables').

Note that table_open_cache cannot be set to a large value blindly. If set too high, it may result in insufficient file descriptors, resulting in unstable performance or connection failure.

Open_tables / Opened_tables > = 0.85

Open_tables / table_cache flush tables; can zero the open_tables

# service mysqld restart can say opened_tables zeroing

Here are the instructions for mysql 5.6

Table_open_cache specifies the size of the table cache. Whenever MySQL accesses a table, if there is space in the table buffer, the table is opened and placed in it, allowing faster access to the table contents.

By checking the state values of peak time Open_tables and Opened_tables, you can decide whether you need to increase the value of table_open_cache.

If you find that open_tables equals table_open_cache, and opened_tables is growing, then you need to increase the value of table_open_cache (the above status values can be obtained through SHOW GLOBAL STATUS LIKE 'Open%tables').

Note that table_open_cache cannot be blindly set to a large value, which exceeds the file descriptor of shell (viewed through ulimit-n), resulting in insufficient file descriptors, resulting in unstable performance or connection failure.

Test environment: Tencent Cloud CDB with 4000MB memory. Check the table_open_cache=512 in the console to monitor whether the table_open_cache setting is reasonable and whether it needs to be optimized.

Show variables like'% table_open_cache%'

Show global status like 'Open%tables'

It is found that open_tables is equal to table_open_cache, which means that mysql is releasing the cached table to accommodate the new table. It may be necessary to increase the value of table_open_cache. For machines with 4G memory, it is recommended to set it to 2048.

More appropriate values:

Open_tables / Opened_tables > = 0.85

Open_tables / table_open_cache

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.