MySQL Forums
Forum List  »  Optimizer & Parser

Re: Optimize Query with multiple joins
Posted by: Rick James
Date: March 29, 2011 08:28AM

query_cache_size | 268435456 -- Too big. Purging this could cause the sluggishness. Recommend under 50M

| Qcache_hits | 8573058 |
| Qcache_inserts | 3479315 |
-- Poor usage of the QC.

Plan A:
* query_cache_type = DEMAND
* lower query_cache_size
* explicitly add SQL_CACHE or SQL_NO_CACHE to each SELECT, based on whether the SELECT is likely to be repeated _exactly_, and soon.

Plan B: query_cache_type = OFF.

Qcache_lowmem_prunes | 1161019 -- divide that by Uptime to see how often it is pruning the QC. I suspect the number is too high.

"inserts are rare" --
The final proof that the QC is at fault for 1.43s would be to discover that an INSERT happened at that time.

Options: ReplyQuote

Written By
Re: Optimize Query with multiple joins
March 29, 2011 08:28AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.