mysql in-memory table as a cache? | Hacker News Reader

mysql in-memory table as a cache?

4 pointsjohnrob9 comments

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.

View on HN

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.

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.

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.

Do you know the number of the patent for this?

No idea. However, it specifically affected B-trees in memory. A search revealed US Patent 5283894: Lockless concurrent B-tree index meta access method for cached nodes ( http://www.patentmonkey.com/PM/PatentID/5283894.aspx ) which may or may not be relevant.

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?

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?

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).

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).

Reply on news.ycombinator.com