key_buffer_size

USAGE

DETAILS

The key_buffer_size system variable sets the size of the MyISAM key cache in bytes.

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:

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:

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;

SYNONYMS

SCHEMA

PARAMETERS

Command-line

--key_buffer_size=#

Configuration file

Supported

Dynamic

Yes

Scope

Global

Data Type

BIGINT UNSIGNED

Minimum Value

0

Maximum Value

18446744073709551615

Product Default Value

134217728

SKYSQL

Cloud

SkySQL Topology

ES Ver

Service Default

AWS

Multi-Node Analytics

10.6

134217728

Single Node Analytics

10.6

134217728

Replicated Transactions

10.4

20971520

10.5

20971520

10.6

20971520

Single Node Transactions

10.4

20971520

10.5

20971520

10.6

20971520

GCP

Multi-Node Analytics

10.6

134217728

Single Node Analytics

10.6

134217728

Replicated Transactions

10.4

134217728

10.5

134217728

10.6

134217728

Single Node Transactions

10.4

134217728

10.5

134217728

10.6

134217728

Cloud

SkySQL Topology

ES Ver

Service Default

AWS

ColumnStore Data Warehouse

10.6

134217728

Enterprise Server With Replica(s)

10.4

20971520

10.5

20971520

10.6

20971520

Enterprise Server Single Node

10.4

20971520

10.5

20971520

10.6

20971520

GCP

ColumnStore Data Warehouse

10.6

134217728

Enterprise Server With Replica(s)

10.4

134217728

10.5

134217728

10.6

134217728

Enterprise Server Single Node

10.4

134217728

10.5

134217728

10.6

134217728

PRIVILEGES

EXAMPLES

Set in a Configuration File

A configuration file can persistently set the global value:

[mariadb]
key_buffer_size=512M

The value is specified in bytes.

The value can be specified in a larger unit by including a suffix:

Unit

Suffix

kilobyte

K

megabyte

M

gigabyte

G

After changing a configuration file, the server must be restarted. To change the global value without a server restart, set it with SET GLOBAL too.

Set Dynamically with SET GLOBAL

SET GLOBAL can dynamically set the global value:

SET GLOBAL key_buffer_size = (512 * 1024 * 1024);

The value is specified in bytes.

The value can be specified in a larger unit using an expression:

Unit

Expression

kilobyte

(size * 1024)

megabyte

(size * 1024 * 1024)

gigabyte

(size * 1024 * 1024 * 1024)

This operation requires the SUPER privilege.

When the server is restarted, the change does not persist. To make the change persist on restart, set it in a configuration file too.

Set on the Command-line

When MariaDB Server (mariadbd) is started via the command-line, the --key-buffer-size command-line option can set the global value:

$ mariadbd .. --key-buffer-size='512M' ..

The value is specified in bytes.

The value can be specified in a larger unit by including a suffix:

Unit

Suffix

kilobyte

K

megabyte

M

gigabyte

G

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';

ERROR HANDLING

FEATURE INTERACTION

RESPONSES

DIAGNOSIS

ISO 9075:2016

CHANGE HISTORY

Release Series

History

23.08 Enterprise

  • Present starting in MariaDB Enterprise Server 23.08.0.

23.07 Enterprise

  • Present starting in MariaDB Enterprise Server 23.07.0.

10.6 Enterprise

  • Present starting in MariaDB Enterprise Server 10.6.4-1.

10.6 Community

  • Present starting in MariaDB Community Server 10.6.0.

10.5 Enterprise

  • Present starting in MariaDB Enterprise Server 10.5.3-1.

10.5 Community

  • Present starting in MariaDB Community Server 10.5.0.

10.4 Enterprise

  • Present starting in MariaDB Enterprise Server 10.4.6-1.

10.4 Community

  • Present starting in MariaDB Community Server 10.4.0.

10.3 Enterprise

  • Present starting in MariaDB Enterprise Server 10.3.16-1.

10.3 Community

  • Present starting in MariaDB Community Server 10.3.0.

10.2 Enterprise

  • Present starting in MariaDB Enterprise Server 10.2.25-1.

10.2 Community

  • Present starting in MariaDB Community Server 10.2.0.

Release Series

History

23.08 Enterprise

  • Present starting in MariaDB Enterprise Server 23.08.0.

23.07 Enterprise

  • Present starting in MariaDB Enterprise Server 23.07.0.

10.6 Enterprise

  • Present starting in MariaDB Enterprise Server 10.6.4-1.

10.5 Enterprise

  • Present starting in MariaDB Enterprise Server 10.5.3-1.

10.4 Enterprise

  • Present starting in MariaDB Enterprise Server 10.4.6-1.

EXTERNAL REFERENCES