MySQL table_cache optimization (4)

Mysql queries the maximum number of tables caches:
Show variables like table_cache;
 
Run the following command to query the opened descriptor in mysql:
Show status like open_tables;
 
1. Adjust table cache
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 (where open_tables is the number of tables currently opened, opened_tables indicates the number of opened tables ). That is, if open_tables is close to table_cache and the value of Opened_tables increases gradually, you need to increase the value. In addition, when Table_locks_waited is relatively high, you also need to add table_cache.
Ii. Adjust the limit on the number of files opened by the system and database
When table_cache is increased, the file descriptor is insufficient. In this case, you need to increase the limit on the number of files opened by the system and database.
1. display the file limits of the current system
# Cat/proc/sys/fs/file-max
2. Use phpmyadmin to query mysql configuration parameters (both open files limit ).
Generally, mysql 5's open files limit is automatically adjusted according to the table cache. By default, the open files limit system is adjusted to more than twice that of table cache.