Keeping data plus index-data in memory - InnoDB vs. MyISAM

Question

Assume a database consisting of 1 GB of data and 1 GB of index data.

To minimize disk IO and hence maximize performance I want to allocate memory to MySQL so that the entire dataset including indexes can be kept in RAM (assume that the machine has RAM in abundance).

The InnoDB parameter innodb_buffer_pool_size is used to specify the size of the memory buffer InnoDB uses to cache data and indexes of its tables. (Note: The memory is used for data AND indexes.)

The MyISAM parameter key_buffer_size is used to specify the size of the memory buffer MyISAM uses to cache indexes of its tables. (Note: The memory is used ONLY for indexes.)

If I want the 2 GB database (1 GB data and 1 GB index) to fit into memory under InnoDB, I'd simply configure the innodb_buffer_pool_size to be 2GB. The two gigabytes will hold both the data and the index.

However, when setting the MyISAM key key_buffer_size to 2GB that space will be used for the index, but not for the data.

My questions are:

• Can MyISAM's "data buffer size" (not index data) be configured explicitly?
• When will MyISAM read table data (excluding index data) from disk and when will it read from memory?

Answer 1

• No MyISAM has no general purpose data cache. This is documented in the "key_buffer_size" description from the official documentation: This is because MySQL relies on the operating system to perform file system caching for data reads, so you must leave some room for the file system cache.

Modern OSes, especially Linux, tend to have very smart virtual memory subsystems that will keep frequently accessed files in the page cache, so disk I/O is kept at a bare minimum when the working set fits in available memory.

• So to answer your second question: never.

It's important not to fall into "buffer oversizing" too for the various myisam variables such as read_buffer_size, read_rnd_buffer_size, sort_buffer_size, join_buffer_size, etc as some are dynamically allocated, so bigger doesn't always mean faster - and sometimes it can even be slower - see this post on mysqlperformanceblog for a very interesting case.

If you're on 5.1 on a posix platform, you might want to benchmark myisam_use_mmap on your workload, it's supposed to help high contention cases by reducing the quantity of malloc() calls.