Optimize mysql's key_buffer_size settings, keybuffersize

Optimize mysql's key_buffer_size settings, keybuffersize

Key_buffer_size
Key_buffer_size specifies the size of the index buffer, which determines the index processing speed, especially the index reading speed. Check the status values Key_read_requests and Key_reads to check whether the key_buffer_size setting is reasonable. The ratio of key_reads/key_read_requests should be as low as possible, at least and (the above STATUS values can be obtained using show status like 'key _ read % ).
Key_buffer_size only applies to the MyISAM table. This value is used even if you do not use the MyISAM table, but the internal temporary disk table is a MyISAM table. You can use the check status value created_tmp_disk_tables to learn the details.
For machines with 1 GB memory, if the MyISAM table is not used, the recommended value is 16 M (8-64 M)

Suggestions for improving performance:
1. If opened_tables is too large, increase table_cache in my. cnf.
2. If Key_reads is too large, you should increase the key_buffer_size in my. cnf. You can use Key_reads/Key_read_requests to calculate the cache failure rate.
3. If Handler_read_rnd is too large, many of the SQL statements you write will scan the entire table without using keys.
4. If Threads_created is too large, you need to increase the value of thread_cache_size in my. cnf. You can use Threads_created/Connections to calculate the cache hit rate.
5. If Created_tmp_disk_tables is too large, you need to increase the value of tmp_table_size in my. cnf and replace the disk-based temporary table with a memory-based temporary table.

MySQL optimization example: key_buffer_size
Key_buffer_size is a parameter that has the greatest impact on the performance of the MyISAM table. The following configuration of a storage engine server with MyISAM as the main storage engine:

Mysql> show variables like '% key_buffer_size % ';
　　
The following describes how to use key_buffer_size:

Mysql> show global status like '% key_read % ';
+ ------------------- + ----------------- +
| Variable_name | Value |
+ ------------------- + ----------------- +
| Key_read_requests | 2454354135490 |
| Key_reads | 23490 |
+ ------------------- + ----------------- +
2 rows in set (0.00 sec)
A total of Key_read_requests INDEX requests, a total of Key_reads physical IO

Key_reads/Key_read_requests ≈ 0.1% or lower is better.

According to the above situation, the Mini editor sets key_buffer_size to 2048M to solve the problem.