Key _buffer_size table_cache and tmptablesize are important parameters for mysql optimization.

Key _buffer_size table_cache and tmptablesize are important parameters for mysql optimization.

MySQL server has many parameters, but for most beginners, many parameters often make us feel overwhelmed. But which parameters need to be adjusted and which have the greatest impact on server performance? For Myisam storage engine, there are two parameters: key_buffer_size and table_cache. For the InnoDB engine, parameters starting with innodb _ are also well recognized.

To view MySQL parameters, run the show variables and show status commands. The former is to view static server parameters, that is, values that are not dynamically changed after the database is started, such as the buffer zone and character set. The latter checks the dynamic running status of the server, that is, information about the dynamic changes during database operation, such as locks and current connections.

The key_buffer_size parameter is used to set the size of the index block cache, which is shared by all threads. Strictly speaking, it determines the speed of database index processing, especially the speed of index reading. Then how can we know whether the key_buffer_size settings are reasonable? Generally, we can check the status values Key_read_requests and Key_reads, the ratio of key_reads/key_read_requests should be as low as possible, such. The value can be found using the commands under I:

Mysql> show status like 'key _ read % ';
+ ------------------- + ------------ +
| Variable_name | Value |
+ ------------------- + ------------ +
| Key_read_requests | 3916880184 |
| Key_reads | 1014261 |
+ ------------------- + ------------ +
2 rows in set (0.00 sec)

3916880184/1024/1024 =? M // The unit is MB.

My key_buffer_size value is:

Key_buffer_size = 536870912/1024/1024 = 512 M,

Key_reads/key_read_requests = 1014261: 3916880184 ≈. As shown above, the health status is okay.

Table_cacheSpecify 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.

Open_tables indicates the number of opened table caches. If you perform the flush tables operation, the system will disable some table caches that are not currently in use to reduce the status value;

Opend_tables indicates the number of table caches that have been opened, which is accumulated all the time. If you perform the flush tables operation, the value will not decrease.

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 to find the table_cache value that best suits your maintenance.

The problem is that the file descriptor is insufficient when the table_cache file is increased. The following prompt appears 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.

Innodb_buffer_pool_sizeThis parameter is similar to key_buffer_size of MyISAM, but it is also different. This parameter is mainly used to cache indexes and data of innodb tables. It is the primary parameter for Innodb acceleration and optimization. This parameter is used to allocate memory. The default value of this parameter is 8 MB, which is a very small value. If it is a dedicated DB server, it can account for 70%-80% of the memory. This parameter cannot be changed dynamically. Therefore, you need to consider the allocation. If the allocation is too large, Swap will take up too much, resulting in slow Mysql query. If your data is small, you can allocate about 10% of your data size as the value of this parameter.