Two important parameters in Mysql optimization: table_cache and key_buffer, and mysqltablecache

Two important parameters in Mysql optimization: table_cache and key_buffer, and mysqltablecache

Based on the author's experience, this article discusses two important Mysql Server optimization parameters: table_cache and key_buffer_size.

Table_cache indicates the table cache size. When Mysql accesses a table, if there is still space in the Mysql table buffer, the table will be opened and put into the table buffer, in this way, you can access the content in the table more quickly. 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. That is, if open_tables is close to table_cache, and the value of Opened_tables is gradually increased, we need to consider increasing the size of this value.

When mysql is installed by default, the value of table_cache is 256 to 512 by default for machines with less than 2 GB of memory. If the machine has 4 GB of memory, the default value is 2048, however, this means that the larger the machine memory, the larger the value, because the increase in table_cache makes mysql's SQL response faster, inevitably, more deadlocks are generated, which slows down the entire set of database operations and seriously affects performance. Therefore, during normal maintenance, you still need to make judgments based on the actual situation of the database and find the table_cache value of the database that best suits your maintenance. Some people say: "performance optimization is an art ", that's right. All works of art have been refined and refined.

The problem is that the file descriptor is not enough when table_cache is increased. There is a prompt in the mysql configuration file.

Reference

"The number of open tables for all threads. Increasing this value increases the number of file descriptors that mysqld requires.
Therefore you have to make sure to set the amount of open files allowed to at least 4096 in the variable "open-files-limit" in "section [mysqld_safe]"
We should pay attention to this problem. At the thought of this, some of our siblings may use ulimit-n to make adjustments, but this adjustment is actually not correct. After changing the terminal, this value will return to the original value, so it is best to use sysctl or modify/etc/sysctl. in the conf file, we also need to increase the open_files_limit parameter in the configuration file. For 4G memory servers, I believe that the purchased servers are almost 4 GB, the open_files_limit must be increased to at least 4096. If there are no special cases, set it to 8192.

Next we will talk about the key_buffer_size parameter. key_buffer_sizeO indicates the size of the index buffer. Strictly speaking, it determines the speed of database index processing, especially the speed of index reading. You can check the status values Key_read_requests and Key_reads based on some highly handwritten articles on the network to check whether the key_buffer_size setting is reasonable. Proportional key_reads/key_read_requests should be as low as possible, at least, is better. Although I have not found a theoretical basis, after I have tested several libraries that are actually running well, I have shown that the ratio is close to, which proves that they are correct. We can use it.