MySQL table_cache optimization (1)

The table_cache parameter sets the table cache quantity. Each connection will open at least one table cache. Therefore, the size of table_cache should be related to the settings of max_connections. For example, for 200 concurrent connections, the table cache should be at least 200 × N, where N is the maximum number of tables in a join where the application can execute the query. In addition, additional file descriptors must be reserved for temporary tables and files.
When Mysql accesses a table, if the table has been opened in the cache, it can directly access the cache. If the table is not cached yet, but there is still space in the Mysql table buffer, this table is opened and put into the table buffer. If the table cache is full, the unused table will be released according to certain rules, or the table cache will be expanded temporarily for storage, the advantage of using Table cache is that you can access table content more quickly.
Executing flush tables will clear the cached content. In general, you can check the status values Open_tables and Opened_tables of the database peak time to determine whether to increase the value of table_cache. Open_tables indicates the number of opened tables and Opened_tables indicates the number of opened tables. The following example shows the changes of the two status values:
First, clear the table cache:
Mysql> flush tables;
Query OK, 0 rows affected (0.00 sec)
View the current table cache status:
Mysql> show global status like open % _ tables;
+ --------------- + ------- +
| Variable_name | Value |
+ --------------- + ------- +
| Open_tables | 0 |
| Opened_table | 543 |
+ --------------- + ------- +
2 rows in set (0.00 sec)
Access a table from the current connection:
Mysql> select count (*) from t1;
+ ---------- +
| Count (*) |
+ ---------- +
| 4 |
+ ---------- +
1 row in set (0.03 sec)
Mysql> show global status like open % _ tables;
+ --------------- + ------- +
| Variable_name | Value |
+ --------------- + ------- +
| Open_tables | 1 |
| Opened_table | 544 |
+ --------------- + ------- +
2 rows in set (0.00 sec)
Open_tables and opened_tables both increase by 1.
Access another table and you can see that both parameters are added.
Mysql> select count (*) from t2;
+ ---------- +
| Count (*) |
+ ---------- +
| 1 |
+ ---------- +
1 row in set (0.06 sec)
Mysql> show global status like open % _ tables;
+ --------------- + ------- +
| Variable_name | Value |
+ --------------- + ------- +
| Open_tables | 2 |
| Opened_table | 545 |
+ --------------- + ------- +
2 rows in set (0.00 sec)
When accessing table t1 again:
Mysql> select * from t1;
+ ------ +
| Id |
+ ------ +
| 2 |
| 1 |
| 1 |
| 3 |
+ ------ +
4 rows in set (0.02 sec)
Mysql> show global status like open % _ tables;
+ --------------- + ------- +
| Variable_name | Value |
+ --------------- + ------- +
| Open_tables | 2 |
| Opened_table | 545 |
+ --------------- + ------- +
2 rows in set (0.00 sec)
The two parameters remain unchanged because the table has been opened in the table cache and are not opened repeatedly.
If we find that open_tables is close to table_cache and the value of Opened_tables is gradually increased, it means that table_cache may be set too small. We often need to clear the cached table and put the new table into the cache, in this case, you can increase the size of this parameter to Improve the access efficiency.