MySQL Forums
Forum List  »  Performance

Re: Query Cache Causing High CPU Load
Posted by: Rick James
Date: February 09, 2011 10:53AM

> size of the query cache is 2G
Say no more.
Your case is an excellent example of how the QC does not scale well with current computers.

1. If you don't have query_cache_type = OFF (not just DEMAND), _every_ SELECT has to take out an exclusive lock on the QC. Most notably, this can lead to multi-core machines stalling.

2. When _any_ change (INSERT/UPDATE/...) occurs to a table _all_ entries in the QC that mention that table must be purged from the QC. The bigger the QC, the longer this takes. It can take a long time to scan 2GB checking for entries involving a table.

3. The space freed up by shrinking the QC can be given to key_buffer_size (if using MyISAM) and/or innodb_buffer_pool_size (if using InnoDB). They do not suffer from scaling problems.

Recommendation:
For systems with a lot of writes, turn OFF the QC.
If, on the other hand, you really think the QC is helpful, use it in DEMAND mode and use SQL_CACHE / SQL_NO_CACHE on each SELECT to control which queries take up space in the QC. And set query_cache_size to no more than 100M. (That may even be too large.)

Options: ReplyQuote


Subject
Views
Written By
Posted
8512
February 08, 2011 06:21AM
Re: Query Cache Causing High CPU Load
6401
February 09, 2011 10:53AM
2841
February 09, 2011 10:32PM


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.