Wednesday 2 September 2009

On mysql's myisam_block_size setting

There is a little-known setting, myisam_block_size in MySQL. This affects the block size used in the indexes of MyISAM tables stored in .MYI files on disc and in the key buffer.

The default value is 1k, this is possibly too small for best performance on modern systems; in particular, many filesystems used a bigger block size, so writing a single index block requires a read followed by a write. Random reads are really slow on hard discs (writes are mostly fast as they go into your battery-backed raid controller which has lots of RAM).

I am currently in the process of experimenting with myisam_block_size, and so far have determined the following:
  • myisam_block_size is settable only at server start time, either in my.cnf or on the command-line
  • myisam_block_size only affects newly created tables or tables rebuilt using ALTER TABLE; existing MyISAM tables keep their old index block size and work no differently.
There is a second setting, key_buffer_block_size, which is different. This controls the size of blocks in the key buffer, which is independent.

Advantages of higher myisam_block_size
  • Fewer IO requests required to satisfy some requests (e.g. range scans) as fewer blocks are needed
  • Better index prefix compression, as the blocks are larger - particularly with PACK_KEYS enabled (index compression works on a block-by-block basis)
  • No read-then-write required by the OS to write to the underlying disc, if the block size is >= the filesystem block size
Disadvantages
  • More wasted space in partially filled blocks
  • Possibly less efficient use of key cache, as more of the "wrong" blocks are cached
  • Larger IOs are required to fulfil the same request often - as it requires the same number of bigger blocks
So it could work either way. It may depend on your workload.

Testing continues.

5 comments:

Unknown said...

We had many 5.0 crashes when experimenting with this setting, apparently 5.0 couldn't cope with old tables with differing block size. Also I had the impression you need to set both block sizes at once. This was complicated by our use of myisamchk, which has its own myisam_block_size and no key_cache_block_size. We didn't get it stable on 5.0. 5.1 seems to work, at least it doesn't crash.

From what I read about PACK_KEYS a big block size may actually be a pessimization, if your keys are packed, because blocks of packed keys have to be searched sequentially (unpacking the keys one by one in the process). Bigger blocks would equal slower linear search.

Mark Robson said...

We had some crashes when using 5.0 with "old tables" without changing myisam_block_size, so we had to upgrade them all.

I've had it running for a couple of hours on a test system (with lots of data insert load) without it crashing YET :)

Unknown said...

If I'm just testing and benchmarking a feature, I'm a bit reluctant to dump and restore a 2TB database...

Mark Robson said...

It's not necessary to dump & restore; REPAIR TABLE is sufficient. I did this with much more than 2 Tb, but months of planning were involved.

It was done gradually over three weekends with planned maintenance windows.

Anonymous said...
This comment has been removed by a blog administrator.