3 ms·

I always assumed that it makes sense to have the query cache active. It just makes sense logically. On my production server, "SHOW STATUS LIKE 'Qcache%'" curre

by TekMol 9y ago

I always assumed that it makes sense to have the query cache active. It just makes sense logically. On my production server, "SHOW STATUS LIKE 'Qcache%'" currently gives me: Qcache_free_blocks 2184 Qcache_free_memory 7629288 Qcache_hits 328939349 Qcache_inserts 14440714 Qcache_lowmem_prunes 7814306 Qcache_not_cached 290027559 Qcache_queries_in_cache 7497 Qcache_total_blocks 17225 About 20 hits per insert. Sounds good to me. Anybody here who is more "in the know" then me and can tell me what I can conclude from these numbers and/or which other stats to look at?

morgo 9y ago
It's not easily possible to tell from these numbers. For example: - You can see a hit, but what was the cost of a miss? If it was a point lookup, it is very low cost. - Comparing hits to inserts, does not show the cost added to every other query to search the cache (Com_select) that may have not been added to the cache after execution due to judged to be non-deterministic. - Inserting results into the query cache can cause stalls (mentioned on Rene's post) - Performance should be judged on 99th+ percentile. Even if it makes some queries faster, has it improved your p99? If you wanted to take a more wholistic approach, it is good to measure this from the Application with something like NewRelic.
- TekMol 9y ago
  > what was the cost of a miss Yeah, no idea. Regarding "Com_select", it seems to be 0: SHOW STATUS LIKE "Com_select" Variable_name Value Com_select 0 What does that mean? As for the wholistic approach .. well, the server tucks away nicely. I never experience any lag and all users are happy with the speed. So there is no strong drive to investigate. Computers are so fast these days. A cheap $15/month VPS can easily handle tens of thousands of users per day.

by TekMol 9y ago

morgo 9y ago

It's not easily possible to tell from these numbers. For example: - You can see a hit, but what was the cost of a miss? If it was a point lookup, it is very low cost. - Comparing hits to inserts, does not show the cost added to every other query to search the cache (Com_select) that may have not been added to the cache after execution due to judged to be non-deterministic. - Inserting results into the query cache can cause stalls (mentioned on Rene's post) - Performance should be judged on 99th+ percentile. Even if it makes some queries faster, has it improved your p99? If you wanted to take a more wholistic approach, it is good to measure this from the Application with something like NewRelic.

TekMol 9y ago
> what was the cost of a miss Yeah, no idea. Regarding "Com_select", it seems to be 0: SHOW STATUS LIKE "Com_select" Variable_name Value Com_select 0 What does that mean? As for the wholistic approach .. well, the server tucks away nicely. I never experience any lag and all users are happy with the speed. So there is no strong drive to investigate. Computers are so fast these days. A cheap $15/month VPS can easily handle tens of thousands of users per day.