PHP ,MySQL memory DB and memcached

Question

I've a site which is running in a shared host without memcached. So, how about make a MySQL memory DB as object cache just like memcached?

Answer 1

I would say that if you operate a website that would need memcached, you shouldn't be running it on a shared host.

That's my flippant answer. Here's a real answer:

Memcached has some good advantages over the MEMORY storage engine.

• Storage is distributed over multiple servers. MEMORY storage engine is limited to a single host, and constrained by the CPU and memory of that host.
• Quick access of individual entries. MEMORY storage engine has table-level locking only, so concurrency suffers.
• Non-relational key/value storage. MEMORY storage engine is more structured, which isn't as useful for cache-type usage. Also MEMORY expands varchar to full length, so is less efficient storage.

As a caching solution, I wouldn't choose the MySQL MEMORY storage engine. Since you're using PHP, you should be using APC or Xcache or something. These have data cache features that are better for typical usage in PHP.

If you're not using one of these PHP caching technologies, that's a more important area to improve than worrying about memcached versus MEMORY storage engine.

Answer 2

I agree with Bill and you wouldn't get the same performance boost using MySQL that way.

Hypothetically:

Make sure your host has the memory storage engine enabled.

SHOW ENGINES;

This will show you a list of and status of all the storage engines installed in your mysql instance.

Memcache allows you to store data of any length. Internally, its checking your data to see how long it is, and placing it in a pre-allocated memory bucket which is closest to the size of the item your storing. That part of the logic, you'll need to implement yourself. MySQL's memory storage engine only accepts fixed length rows. To get a similar effect in mysql, you'd need to create several in memory tables with different lengths of char fields to store the data. http://dev.mysql.com/doc/refman/5.0/en/memory-storage-engine.html

Selecting the tables with a union between all your 'buckets' would yield the correct value no matter which table the key was actually stored in.

One gotcha with mysql, if you store data in a field that is longer than the field (18 chars in a 16 char field for example), mysql will truncate it without any kind of error.

Here is a better idea: Use Zend_Cache with the 'file' backend. This will store stuff just like memcache on the webservers local disk. Connecting to a db in a shared hosting is usually expensive. (1 to 2 seconds for the first connection on my godaddy accounts.) Even a slow disk is faster than that. http://framework.zend.com/manual/en/zend.cache.html