key_buffer_size

Sizing Recommendations

The recommended size depends on how much data is in MyISAM tables and how frequently the MyISAM tables are accessed. If MyISAM tables are not used, this value can be very small to avoid wasting memory for the MyISAM key cache.

The maximum recommended size depends on many factors, including but not limited to:

• How much total memory the database node has

• Which other storage engines are in use

• What other software is running on the database node

• How many database connections are in use

Compute Key Cache Miss Rate

The MyISAM key cache miss rate can be computed using the Key_reads and Key_read_requests status variables:

• The Key_reads status variable shows the total number of MyISAM index page reads from disk

• The Key_read_requests status variable shows the total number of MyISAM index pages requested

If you divide Key_reads by Key_read_requests, that shows the MyISAM key cache miss rate:

 SELECT key_reads, key_read_requests,
    (key_reads / key_read_requests) AS key_cache_miss_rate
 FROM
    (SELECT gs1.VARIABLE_VALUE AS key_reads
    FROM information_schema.GLOBAL_STATUS gs1
    WHERE gs1.VARIABLE_NAME LIKE 'Key_reads') kr
JOIN
    (SELECT gs2.VARIABLE_VALUE AS key_read_requests
    FROM information_schema.GLOBAL_STATUS gs2
    WHERE gs2.VARIABLE_NAME LIKE 'Key_read_requests') krr;

If the above query is executed at different times, the results can be interpreted in the following way:

• If key_cache_miss_rate stays constant or grows and approaches 1, the current MyISAM key cache size is not large enough to keep up with new key read requests.

• If key_cache_miss_rate shrinks and approaches 0, the current MyISAM key cache size is either equal to or larger than needed to keep up with new key read requests.

Compute Key Cache Write Rate

The MyISAM key cache write rate can be computed using the Key_writes and Key_write_requests status variables:

• The Key_writes status variable shows the total number of MyISAM index pages written to disk

• The Key_write_requests status variable shows the total number of MyISAM index page writes requested

If you divide Key_writes by Key_write_requests, that shows the MyISAM key cache write rate:

 SELECT key_writes, key_write_requests,
    (key_writes / key_write_requests) AS key_cache_write_rate
 FROM
    (SELECT gs1.VARIABLE_VALUE AS key_writes
    FROM information_schema.GLOBAL_STATUS gs1
    WHERE gs1.VARIABLE_NAME LIKE 'Key_writes') kw
JOIN
    (SELECT gs2.VARIABLE_VALUE AS key_write_requests
    FROM information_schema.GLOBAL_STATUS gs2
    WHERE gs2.VARIABLE_NAME LIKE 'Key_write_requests') kwr;

In the above query, key_cache_write_rate would usually be close to 1, unless your workload includes a lot of delayed operations or operations that affect many rows at the same time.

Compute Key Cache Usage

The maximum and current usage of the MyISAM key cache can be computed using the Key_blocks_used and Key_blocks_unused status variables:

• Key_blocks_used gives a high water mark of the most blocks ever used in the MyISAM key cache.

• Key_blocks_unused gives the current number of unused blocks in the MyISAM key cache.

Since the units are in blocks, the key_cache_block_size system variable can be used to determine the usage:

 SELECT @@global.key_buffer_size, @@global.key_cache_block_size,
    key_blocks_used, key_blocks_unused,
    ((@@global.key_buffer_size - (@@global.key_cache_block_size * key_blocks_unused)) / @@global.key_buffer_size) AS key_cache_current_usage,
    ((@@global.key_cache_block_size * key_blocks_used) / @@global.key_buffer_size) AS key_cache_max_usage
 FROM
    (SELECT gs1.VARIABLE_VALUE AS key_blocks_used
    FROM information_schema.GLOBAL_STATUS gs1
    WHERE gs1.VARIABLE_NAME LIKE 'Key_blocks_used') kbu
JOIN
    (SELECT gs2.VARIABLE_VALUE AS key_blocks_unused
    FROM information_schema.GLOBAL_STATUS gs2
    WHERE gs2.VARIABLE_NAME LIKE 'Key_blocks_unused') kbuu;

PARAMETERS

SKYSQL

Show Value with SHOW VARIABLES

SHOW GLOBAL VARIABLES can show the global value:

SHOW GLOBAL VARIABLES
   LIKE 'key_buffer_size';

Since this system variable is only global scope, SHOW VARIABLES and SHOW SESSION VARIABLES also show the global value.

Query Value using @@global and @@session

In SELECT and other SQL statements, @@global can be queried for the global value:

SELECT @@global.key_buffer_size;

Since this system variable is only global scope, @@session can't be queried for this variable.

Information Schema

The information_schema.SYSTEM_VARIABLES table can be queried to obtain details about the system variable:

SELECT *
FROM information_schema.SYSTEM_VARIABLES
WHERE VARIABLE_NAME LIKE 'key_buffer_size';

The information_schema.GLOBAL_VARIABLES table can be queried to obtain the global value:

SELECT *
FROM information_schema.GLOBAL_VARIABLES
WHERE VARIABLE_NAME LIKE 'key_buffer_size';

This system variable is only global scope, so the information_schema.SESSION_VARIABLES table can also be queried to obtain the global value:

SELECT *
FROM information_schema.SESSION_VARIABLES
WHERE VARIABLE_NAME LIKE 'key_buffer_size';