Optimal setting of MySQL key_buffer_size parameters _mysql

Let's look at the interpretation of this argument by document:

The index block size of the cached MyISAM table can be shared by all processes. When set Key_buffer_size, the operating system does not immediately assign the value of the Key_buffer_size setting, but assigns it when needed. You can set multiple Key_buffer, and when the setting is not the default Key_buffer 0 o'clock, MySQL moves the cached index block to the default Key_buffer and deletes the index block that is no longer in use. MyISAM table can only cache index block, can not cache data block.

Originally described:

The Index blocks for MyISAM tables are buffered and are are shared by the all threads. Key_buffer_size is the size of the buffer used for index blocks. The key buffer is also known as the key cache.

The maximum allowable setting for key_buffer_size are 4GB on 32-bit platforms. As of MySQL 5.0.52, values larger than 4GB are allowed for 64-bit platforms (except 64-bit Windows, for which large values Are truncated to 4GB with a warning). The effective maximum size might is less, depending on your available-physical RAM and per-process ram limits imposed by Y Our operating system or hardware platform. The value of this variable indicates the amount of memory requested. Internally, the server allocates as much memory as possible up to this amount, but the actual the allocation might >

Increase the value to get better index handling (for all reads and multiple writes) to as much as you can afford. Using a value that's 25% of total memory on a machine that mainly runs the MySQL is quite common. However, if you do the value too large (for example, more than 50% of your total memory) your system might start to page and become extremely slow. MySQL relies on the operating system to perform file system caching for data reads, so must leave some room for the fi Le system cache. Consider also the memory requirements of the other storage engines.

1. Set up cache index:

mysql> set global key_buffer_1.key_buffer_size=8384512;
Query OK, 0 rows affected (0.01 sec)
mysql> set global key_buffer_2.key_buffer_size=8384512;
Query OK, 0 rows affected (0.01 sec)

2, put the specified table in key buffer

Mysql> Cache index t1,t2 in key_buffer_1;
+ ———— + —————— –+ ———-+ ———-+
| Table | Op | Msg_type | Msg_text |
+ ———— + —————— –+ ———-+ ———-+
| Luoxuan.t1 | Assign_to_keycache | Status | OK |
| Luoxuan.t2 | Assign_to_keycache | Status | OK |
+ ———— + —————— –+ ———-+ ———-+
2 rows in Set (0.00 sec)

3. Pre-loading table index block

Mysql> load index into the cache t1,t2;
+ ———— + ———— –+ ———-+ ———-+
| Table | Op | Msg_type | Msg_text |
+ ———— + ———— –+ ———-+ ———-+
| Luoxuan.t1 | Preload_keys | Status | OK |
| Luoxuan.t2 | Preload_keys | Status | OK |
+ ———— + ———— –+ ———-+ ———-+
2 rows in Set (0.00 sec)

Let's take a look at the calculation of hit ratio and key buffer usage





Cache Hit Ratio:

100– ((key_reads)/key_read_requests)

Key Buffer Usage Rate

100– ((key_blocks_unused * key_cache_block_size) * 100/key_buffer_size) in the MySQL database, MySQL key_buffer_size is the effect on MyISAM table performance One of the largest parameters, the following will be the MySQL key_buffer_size parameter settings for your reference.

The following is a configuration with MyISAM as the primary storage Engine server:

Mysql> Show variables like ' key_buffer_size ';
+-----------------+------------+
| variable_name | Value |
+-----------------+------------+
| Key_buffer_size | 536870912 |
+-----------------+------------+

Allocated 512MB memory to the MySQL key_buffer_size, let's take a look at the use of key_buffer_size:
Mysql> show global status like ' key_read% ';

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

There are 27,813,678,764 index read requests, 6,798,830 requests are not found in memory read the index directly from the hard disk, the probability of the index misses the cache is computed:

Key_cache_miss_rate = key_reads/key_read_requests * 100%

For example, the above data, Key_cache_miss_rate 0.0244%, 4,000 index read request only a direct read hard drive, has been very bt, Key_cache_miss_rate is very good under 0.1% (every 1000 requests have a direct read hard drive ), if the key_cache_miss_rate is below 0.01%, key_buffer_size distribution is too much, can be appropriately reduced.
The MySQL server also provides key_blocks_* parameters:

Mysql> show global status 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 used, such as this server, all caches are used, or increase key_buffer_size, Either the transition index or the cache is full. More Ideal settings:

Key_blocks_used/(key_blocks_unused + key_blocks_used) * 100%≈80%

Key_buffer_size Setting considerations
1. A single key_buffer size cannot exceed 4G, and if the setting is more than 4G, it is possible to encounter the following 3 bugs:

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 that the Key_buffer be set to 1/4 of physical memory (for the MyISAM engine), or even the 30%~40% of physical memory, if the key_buffer_size setting is too large, the system will frequently change pages and reduce system performance. Because MySQL uses the operating system's cache to cache data, we have to leave enough memory for the system; In many cases the data is much larger than the index.

3. If the machine performance is superior, may set several key_buffer, separately lets the different key_buffer to cache the specialized index

Just for "novice", 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 the size of your application, the size of the key_reads/key_read_requests is normally less than 0.01

Resources:

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

Optimizing the key_buffer_size of 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.

After comparison, for my memory is 64G, so I set the key_buffer_size to 2048M, feel much better, there is a memory overflow. Solved the problem. Follow-up what is the situation of the cloud Habitat Community Small series continue to supplement.

Alibaba Cloud Hot Products
Elastic Compute Service (ECS)	Dedicated Host (DDH)	ApsaraDB RDS for MySQL (RDS)	ApsaraDB for PolarDB(PolarDB)	AnalyticDB for PostgreSQL (ADB for PG)
AnalyticDB for MySQL(ADB for MySQL)	Data Transmission Service (DTS)	Server Load Balancer (SLB)	Global Accelerator (GA)	Cloud Enterprise Network (CEN)
Object Storage Service (OSS)	Content Delivery Network (CDN)	Short Message Service (SMS)	Container Service for Kubernetes (ACK)	Data Lake Analytics (DLA)
ApsaraDB for Redis (Redis)	ApsaraDB for MongoDB (MongoDB)	NAT Gateway	VPN Gateway	Cloud Firewall
Anti-DDoS	Web Application Firewall (WAF)	Log Service	DataWorks	MaxCompute
Elastic MapReduce (EMR)	Elasticsearch