Last week I was having horrible issues with write speeds on my server. Amazingly, I think I've narrowed it down to my query_cache_size just being far too big. I set it to 2GB, and it looks like it was storing so much information that it couldn't sort through it all fast enough when writing, to put it in simple terms anyway.
My questions is this: is there really that big of a benefit to enabling the query_cache in MySQL if I'm already running memcache? It seems as though the MySQL query cache is going to invalidate the queries at a pretty fast pace since there's a lot of writing to the DB and constant changed, whereas memcache can store the data through table changes.
Am I just using up memory pointlessly here? I've turn my query_cache_size down to 128MB, and I am getting low_mem_prunes still, but I think the sacrifice is probably worth it for the faster write speeds. I'm thinking that the write speeds would be even faster with no query_cache enabled, and memcache could still handle the caching of queries.
But I'm not sure that makes any sense at all either, as I'm very inexperienced in this matter.
Comments
It's very much worth it.
It's very much worth it. Memcache doesn't cache all queries, so the ones that don't get stored in memcache will see quiet a bit of help from the query cache. Likewise, when memcache objects need to refresh, the time for that would be quicker with query cache. Things like nodes, taxonomy and users don't get stored in a default memcache installation (there are core patches that will do this), so you will see a benefit from that.
HollyIT - Grab the Netbeans Drupal Development Tool at GitHub.
You're right, I hadn't really
You're right, I hadn't really though out caching non-cache table queries like users and taxonomy terms. Makes sense now. Thanks.
Mysql tuning is indeed a dark
Mysql tuning is indeed a dark art. Good catch on the query_cache being too large.
http://blogs.sun.com/dlutz/entry/mysql_query_cache_sizing
http://www.mysqlperformanceblog.com/2007/03/23/beware-large-query_cache-...
More information about the MySQL Query Cache
The query case is a brute force caching mechanism. There is a line when it becomes unproductive, and when it affects performance.
As pointed out 2G is way to large. 64M-128M are more reasonable numbers when the QC is productive.
What makes QC productive.
* High Read Ratio (e.g. Com_Select v Com_Insert/Update/Delete etc)
* Deterministic queries (e.g. '2010-05-29' instead of CURDATE() or NOW())
* MySQL Version. Older versions had more serious lack of performance in invalidations.
As pointed out one of the reasons why you were seeking a problem is the time it takes to invalidate the QC queries.
The QC stores the tcp packet results of a query based on a hash, which includes items such as the actual SQL statement, protocol version, character set, etc. When you look at the performance it's like 4 steps internally then 20 for even a simple query (SHOW PROFILE) is a good tool to understand why.
As soon as you make one modification to a table, every QC query that uses that table is flushed. So in a mixed read/write environment, every time you add or change a record, the QC is flushed of said table, then 100 reads per second come in, are cached, only for a write 1 second later to flush them all.
You really need to look at the QC ratio of usage to determine it's ultimate effect. You also need to consider that sometimes, more complex selects that use joins are better written differently is you want to use the QC.
Another trick is determine when to use the QC. You can dynamically turn it on and off, so in high peak writes you may elect to disabled it for a time, and re-enable later. Again detailed monitoring and analysis is needed to determine the best usage.
There is a lot more, I've only touched on few points. There is also a lot of recent blog posts about the QC from the MySQL community I can dig up if you want them. The 2007 article is pre bug that fixed a serious QC invalidation performance problem.
It can be a great tool you get for free, it can also kill you in performance, like 20% if ineffective.
Ronald Bradford
MySQL Expert
Well there is no doubt that
Well there is no doubt that this was a major learning experience for me.
Everything I've read says "more RAM, more RAM!" for MySQL. The tuning tools I've been using all say I should give my QC more RAM to avoid low mem prunes.
So I did.
In reality, I had both my QC size and my QC limit set way too high. I was allowing MySQL to cache anything up to 20M in size, and had a 2Gb query cache. The effect was disastrous. I was running at up to 91% CPU with as few as 30 apache connections on my server.
After lowering the QC to 128Mb, and the QV limit to 1M, I'm running about 10% cpu utilizations with 100+ connections.
And those were the only changes I made. So the proof is in the pudding.
Rookies that are just being thrown into the fire like myself take heed, query cache can be your friend, or it can be your worst enemy.
Where does one set up the query cache in Drupal??
Trying to find the answer to this question I only found this "recent" trhead and the following encouraging information:
"The MySQL query cache can improve performance of your site by storing the result of queries. Then, if an identical query is received later, the MySQL server retrieves the result from the query cache rather than parsing and executing the statement again."
But nothing explaining where to go to to set this up in Drupal. In Admin I only find "Imagecache" and "Jquery" but no combination of them both.
Suggestions?
pd I have been looking so long to find something, now I don't even remember what for anymore...
You don't enable the MySQL
You don't enable the MySQL query cache in Drupal. That's all set through the actual MySQL server. Here's a page explaining the variables and what they mean:
http://www.howtogeek.com/howto/programming/speed-up-your-web-site-with-m...
HollyIT - Grab the Netbeans Drupal Development Tool at GitHub.