October 1, 2014

Return of the Query Cache, win a Percona Live ticket

It’s Friday again, and time for another TGIF give-away of a Percona Live London ticket! But first, what’s new with the MySQL query cache? You may know that it still has the same fundamental architecture that it’s always had, and that this can cause scalability problems and locking, but there have been some important changes recently. Let’s take a look at those.

The first important change is that both Percona and Oracle actually built some code improvements into the query cache and the interface between it and MySQL. It’s now possible to completely disable it, for example. This used to be possible only by eliminating it at compile time. If you didn’t do that, then there was still a query-cache single choke-point in the server. Now that’s gone. As of MySQL 5.5, the query cache mutex isn’t hit at all if query_cache_type is zero. We made some related changes in Percona Server 5.1 a while ago. I don’t recall the differences between Oracle’s changes and ours, but theirs was better than ours. When they released this fix it obsoleted ours, and we didn’t port our fix forward to Percona Server 5.5, and instead backported the 5.5 fix to our 5.1 branch and replaced our fix. There are some other query cache improvements in MySQL 5.5 as well, but on big hardware with a write-heavy workload that doesn’t benefit from the query cache, the only possible improvement is to disable the cache completely, and people don’t care beyond that. Here’s my favorite query cache tuning guide.

The second important change is really minor. It didn’t improve anything in the server’s performance, but it improved transparency to the user. This is a feature that we introduced, also later superceded by Oracle, that changes the thread’s status in SHOW PROCESSLIST to “Waiting on query cache mutex” when the query cache mutex is taken. This makes it really obvious when the query cache is a bottleneck. Oracle released a similar change pretty soon afterwards, but theirs changed the wording to “Waiting on query cache lock” instead. This is more intuitive for non-programmers anyway. Regardless, the end effect is the same thing: just as you used to be able to see that you had MyISAM problems really easily when you had a screen full of threads in Locked status, now you get a screen full of query cache locks. It makes it impossible to miss serious contention when it happens. For example, it alerted a user on our forum to the fact that the query cache was configured far too large. This might have taken a lot longer to discover otherwise.

And now the moment you’ve been waiting for: round two of Percona’s TGIF contest is underway! Watch our @Percona Twitter stream and retweet the contest to win a free ticket to Percona Live London on October 24-25! We’ll pick a random retweet and give away a free ticket each week. If you don’t win this time, try next Friday or register and get the early-bird discount (but don’t wait too long: it expires September 18th). Our tutorial schedule is 100% complete at this time; don’t miss your opportunity to learn subjects from NDB Cluster to InnoDB Architecture and Performance Optimization. These detailed presentations will give you the hands-on experience you need to take your MySQL understanding to the next level.

About Baron Schwartz

Baron is the lead author of High Performance MySQL.
He is a former Percona employee.

Comments

  1. Davi Arnaut says:

    > As of MySQL 5.5, the query cache mutex isn’t hit at all if query_cache_size is zero.

    I think you mean query_cache_type.

    > [...] but I believe they improved upon our technique.

    Don’t think so, see pad.lv/609027

  2. Davi,

    When I said “improved upon our technique” that was ambiguous. I’m sorry, I didn’t mean that Oracle took our change and improved it. I mean that our fix wasn’t as good as Oracle’s.

    As for query_cache_size versus query_cache_type, you are right, I switched them. I’ll update the text of the blog. Thanks!

  3. I updated that section of the text in general in this blog post. It was pretty sloppy and needed to be rewritten to reflect history accurately. I’m sorry for the confusion and the wrongly implied relationships between Percona’s work and Oracle’s.

  4. Davi Arnaut says:

    Thanks. It’s not a problem at all. I think it looks and reads better now than before.

Speak Your Mind

*