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

[Robert Deaton]

On 4/17/07, Andy Skelton <skeltoac at gmail.com> wrote:
> 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.

Sorry, I should have worded it better. What I was trying to say is
that, when you're trying to squeeze performance out, the overhead of
connections to the MySQL server is still there when using these sorts
of tables. For a site that's under heavy slashdot or digg traffic, for
example, its not uncommon to hit the mysql connection limit with your
scripts, whereas a diskbased or other non-MySQL method for persistent
object caching does not suffer this limitation.

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

Yes, and for the size of the objects we'd like to cache, this may
hurt. char columns or binary columns can only have a length of up to
255. I guess theoretically we could do splitting in PHP and concat
them back out.

> MEMORY tables cannot contain BLOB or TEXT columns.

I believe that's a given due to the fixed-length row storage :)

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

Ah, caching whole pages? I didn't get that impression from your
original mail. Interesting thought.


-- 
--Robert Deaton
http://lushlab.com