How do I save and reload MySQL Query Cache to Keep Site Running at Peak Speed

Question

I am running a Magento e-commerce installation (which runs on PHP/MySQL) on a testing server. I have a fairly large (from what I've read) query_cache_size of 272,629,760 bytes and it works great.

The site runs lightning fast once most of the queries are loaded into the query cache, as fast as the fastest production sites (other than perhaps Google.com or Amazon.com). But the problem I have is that in order to load all of those queries into the query cache I have to manually click through 100's of links on the site. Every time you click a link, a query is sent to the database and saved in the cache. But if I restart the server, then I have to do that all over again. There must be a better way!

Ideally, I think there should just be a way to "backup" the query cache before restart and load it upon restart. Is this possible?

Otherwise, i'm just going to have to design a web crawler that automatically clicks all my links.

Answer 1

Aside from MySQL's query cache, you should be using APC (Alternative PHP Cache) as well, and possibly some of Magento's built-in full-page caching mechanisms.

This could mitigate some of your round-trips to MySQL and back, lessening your need for a primed MySQL cache. You can 'prime' the cache of your Magento DB manually, but that's hiding the real issue here, and that's to avoid going to MySQL at all - especially within Magento.

And for what it's worth - you're the first person I've ever come across that compared Magento's speed with that of Google and Yahoo. :)

Answer 2

This question may be better suited to serverfault, but it's interesting, so here goes :)

Magento mostly uses InnoDB tables, so take that into consideration as you read this and this

I would also recommend APC per @philwinkle's suggestion, that's made a huge difference to the performance for me. Ditto to using a CDN to take off the load of serving small files (img/js/css)