MySQL performance optimization (ii)

Key_buffer_size

To minimize the disk I/O, the MyISAM Storage engine table uses the key cache to cache the index, and the size of the key cache is set by the Key-buffer-size parameter. If the table used in the application is dominated by the MyISAM storage engine, the value of the parameter should be increased appropriately to cache the index as much as possible, increasing the speed of access.
By default, all indexes use the same key cache, and when the index accessed is not in the cache, use the LRU (Least recently used least recently used) algorithm to replace the least recently used index block in the cache. To further avoid contention for key caches, starting with MySQL5.1, you can set up multiple key caches and specify the key caches to use for different index keys. [1]

The following configuration with MyISAM as the primary storage engine server: [2]

MySQL>like'key_buffer_size';

+-----------------+------------+
| variable_name | Value
|
+-----------------+------------+
| Key_buffer_size | 536870912
|
+-----------------+------------+

Allocating 512MB of memory to MySQL Key_buffer_size, let's look at the usage of key_buffer_size:

MySQL>like'key_read%';

+------------------------+-------------+
|  variable_name | Value
|
+------------------------+-------------+
| key_read_requests | 27813678764 |
| Key_reads | 6798830 |
+------------------------+-------------+

A total of 27,813,678,764 index read requests, with 6,798,830 requests not found in memory directly from the hard disk to read the index, calculate the probability of index misses cache:

= / *  - %

For example, the above data, Key_cache_miss_rate is 0.0244%, 4,000 index read requests have a direct read hard disk, already very bt, key_cache_miss_rate under 0.1% is very good (every 1000 requests have a direct read hard disk If Key_cache_miss_rate is below 0.01%, the key_buffer_size is allocated too much and can be appropriately reduced.
The MySQL server also provides the key_blocks_* parameter:

MySQL>like'key_blocks_u%';

+------------------------+-------------+
| variable_name | Value
|
+------------------------+-------------+
| key_blocks_unused | 0 |
| key_blocks_used | 413543 |
+------------------------+-------------+

Key_blocks_unused represents the number of unused cache clusters (blocks), key_blocks_used indicates the maximum number of blocks ever used, such as this server, all caches are used, or key_buffer_size is added, It's either a transition index or a full cache. More Ideal settings:

/ + *  - %  the %

Note, it is not possible to determine whether the Key_buffer_size value is reasonable by this result value, refer to [3].

Query_cache_size

MySQL Query cache query_cache_type. So how did MySQL decide whether or not to put the query results in the query cache? is determined by the Query_cache_type variable. [4]
This variable has three values: 0,1,2, which represents off, on, and demand, respectively. [5]

• 0 or off will block caching or query cache results.
• 1 or on will allow caching, except for query statements starting with select Sql_no_cache.
• 2 or demand, only those query statements starting with select Sql_cache are enabled for caching.

Thread_cache_size

If we set the thread_cache_size in the MySQL server configuration file, when the client disconnects, the server processes the client's thread to cache in response to the next customer instead of destroying it (provided the cache count is not up to the limit). Threads_created indicates the number of threads created, and if the threads_created value is found to be too large, it indicates that the MySQL server has been creating threads, which is also a relatively resource-intensive, and can be appropriately increased thread_cache_size in the configuration file. [6]

Query process usage:

MySQL> like ' Thread%';

+ —————————-+ ——————-+
| variable_name | Value |
+ —————————-+ ——————-+
| threads_cached | 26 |
| threads_connected | 510 |
| threads_created | 35168165 |
| threads_running | 459 |
+ —————————-+ ——————-+

Query Server Thread_cache_size configuration:

MySQL> like ' thread_cache_size';

+ ————————-+ ————-+
| variable_name | Value |
+ ————————-+ ————-+
| Thread_cache_size | 32 |
+ ————————-+ ————-+

Reference:

[1] adjusting the key_buffer_size parameter. http://blog.sina.com.cn/s/blog_4a8902380100brg0.html

[2] mysql key_buffer_size parameter settings. http://database.51cto.com/art/201010/229939.htm

[3] MySQL optimizer: key_buffer_size.http://www.php1.cn/article/9810.html

[4] MySQL query cache query_cache_type.http://blog.csdn.net/wmsjlihuan/article/details/16337685

[5] MySQL Query cache (learning Note). http://blog.csdn.net/linuxin/article/details/2665015

[6] mysql optimization parameter thread_cache_size.http://www.fangshanzi.com/mysql%e4%bc%98%e5%8c%96%e5%8f%82%e6%95%b0thread_cache_ size/http://www.fangshanzi.com/mysql%e4%bc%98%e5%8c%96%e5%8f%82%e6%95%b0thread_cache_size/

MySQL performance optimization (ii)