MySQL Forums
Forum List  »  Performance

Query Cache Causing High CPU Load
Posted by: Jeremy Bell
Date: February 08, 2011 06:21AM

We've recently moved our database to a high memory server where there's plenty of memory to go around for buffers, connections, cache, etc.

I noticed tonight, that with a relatively small amount of people online that the database was running a tad sluggish. The server load was around 1.5 - 2.5 on a dual core. I loaded up the normal SHOW FULL PROCESSLIST; and saw that there were alot of "Waiting for query cache lock" processes running. This was fairly consistent as I refreshed the list over a few minutes.

The allowed size of the query cache is 2G. I looked at the server status variables and saw that actual cache memory usage was around 1.1G. I wondered if the cache was simply so big that it was taking extra long to lock and update things there. So I reduced the size of the cache to 256M using SET GLOBAL query_cache_size = 268435456; From what I can tell looking at the status variables, this completely clears the cache and starts again (rather than pruning). But even though there was nothing in the cache, as queries were being added to the cache again, the server load dropped to 0.05 - 0.2 (Yes, that low). Everything was speedy again.

I just wonder if there's something awry here. Or is this expected/known behaviour? I don't see any documentation along the lines of "Don't set the cache size too high or it could slow things down rather than speeding things up".

Does anyone have any thoughts on this? Have you seen this before?

We're running MySQL Community Server 5.5.8

Options: ReplyQuote

Written By
Query Cache Causing High CPU Load
February 08, 2011 06:21AM
February 09, 2011 10:53AM
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.