fit mysql db in memory

Question

I am using Redis database where we store the navigational information. These data must be persistent and should be fetched faster. I don't have more than 200 MB data in this data set.

I face problem when writing admin modules for redis db and I really missing the sql schema and power of django style admin modules.

Now I am thinking of using MySQL. The requirement is, I want the persistent database but the data can be loaded into the memory like redis so that I can do the SQL queries REALLY faster.

Is it possible to use MySQL in persistent mode and instruct MySQL to use the memory for querying purpose? What is the best suitable MySQL DB where I do not worry much on consistencies where our writes are very few.

Answer 1

I would think you could have a persistent table, copy all of the data into a MEMORY engine table whenever the server starts, and have triggers on the memory db for INSERT UPDATE and DELETE write to the persistent table so it is hidden for the user. Correct me if I'm wrong though, it's just the approach I would first try.

Answer 2

I would create a read only slave to your mysql database and force its database engines to memory. You'd have to handle failures by re-initializing the read only database, but that can be scripted rather easily.

This way you still have your persistence in the regular mysql database and your read speed in the read only memory tables.

Answer 3

i did that with HSQLDB (link: http://hsqldb.org/)

has a few basic SQL features missing, like the IN clause. but it mostly does the job.