MySQL Query Cache

[PaulKroll]

The MySQL query cache appears to be off, here (well, it's query_cache_type ON, but query_cache_size 0, so it's on but not really). Is there a technical reason why? I'm expecting that's a yes obviously, so what IS that technical reason? Is the number of connections/extra memory to go to the cache not worth it? Is it suspected that the cache wouldn't have many hits? (It appears to be getting about a 35% hit rate at a site I'm running for work, but then, that's a fairly specific kind of sites/type of traffic, and I certainly wouldn't expect that for the range of diverse sites you've got pounding the servers here.)

Or is the qcache functionality not trusted for some reason, yet?

Inquiring minds want to know!

[Terra]

Hrrrm, I don't know why Arthur set the query_cache_size to 0... I will need to defer this until he comes in after the holiday since he is the lead MySQL engineer...

--
Terra
--delegation--
FutureQuest

[Arthur]

The query cache being off was basically a misconfiguration and not a conscious decision. Somewhere between upgrades the settings were lost and it reverted to its default value (which is off).

The cache has been turned back on and it has been added to our MySQL monitor. I don't have any meaningful statistics for you at the moment, but the number of cache hits so far is significantly higher than the number of misses.

Which numbers did you base your hit rate of 35% on? There are several methods you could use.

Arthur

[PaulKroll]

On the server at work, of 111K SELECT queries for a given period, there were 40K or so qcache hits.
EDIT: That's using PHPAdmin to show the "Status" page, FYI, including numbers of various queries.

the number of cache hits so far is significantly higher than the number of misses
While I'd love for that to be true... does that make a lot of sense? I mean, my understanding is:
Query cache only caches SELECTS
A particular cached query is cleared/marked invalid anytime one of the component tables is INSERTed/UPDATEd/DELETEd or ALTERed.
For that quote to be in line with what I'm looking at, you'd have to be seeing qcache hits of more than 50% of the SELECTs.
Now, the number of VBB or phpBB or similar forums might seriously skew the numbers up (and that's just about a dream come true for you folks, I'm thinking, as that's one of the harsher users of the db servers), but significantly higher than misses? I mean, that seems too good to be true.

[Arthur]

For that quote to be in line with what I'm looking at, you'd have to be seeing qcache hits of more than 50% of the SELECTs. That is definitely what I'm seeing. Over a 24 hour period (a slow day, because of Independence Day), the qcache hit rate ranges from 52% to 67.5% (average is 62.3%) of the total number of SELECTs (Com_select + Qcache_hits).

Forums do make up a large part of all traffic and there's a lot of other static data with repetitive queries that would account for the numbers.
When comparing the cache_hits against the total number of questions, the percentages range from 32 to 51% (42.8% average).

Arthur