Friday, March 28, 2008

Variable's Day Out #2: key_buffer_size

Properties:

Engine(s) MyISAM
Server Startup Option --key_buffer_size=<value>
Scope Global
Dynamic Yes
Possible Values Integer
Range: 8 - 4294967295 (4 GB)
Default Value 131072 (128 KB)
Category Performance

Description:

This is a global buffer where MySQL caches frequently used blocks of index data for MyISAM data. Maximum allowed size is 4GB on a 32 bit platform. Greater values are permitted for 64-bit platforms beyond MySQL 5.0.52.

Keeping this buffer to an optimal value (neither too low nor too high) contributes heavily to the performance of your MySQL server. As given in the MySQL Documentation, "using a value that is 25% of total memory on a machine that mainly runs MySQL is quite common". As suggested by Peter, "Set up to 30-40% of available memory if you use MyISAM tables exclusively. Right size depends on amount of indexes, data size and workload".

Starting with 25%, one can keep monitoring key cache hit ratio and increase it over time. A key cache hit ratio of more than 95% should be good enough. Also, for preventing oneself from allocating too much memory to this buffer, monitoring key buffer usage should be helpful. I feel one should target towards more than 80% of buffer usage.

If you are not using very few MyISAM tables, still key_buffer can be useful for MySQL's tables and temporary tables as well.

Calculations:

  • key_cache_hit_ratio = (1 - Key_reads/Key_read_requests) * 100
  • key_buffer_usage    = (1 - (Key_blocks_unused * key_cache_block_size)/key_buffer_size) * 100

Read More:

MySQL manual entry on key_buffer_size

What to tune in MySQL server after installation

MyISAM Key Cache - MySQL Manual

 

Hope this post was helpful to you.

3 comments:

Kévin said...

Hi there,

Looking at the MySQL doc: "The Key_reads/Key_read_requests ratio should normally be less than 0.01."

Applied to your key_cache_hit_ratio calculation, it seems that MySQL recommend having a key_cache_hit_ratio > 99%.

Who's not right?

Cheers,
Kévin

Parvesh Garg said...

@Kévin

It's nice if one can have >99% (which is also >95% :) ) ratio and in most cases with stale data its possible. But with too much of dynamic data, which is also true in most cases including mine, achieving something like >95% can be challenging. Also, mixed storage engine schemas (leading to memory being divided between engines) make it more difficult to achieve such rates. Though mixing storage engines is normally not recommended, sometimes its forced.

Kévin said...

Thanks for your quick answer :)

I've checked I have a ratio of 97%, that's cool then!

Great articles by the way

Kevin