Monday, 26 November 2007

Some MySQL hacking projects

I have two ideas for hacking MySQL in a useful manner, in response to issues I've had using it:

InnoDB tablespace usage monitor

A really simple tool that would report the amount of innodb tablespace used by specific tables and, ideally, indexes too.

This is required as I'm fairly sure that there isn't a tool to do this at the moment.

MyISAM mmap() its index files

I'm having trouble tuning MyISAM's key_buffer for production use. We want to use delay_key_write to reduce the IO of a lot of inserts, but at the same time, I'd prefer it not to get too out of hand, as large flushes create a lot of I/O in one go.

Anyway perhaps that was a poor explanation of my motivation, however, the idea would be:

  • mmap() the whole of each index file when the table is opened.

  • Leave the memory mapping in place as long as the table is open. resize the file and the memory mapping if necessary, e.g. when the index file gets bigger.

  • Have all reads/writes go via the memory mapping.

  • msync() as necessary (e.g. following a write)

I'm confident that this will have a performance benefit, but mostly, it will remove the onus on the DBA to tune key_buffer correctly, and allow the OS to use its memory how it wants to.

The downside? It won't work very well on 32-bit systems as they'll rapidly run out of address space. But do we care any more?