MySQL table_cache optimization (2)

Table_cache specifies the table cache size. When MySQL accesses a table, if there is space in the table buffer, the table is opened and put into it, so that the table content can be accessed more quickly. Check the status values Open_tables and Opened_tables of the peak time to determine whether to increase the value of table_cache. If you find that open_tables is equal to table_cache and opened_tables is growing, you need to increase the value of table_cache (the preceding STATUS values can be obtained using show status like 'open % tables ). Note that you cannot blindly set table_cache to a large value. If it is set too high, the file descriptor may be insufficient, resulting in unstable performance or connection failure.
Www.itcankao.cn
First, MyISAM:
From the official website, each thread holds the file descriptor of a data file, while the file descriptor of the index file is public. When the table cache is insufficient, MySQL uses the LRU algorithm to kill the table that has 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 settings, the better? From the test in this article table_cache negative scalability, we can see that if the table_cache settings are too large, MySQL will consume a lot of CPU to perform table cache algorithm operations (which algorithm is not clear at present, may be LRU ). Therefore, you must set the value of table_cache properly. If you do not need to take a look at the opened_tables parameter, you need to increase the value of table_cache as appropriate.

Next is InnoDB:
The metadata management of InnoDB is implemented in the shared tablespace. Therefore, you do not need to parse the frm file repeatedly to obtain the table structure, which is better than MyISAM. Even if the table_cache setting is too small, it has little impact on InnoDB because it does not need to open or close the frm file repeatedly to obtain metadata. According to the test in the article "How innodb_open_files affects performance", we can see that the sizes of table_cache and innodb_open_files have little impact on InnoDB efficiency. However, in the case of InnoDB crash, too small innodb_open_files settings will affect the recovery efficiency. Therefore, it is appropriate to enlarge innodb_open_files when using InnoDB.