MySQL: Load database to memory

Is there a way to load an entire MySQL database to the RAM, especially on en EC2 server?

1. The database is quite small (~500 MegaBytes)
2. I have enough memory
3. Speed issues are crucial - the resulted queries are used to serve a dynamic webpage.

Thanks,

Adam

Solution 1:

You can certainly load indexes into cache; if you have small ISAM tables that are frequently queried, MySQL will probably load these into cache as well. Here is a good Performance Wiki article on MySQL tuning.

Solution 2:

You could store the database on a RAM disk, such as ramfs. This could potentially risk the data, depending on the implementation, so you would need to design to compensate for this. One potential solution would be to have a readonly slave replicating to a RAM disk and the data there could be disposable.

MySQL Clustering (NDB engine) is designed to store data in memory. However, MySQL clustering is best suited for small datasets accessed with simple queries.

A favorite solution is memcached these days but this requires application architecture to support the technology.

This is a fairly big question. High Performance MySQL is a fantastic book that covers high level MySQL implementations.

• RAM Disk
• MySQL Cluster
• Memcached
• High Performance MySQL