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.