MySQL Forums
Forum List  »  MyISAM

Re: Randomly slow Insert / Update on small table
Posted by: Sebastian Hamann
Date: November 04, 2009 01:49AM

Hey Rick,

thanks for your in depth explanation, they do shed good light and spawn further ideas! We'll definitly want to reduce the number of logs, I'll bring this right to the top when doing our next DB maintenance!

Good news now: I think we found the culprit, which locks up our DB:

mysql> show variables like 'query_cache%';
+------------------------------+------------+
| Variable_name                | Value      |
+------------------------------+------------+
| query_cache_limit            | 1048576    |
| query_cache_min_res_unit     | 4096       |
| query_cache_size             | 1073741824 |
| query_cache_type             | ON         |
| query_cache_wlock_invalidate | OFF        |
+------------------------------+------------+

We had a query cache of 1GB reserved. While this does not pose a problem per se, it's a problem with our type of usage: we got a massively biased read / write ratio (99%/1%).

What seems to happen, is that our qcache accumulates a massive amout of resultsets, which get invalidated by those infrequently happening updates of the domain table. Which leads to full invalidation of all resultsets in connection to the domain table, which are next to all!

All the surveyed things: low disk IO, high CPU load w/ mysql process, query hangs in "end" query stage point to that. Also that imediate updates after the first one would run smoothly would be explained.

(source: http://www.mysqlperformanceblog.com/2009/03/19/mysql-random-freezes-could-be-the-query-cache)

I did a test this morning, after reducing the query cache to 256mb. End query stage went down to 16.8 secs (17.1 secs overall execution time). Wow this really might be it.

I had also another source (forgot to save the url) which was commented by a mysql developer, who said that the query cache invalidation part of the mysql code base is one of the older ones and is scheduled for overhaul one of these days.

As the qcache efficency isn't skyrocketing (about 50%, which is due to the nature of our queries) I'll probably tune the qcache even further down, trying values of 128mb or 64mb.

Rick, thanks for your help, thanks for any further insights, I hope this is it.

regards,
Sebastian

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Randomly slow Insert / Update on small table
4786
November 04, 2009 01:49AM


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.