Basic Details
The key_buffer_size variable, also called the key cache, sets the buffer size for index blocks.
key_buffer_size – Usage
The key_buffer_size variable is used with the MyISAM storage engine in earlier versions of MySQL. InnoDB replaced MyISAM starting with MySQL 5.5. This variable can be set very low if you do not use MyISAM within your servers.
When key_buffer_size is increased more memory is allocated for the MySQL index buffer. More memory means less reliance on disk I/O and improved performance. For server situations still using MyISAM, set this value at 25 to 30% of available memory.
This variable will have a minimal performance impact for most users, as the majority of servers have very few MyISAM tables.
When key_buffer_size is increased more memory is allocated for the MySQL index buffer. More memory means less reliance on disk I/O and improved performance. For server situations still using MyISAM, set this value at 25 to 30% of available memory.
This variable will have a minimal performance impact for most users, as the majority of servers have very few MyISAM tables.
key_buffer_size – Configuration
key_buffer_size variable can be configured using the command line or configuration file:
Command Line:
mysqld> set global key_buffer_size = XX
Replace XX with value to suit your database needs. This change will only be applied to new connections. Make a new connection to the server and the values will update. SET GLOBAL will not persist through a server restart.
Configuration File:
[mysqld]
key_buffer_size = XX
Replace XX with value to suit your database needs. Must restart MySQL server to see changes applied.
Command Line:
mysqld> set global key_buffer_size = XX
Replace XX with value to suit your database needs. This change will only be applied to new connections. Make a new connection to the server and the values will update. SET GLOBAL will not persist through a server restart.
Configuration File:
[mysqld]
key_buffer_size = XX
Replace XX with value to suit your database needs. Must restart MySQL server to see changes applied.
key_buffer_size - Considerations
When configuring the key_buffer_size variable, try to keep this value between 25% and 30% of the server's total memory. Be careful not to raise key_buffer_size too high (above 50%), or you will experience major slowdowns due to paging.
Ideally, keep the ratio between key_reads to key_read_requests as low as possible. This means that for every key_read from the disk, there are way more key_read_requests from memory. 1 key_read per 1000 key_read_requests is a great ratio.
Remember that the InnoDB storage engine is likely in use, and has its own memory requirements. For most modern servers, start with 1% of RAM and adjust from there.
Adjust key_buffer_size in increments or multiples of 4096 bytes or the system will warn you and then automatically select a value that matches the 4096-byte rule.
NOTE: Setting key_buffer_size to 0 will cause the key cache to not initialize at server startup. Upon changing to a non-zero number, the key cache will initialize.
Ideally, keep the ratio between key_reads to key_read_requests as low as possible. This means that for every key_read from the disk, there are way more key_read_requests from memory. 1 key_read per 1000 key_read_requests is a great ratio.
Remember that the InnoDB storage engine is likely in use, and has its own memory requirements. For most modern servers, start with 1% of RAM and adjust from there.
Adjust key_buffer_size in increments or multiples of 4096 bytes or the system will warn you and then automatically select a value that matches the 4096-byte rule.
NOTE: Setting key_buffer_size to 0 will cause the key cache to not initialize at server startup. Upon changing to a non-zero number, the key cache will initialize.
If you're confused, let Releem take handle the configuration process and remove all uncertainty from your system variable settings.
Releem Agent will collect metrics and important system information that is transferred to the Releem Cloud Platform. Releem Cloud Platform will calculate MySQL Performance Score and make any recommendations to improve your server performance. Releem can automatically apply these settings, including any recommendations for key_buffer_size.
Releem Agent will collect metrics and important system information that is transferred to the Releem Cloud Platform. Releem Cloud Platform will calculate MySQL Performance Score and make any recommendations to improve your server performance. Releem can automatically apply these settings, including any recommendations for key_buffer_size.
