[wp-hackers] MySQL MEMORY worth using for an object cache?

[Andy Skelton]

On 4/17/07, Robert Deaton <false.hopes at gmail.com> wrote:
> The advantage of using MySQL is indeed using RAM, which is many
> magnitudes faster than disk based files. The disadvantage, however, is
> that you still have MySQL connection overhead, a limited number of
> connections to the MySQL server, etc. On a server expecting burst
> traffic or just flat out heavy traffic, its worthwhile to use the
> diskbased cache instead to relieve some of the stress from the MySQL
> server.

There is no reason that this should cause additional connections.
Memory tables can live in the same database as other types.

> It is because of this that I'd choose APC or memcached as a persistent
> object cache backend. Probably APC because there is no daemon there
> either, it can be stored straight to RAM and retrieved straight from
> RAM.

I have no personal experience with APC. For any setup that needs to
scale beyond a single web server I would choose memcached. However,
the idea was to replace the disk-based cache with something that (a)
improves performance under load for the majority of installs
(shared/VPS) and (b) can be enabled by default.

Here is the horse's mouth:
http://dev.mysql.com/doc/refman/4.1/en/memory-storage-engine.html

And some highlights from the above:
MEMORY tables use a fixed-length row storage format.
MEMORY tables cannot contain BLOB or TEXT columns.
MEMORY tables are never converted to disk tables. To ensure that you
don't accidentally do anything foolish, you can set the
max_heap_table_size system variable to impose a maximum size on MEMORY
tables. For individual tables, you can also specify a MAX_ROWS table
option in the CREATE TABLE statement.

Even if it were limited to 10-20 rows at 50-100k per row, that
megabyte of RAM might be very useful for caching the ten or twenty
pages that get the most views. Typically that's the front page and a
few posts as rendered sans cookies.

Andy