Caching in PHP?

Question

We run a number of web applications written in PHP. Unfortunately there are some select queries within these with some pretty hefty joins which are causing MySQL to become less responsive.

Because of this we are looking into caching some of the regularly used joins. I have looked into Zend_Cache which looks promising, are there any other alternatives that may perform better?

Also what is the best back end for a cache? I believe Zend_Cache offers file based, Sqlite and Memcached.

Answer 1

When speed is important you should definitely go for memcached. But in most cases a filecache or sqlite cache serves your needs.

Zend_Cache is a very good Caching library, the fact that it support numerous caching mechanisms through it's adapter system makes changing caching solutions lateron a breeze, so I would definitely go for Zend_Cache.

Answer 2

It really depends on exactly what is causing these queries to be slow, and whether the queries are the same ones again, or if they're different every time.

Generally speaking, I'd initially focus on why the queries are slow and improving them. The cache used inside the database is efficient because the same cache entries can be used by different queries (assuming they use the same data).

Don't bother with the MySQL query cache, your database server's ram is better used increasing the innodb buffer pool (assuming you're using innodb). The main difficulty with the query cache is that every entry for that table gets thrown out when a single row in the table is modified - even if the results of that particular query had not changed.

The query cache can significantly harm performance if used incorrectly - but the innodb buffer pool is generally only beneficial.

If your data aren't too big, a cheap and low-risk solution is to buy your db server lots of ram and increase the innodb buffer pool to exceed the size of your data.

Client-side caches will be more scalable (for example, if one per web server) but less effective, and expose stale data to the application (they will probably not be automatically expired when their data become stale).

As with any performance thing, test, test test. Use production-grade hardware, production-like data and workloads.