News for hackers. Stuff that matters.

mysql in-memory table as a cache?

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.

( 9 comments | 4 points | on HN )
Re:mysql in-memory table as a cache? by bmatheny on Tuesday February 19, 2008 @07:30AM (#118854)
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.

Re:mysql in-memory table as a cache? by bayareaguy on Tuesday February 19, 2008 @02:37AM (#118765)
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.

Re:mysql in-memory table as a cache? by xirium on Tuesday February 19, 2008 @03:27AM (#118791)
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.

Re:mysql in-memory table as a cache? by bayareaguy on Tuesday February 19, 2008 @08:09AM (#118863)
Do you know the number of the patent for this?
Re:mysql in-memory table as a cache? by xirium on Friday February 22, 2008 @03:10PM (#121511)
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.
Re:mysql in-memory table as a cache? by st3fan on Tuesday February 19, 2008 @02:27AM (#118761)
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?
Re:mysql in-memory table as a cache? by jsjenkins168 on Tuesday February 19, 2008 @02:37AM (#118763)
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?
Re:mysql in-memory table as a cache? by aston on Tuesday February 19, 2008 @05:19AM (#118823)
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).
Re:mysql in-memory table as a cache? by johnrob on Tuesday February 19, 2008 @12:21PM (#119020)
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).