Table_open_cache of MySQL performance optimization

MySQL default Table_open_cache is 64, this value is small, if the max_connections is large, it is easy to cause performance problems.

Performance: Database query efficiency is slow, show processlist found that more than the query is opening table.

Further confirmation, execute the following statement:

Mysql> show global status like ' open%tables% ';

+---------------+---------+

| variable_name | Value |

+---------------+---------+

| Open_tables | 345 |

| Opened_tables | 9734116 |

+---------------+---------+

The Opened_tables value is very large, indicating that the cache is too small, causing the open table to be used frequently to view the current Table_open_cache settings:

Mysql> Show variables like '%table_open_cache% ';

+------------------+-------+

| variable_name | Value |

+------------------+-------+

|     Table_open_cache | 64|

+------------------+-------+

The default is 64, and some data recommend setting this value to (the number of tables used by the max_connections* query). I found in practice that the general setting for Max_connections is no problem (if not enough, you can continue to increase, but you cannot set the big outrageous, may cause other problems). 4G memory machine, recommended setting to 2048 immediate effect settings:

mysql> set global table_open_cache=2048;

Query OK, 0 rows Affected (0.00 sec)

After setting, you can observe that if opening table no longer appears, the modification is valid and added to the MySQL configuration file so that the setting can still be maintained after the database restarts.

Mysql> Show variables like '%table_open_cache% ';

+----------------------------+-------+

| variable_name | Value |

+----------------------------+-------+

| Table_open_cache | 400 |

| table_open_cache_instances | 1 |

+----------------------------+-------+

2 rows in Set (0.00 sec)

Values that are more appropriate:

Open_tables/opened_tables >= 0.85

Open_tables/table_open_cache <= 0.95

Table_open_cache of MySQL performance optimization