Why not use MySQL like memcached?

Question

For the same reason it makes sense to use both NoSQL and RDBMSs in one application, It makes sense to me to have an RDBMS cache besides NoSQL cache.

I was thinking about using a MySQL server with all tables using the memory engine.

Is there any caveat to this approach?

Just to clarify, I am suggesting here to use a MySQL server for caching purposes only, not for the actual data storage of my application.

bash- · Answer 1 · 2011-07-22T16:10:23.670

Memory tables are stored entirely in memory, so it is very fast.

It uses hash indexes which are also very fast, great for temp table purposes and lookups.

Memory tables have table level locks, so if concurrency is required, this is a problem

No transactions

When server shuts down or crashes ALL ROWS ARE LOST

though table definition stays the same, the data would be all gone.

EDIT:

The Memory Storage Engine is a good candidate for caching purposes.

The following are a few things that the Memory Engine is good for:

Lookup/mapping tables
caching results of periodically added data
for data analysis purposes
Sessions management
Low latency operations
Better than other strategies such as CREATE TEMPORARY TABLE as the Memory table persists (if that is what you need)

There are a few negatives:

It does not support TEXT or BLOB columns, the table would be converted to a MyISAM on disk if such an event happens.
The table should not hold too much data, as it takes up resources that can otherwise be allocated to indexes/query caches

All in all the memory engine should be the best choice for you if you need caching.

isn't all data loast when the memcahd server crashes, too? I suggested the My SQL memory based server as a caching mechanizem, not the actual datastorage of the application — Itay Moav -Malimovka, Jul 22 '11 at 15:30

score 0 · Answer 2 · answered Jul 22 '11 at 13:24

0

another reason,
RAM is much limited compared to disk space,
you can have disk drive up to terabytes,
but hardly can let memory goes up-to terabytes

answered Jul 22 '11 at 13:24

ajreal

In theory using memcache your problem is solved, though cost-efficiently it won't be making your boss happy. – Robert de W Jul 22 '11 at 13:26
ahhh...another reason for what? – Itay Moav -Malimovka Jul 22 '11 at 15:29
that's mean RAM is much expensive than disk, computer science 101? – ajreal Jul 25 '11 at 04:59

score 0 · Answer 3 · answered Jul 22 '11 at 13:40

0

If you got the $$, the following article about MySQL and InnoDB's capabilities will be of high interest.

It outperforms any sort of cache, memory engine or memcached. Drawback is - it requires ram, lots of it.

answered Jul 22 '11 at 13:40

Michael J.V.

3 Answers3