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?


Jeremy Cole said...

Re. tablespace used per table, what about SHOW TABLE STATUS?

Mark Robson said...

Indeed, SHOW TABLE STATUS appears to show a lot more info about InnoDB tables than I thought... well, that's excellent news.