MySQL Query Cache: Yes or No for large website building platform

Question

Wading through so much information and conflicting advice on MySQL's query cache can be really overwhelming and hard to tell how it pertains to my use case.

We have a large custom platform running on a LAMP stack that hosts many websites. Each website has its content stored in a table row. For the most part they're all in the same table. I've read that all cached queries are invalidated whenever a table is updated, but I've also read conflicting things about it.

Say somebody visits Website A and its content is loaded from the database and cached in the process. Another person visits right after and the site loads faster for them because the data were cached. Now Website B's content is changed, which is a row in the same table as Website A. Is all the cached data from Website A now invalidated? And if so, would we actually see a performance increase by turning off the query cache entirely?

I've been reading up on tuning the query cache and again, very overwhelming. I tried a few things but it's hard to tell how much of an effect they had. Here is a current paste from the MySQLTunerscript:

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.62-cll
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 4G (Tables: 1977)
[--] Data in InnoDB tables: 384K (Tables: 16)
[!!] Total fragmented tables: 33

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 5d 1h 30m 33s (53M q [122.486 qps], 1M conn, TX: 125B, RX: 13B)
[--] Reads / Writes: 83% / 17%
[--] Total buffers: 8.1G global + 5.5M per thread (500 max threads)
[OK] Maximum possible memory usage: 10.8G (46% of installed RAM)
[OK] Slow queries: 0% (2K/53M)
[OK] Highest usage of available connections: 5% (28/500)
[OK] Key buffer size / total MyISAM indexes: 8.0G/1.2G
[OK] Key buffer hit rate: 99.7% (1B cached / 3M reads)
[!!] Query cache efficiency: 16.2% (6M cached / 41M selects)
[!!] Query cache prunes per day: 2869188
[OK] Sorts requiring temporary tables: 0% (11 temp sorts / 609K sorts)
[OK] Temporary tables created on disk: 0% (11K on disk / 2M total)
[OK] Thread cache hit rate: 99% (28 created / 1M connections)
[!!] Table cache hit rate: 1% (1K open / 88K opened)
[OK] Open file limit used: 3% (2K/65K)
[OK] Table locks acquired immediately: 99% (41M immediate / 41M locks)
[OK] InnoDB data size / buffer pool: 384.0K/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    query_cache_limit (> 6M, or use smaller result sets)
    query_cache_size (> 96M)
    table_cache (> 1024)

Thanks in advance for any advice.

Answer 1

The answer would be very yes.

It is invalidated, and it's completely transparent unless you disable query_cache_wlock_invalidate which you may want to check out because it could still be worth disabling if you use MyISAM tables.

Yet it's saving 16% of your read queries and solving them immediately without bothering the storage engine, which in the case of MyISAM most often means without bothering the I/O system too. This is great enough! As a matter of fact the query cache is about the best thing to do with your RAM for caching, so set the query cache to something like 128 MB, and after some normal/heavy operation, SHOW GLOBAL STATUS LIKE '%qcache%' shows a low or high Qcache_free_memory: if it's low, increase your query cache, even at the expense of other database caches such as the InnoDB buffer pool; if it's high, reduce your query cache by almost that much because unfortunately you won't get to use more of it in your work set.

Answer 2

Last time I was looking at query cache it was a definite no-no to me. While adding ability to cache results it also slows MySQL down a bit as it takes time to invalidate cache entries. I wouldn't recommend turning it on explicitly unless you test your workload against it (as manual suggests: http://dev.mysql.com/doc/refman/5.1/en/query-cache.html).

Good way for testing it would be snapshotting database and capturing the following 1 hour of requests. This way you can setup another server and run all test there without heavily loading your production machine.