Optimize key_buffer_size Settings for mysql

key_buffer_size

key_buffer_size Specifies the size of the index buffer, which determines the speed of index processing and, in particular, the speed of index reads. You can know if key_buffer_size is set properly by checking the status values Key_read_requests and Key_reads. The ratio key_reads /key_read_requests should be as low as possible, at least 1:100, 1:1000 is better (the status values above can be obtained using SHOW STATUS LIKE 'key_read%').
key_buffer_size only works for MyISAM tables. Use this value even if you do not use the MyISAM table, but the internal temporary disk table is MyISAM. You can use the check status value created_tmp_disk_tables for details.
For machines with 1G memory, if the MyISAM table is not used, the recommended value is 16M (8-64M)

Suggestions for improving performance:
1. If opened_tables is too large, make table_cache in my.cnf larger
2. If Key_reads is too large, make key_buffer_size larger in my.cnf. The failure rate of the cache can be calculated using Key_reads/Key_read_requests
3. If Handler_read_rnd is too large, then many of the queries you write in SQL will scan the entire table instead of using the key
4. If Threads_created is too large, increase the value of thread_cache_size in my.cnf. The cache hit ratio can be calculated using Threads_created/Connections
5. If Created_tmp_disk_tables is too large, increase the value of tmp_table_size in my.cnf and replace the disk-based temporary table with the memor-based temporary table

MySQL optimization small case: key_buffer_size
key_buffer_size is the one parameter that has the most impact on the performance of MyISAM table. The following is the configuration of a server with MyISAM as the main storage engine:

mysql > SHOW VARIABLES LIKE '%key_buffer_size%';

key_buffer_size: key_size: key_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)
1 There are a total of Key_read_requests index requests. 1 There are a total of Key_reads physical IO

Key_reads/Key_read_requests ≈ less than 0.1% is preferable.

key_buffer_size setup considerations

1. The size of a single key_buffer cannot be larger than 4G. If you set this to more than 4G, you may encounter the following 3 bug:

http://bugs.mysql.com/bug.php?id=29446

http://bugs.mysql.com/bug.php?id=29419

http://bugs.mysql.com/bug.php?id=5731

2. It is recommended to set key_buffer as 1/4 of the physical memory (for the MyISAM engine), or even 30%~40% of the physical memory. If key_buffer_size is set too large, the system will frequently page feed and reduce system performance. Because MySQL uses the operating system's cache to cache data, we need to keep enough memory for the system; In many cases the data is much larger than the index.

3. If the machine performance is superior, you can set multiple key_buffer, and have different key_buffer to cache the specific index

This is only for "beginners", we can further optimize key_buffer_size, use "show status" to view "Key_read_requests, Key_reads, Key_write_requests and Key_writes "to adjust to a more suitable size for your application. The size of Key_reads/Key_read_requests is normally less than 0.01

References:

http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_key_buffer_size

key_buffer_size is set to 2048M to solve the problem.