mysql in-memory table as a cache?

https://news.ycombinator.com/item?id=118748

by johnrob • 18 years ago

4 9 18 years ago

news.ycombinator.com

I'm curious if anyone has used an in-memory mysql table as a cache. I've got some ad-hoc data browsing needs that memcached is not well suited for. The data doesn't change often, so there is no real need to be making normal SQL calls. I've read that the ACID overhead on these memory tables is almost insignificant, and since the data is all in memory it sounds like a reasonable caching solution.

I'm just curious if anyone has used this approach before.

st3fan• 18 years ago

Why even bother using a database then? Why not simply build a convenient data structure in memory? Or do you need something global that is shared by multiple servers?

jsjenkins168• 18 years ago

I agree. The only practical reasons I can think of using a DB would be if sharing between machines, you needed rigid data synchronization at all times, or you wanted to utilize some existing DBMS backup routine. Otherwise why deal with the headache and overhead?

aston• 18 years ago

ACID-related stuff would be the big win. Also, not having to manually synchronize access to your resources (assuming you're servicing more than one request at a time).

bayareaguy2• 18 years ago

What you suggest is pretty much what everyone did before memcached: load your database server with ram and use various tricks to pin the important stuff in memory.

However the memcached code path is pretty lean and will give you better performance than in-memory mysql for single key/value lookups.

xirium2• 18 years ago

There's a patent for storing a specific database structure in memory and therefore MySQL Server memory tables use a hash structure to avoid this patent. This would very well when you lookup a row which is present. However, the entire hash is enumerated when an row is not present. So, perversely, you'd get more performance with MyISAM, a RAID card and a big disk buffer.

You could use memcached. However, if you are developing REST web services, you can retrospectively introduce Squid without changing your code.

bayareaguy2• 18 years ago

Do you know the number of the patent for this?

bmatheny• 18 years ago

There's a lot of overhead in running a MySQL instance compared with running a memcached instance (in terms of system resources, personnel, building it redundantly). I would revisit your problem and see if you can't get memcached to fit before you decide to reinvent the wheel.

If you're set on MySQL, assuming you are on Linux (or a similar platform) you should consider perhaps using BDB or MyISAM for your storage engine and just keeping the data on a ram disk. This will give you wicked fast reads.

johnrob• 18 years ago

the idea is to have a single place to cache a data structure, so that duplication is avoided and invalidation is easier.

The problem with memcached is that I want to access the data sequentially, page by page. I can't seem to find a feasable way to store an ordered list (without really bad insert performance).

mysql in-memory table as a cache?

Related Stories