Optimize Mysql key_buffer_size Settings _mysql

Key_buffer_size
KEY_BUFFER_SIZE Specifies the size of the index buffer, which determines the speed at which indexing is processed, especially the speed at which index reads. By checking the status values key_read_requests and Key_reads, you can see if the key_buffer_size settings are reasonable. The proportional key_reads/key_read_requests should be as low as possible, at least 1:100,1:1000 better (the above state values can be obtained using show status like ' key_read% ').
Key_buffer_size only works on the MyISAM table. Even if you don't use the MyISAM table, the internal temporary disk table is the MyISAM table, and you also use this value. You can use the Check status value created_tmp_disk_tables to know the details.
For machines with 1G of memory, if the MyISAM table is not used, the recommended value is 16M (8-64m)

Recommendations for improving performance:
1. If the opened_tables is too large, the table_cache in the my.cnf should be bigger.
2. If the key_reads is too large, the my.cnf key_buffer_size should be larger. Can calculate the cache failure rate with key_reads/key_read_requests
3. If the handler_read_rnd is too large, then many queries in the SQL statement you write are to scan the entire table without playing the role of the key
4. If the threads_created is too large, it is necessary to increase the value of thread_cache_size in MY.CNF. You can use Threads_created/connections to calculate cache hit rates
5. If the created_tmp_disk_tables is too large, it is necessary to increase the value of tmp_table_size in the MY.CNF and replace the disk-based based temporary table

MySQL Optimization small case: Key_buffer_size
Key_buffer_size is one of the most significant parameters for MyISAM table performance, and the following is a configuration that MyISAM as the primary storage Engine server:

Mysql> show VARIABLES like '%key_buffer_size% ';
　　
Check the Key_buffer_size usage below:

Mysql> show GLOBAL STATUS like '%key_read% ';
+-------------------+-----------------+
| variable_name | Value |
+-------------------+-----------------+
| key_read_requests | 2454354135490 |
| Key_reads | 23490 |
+-------------------+-----------------+
2 rows in Set (0.00 sec)
Altogether there was a key_read_requests index request, which took place altogether key_reads-time physical IO

key_reads/key_read_requests≈0.1% the following is better.

According to the above situation, the cloud-Habitat community small knitting key_buffer_size set to 2048M solves the problem.