MyISAM Related Options and Configuration Guidelines

This article provides an overview of MyISAM‑related configuration variables in MySQL, describing their functions, typical values, and practical tuning advice.

key_buffer_size – buffer size used for MyISAM index blocks; increasing it can improve index handling performance. It is usually set to 30‑40% of system memory on servers with around 4 GB RAM, and a minimum of 8‑64 MB is recommended even if MyISAM tables are not used.

# For a server with ~4 GB RAM, set to 256 MB or 384 MB.
# Show current value: SHOW VARIABLES LIKE '%key_buffer_size%';
# Check unused blocks: SHOW GLOBAL STATUS LIKE '%key_blocks_unused%';
# 1. Use only for MyISAM tables.
# 2. Allocate about 30‑40% of memory.
# 3. Even if not using MyISAM, keep 8‑64 MB because internal temporary tables share it.
key_buffer_size = 2048M

read_buffer_size – per‑connection buffer used for full table scans. Increasing it can speed up frequent sequential scans.

read_buffer_size = 2M

read_rnd_buffer_size – buffer for random reads during ORDER BY operations; each thread gets its own allocation. Adjust upward when sorting large datasets, but be mindful of per‑connection memory usage.

# Each client connection receives this buffer, so set it conservatively.
read_rnd_buffer_size = 16M

bulk_insert_buffer_size – cache size for bulk inserts, default 8 M. Do not set larger than key_buffer_size ; setting to 0 disables the buffer.

# 1. Do not exceed key_buffer_size.
# 2. Setting to 0 disables this parameter.
bulk_insert_buffer_size = 16M

myisam_sort_buffer_size – buffer used when rebuilding MyISAM indexes; allocate about 30‑40% of memory per thread, but avoid excessive values.

myisam_sort_buffer_size = 128M

myisam_max_sort_file_size – maximum temporary file size allowed during index rebuilds (e.g., REPAIR, ALTER TABLE, LOAD DATA INFILE). If a temporary file exceeds this size, MySQL falls back to a slower key‑buffer method.

myisam_max_sort_file_size = 1G

myisam_repair_threads – number of parallel threads used to repair indexes when a table has multiple indexes. Useful on systems with multiple CPUs and ample memory.

myisam_repair_threads = 1

myisam_recover – automatic check and repair of MyISAM tables that were not closed properly.

myisam_recover