Master MySQL Cache Settings: Key Buffers, Table Cache, and More

key_buffer_size sets the buffer size for MyISAM table indexes and has the greatest impact on MyISAM performance. On a server with about 4 GB RAM, a value of 256 MB or 384 MB is typical.

table_cache defines the size of the table cache. When MySQL opens a table and there is space in the cache, the table stays open for faster subsequent access. Monitor the Open_tables and Opened_tables status variables; if Open_tables approaches table_cache and Opened_tables keeps increasing, increase table_cache.

sort_buffer_size is the buffer used for sorting operations. The default is 2 MB. The memory is allocated per connection, so with 100 connections the total allocation is 100 × sort_buffer_size. For a 4 GB server, a setting of 6–8 MB is recommended.

read_buffer_size controls the buffer for read operations. Like sort_buffer_size, it is allocated per connection and can be set using the same guidelines.

join_buffer_size determines the buffer for join operations, also allocated per connection and typically set similarly to sort_buffer_size.

thread_cache_size sets the maximum number of cached threads. It can be 0–16384, default 0. Caching threads reduces the overhead of creating new threads. Recommended values: 8 for 1 GB RAM, 16 for 2 GB, 32 for 3 GB, and 64 or higher for 4 GB and above.

query_cache_size specifies the size of the query cache. Observe MySQL status variables: a large Qcache_lowmem_prunes indicates frequent cache evictions, a high Qcache_hits shows frequent cache usage, and a large Qcache_free_blocks suggests fragmentation.

tmp_table_size sets the maximum size for in‑memory temporary tables. If a temporary table exceeds this size, it is written to disk. Acceptable values range from 1 KB to 4 GB.