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

This article mainly introduces the example analysis of table_cache optimization in MySQL, which is very detailed and has certain reference value. Friends who are interested must finish it!

Table_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_cache. If you find that open_tables equals table_cache and opened_tables is growing, then you need to increase the value of table_cache (the above state values can be obtained using SHOW STATUS LIKE 'Open%tables'). Note that table_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.

The first is MyISAM:

From the official website, each thread holds a file descriptor of a data file alone, while the file descriptor of an index file is common. When table cache is insufficient, MySQL uses the LRU algorithm to kick out tables that have not been used for the longest time. If the table_cache setting is too small, MySQL will repeatedly open and close the frm file, resulting in a certain performance loss. So, is the bigger the table_cache setting, the better? As can be seen from the tests in table_cache negative scalability this article, if the table_cache setting is too large, MySQL will consume a lot of CPU to do table cache algorithm operations (which algorithm is not clear, it may be LRU). Therefore, the value of table_cache must be set properly. Take a look at the opened_tables parameter. If it keeps growing, you need to increase the value of table_cache appropriately.

Then there is InnoDB:

The metadata management of InnoDB is done in a shared tablespace, so getting the structure of the table does not require repeated parsing of the frm file, which is better than MyISAM. Even if the table_cache setting is too small, the impact on InnoDB is small, because it does not need to repeatedly open and close the frm file to get metadata. According to the test of How innodb_open_files affects performance this article, we can see that the size of table_cache and innodb_open_files has little effect on the efficiency of InnoDB. However, in the case of InnoDB crash, a small setting of innodb_open_files will affect the efficiency of recovery. So it is more appropriate to enlarge the innodb_open_files when using InnoDB.

The above is all the content of the article "sample Analysis of table_cache Optimization in MySQL". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.