

mysql in-memory table as a cache? - johnrob

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.<p>I'm just curious if anyone has used this approach before.
======
bmatheny
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.

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

~~~
xirium
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.

~~~
bayareaguy
Do you know the number of the patent for this?

~~~
xirium
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.

------
st3fan
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
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?

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

