This week in Support was busy enough for me. Among other things I had to study all possible reasons (other than obvious query cache impact) for queries hanging in "query end" status and noted Bug #80652 related to binlog group commit and fixed in MySQL 5.7.17+ and 8.0.1+ only. The case I had to review was related to Galera though, and I suggest you to note that "query end" may be related to Galera replication stall. Studying this path further soon brought lp:1197771 - "Cluster stalls while distributing transaction" to my attention again, so I asked about proper status for it on Facebook. As it happens way too often recently, I've got few 'Likes" but no further comments, neither …[Read more]
10 Older Entries »
This blog post was motivated by an internal discussion about how to fully disable query cache in MySQL.
According to the manual, we should be able to disable “Query Cache” on the fly by changing
to 0, but as we will show this is not fully true. This blog will show you how to properly disable “query cache,” and how common practices might not be as good as we think.
Can we just disable it by changing variables, or does it requires a restart to avoid the global mutex? Let’s see how it works.
Some Query Cache context
The query cache stores the text of a “Select” statement together with the corresponding result that was sent to the client. If an identical statement is received later, the server retrieves the results …[Read more]
There are a lot of blog posts on the internet which warn you about using the Query Cache in MySQL.
I was surprised to see that the query cache was enabled in Aurora.
This was the size on a ‘db.r3.large’ instance.
On a ‘db.r3.2xlarge’ instance, it was set to 2460900352 i.e. 2.4GB
I am not sure, if amazon has done something to improve the query cache.
So, do run tests with Aurora and see if the cache suits you.
When you make a change to your MySQL configuration in production it would be great to know the impact (a “before and after” type of picture). Some changes are obvious. For many variables proper values can be determined beforehand, i.e. innodb_buffer_pool_size or innodb_log_file_size. However, there is 1 configuration variable which is much less obvious for many people working with MySQL: query_cache.
The idea of query cache is great, however, there are a lot of issues with MySQL query …[Read more]
We’ve known for over six years (since before we started Drizzle) that the query cache hurt performance. It was for that reason that the query cache was one of the early things to be removed from Drizzle, it just didn’t scale on multi core systems that we were targeting.
So what about modern hardware? While working on MySQL 5.6 on POWER8, I enabled the query cache and ran a benchmark. …[Read more]
Thanks to all who attended my “MySQL Query Tuning” webinar on July 24. If you missed it, you can you can download the slides and also watch the recorded video. Thank you for the excellent questions after the webinar as well. Query tuning is a big topic and, due to the limited time, I had to skip some material, especially some of the monitoring. I would like, however, to answer all the questions I did not get into during the webinar session.
Q: Did you reset the query cache before doing your benchmark on your query? 0.00 seconds sounds too good
A: (This is in response to a …[Read more]
While most of the talk recently has mostly been around the new changes in MySQL 5.6 (and that is understandable), I have had lately some very interesting cases to deal with, with respect to the Metadata Locking related changes that were introduced in MySQL 5.5.3. It appears that the implications of Metadata Locking have not been covered well, and since there are still a large number of MySQL 5.0 and 5.1 installations that would upgrade or are in the process of upgrading to MySQL 5.5, I thought it necessary to discuss what these implications exactly are.
To read what Metadata Locking exactly is please read this section here in the MySQL manual.
Let’s start off with having a look at the Meta Data Locking behavior prior to MySQL 5.5.3
Metadata Locking behavior prior to MySQL 5.5.3
Prior to MySQL 5.5.3 a statement that opened a …[Read more]
Like others we were not satisfied with the fix for a bug in MySQL which caused the query cache and partitioning to not work reliably together. The bug, in simple terms, was that if the query cache was enabled and you used partitioned tables and if a partitioned table was using a transactional engine like InnoDB or XtraDB, the query cache could, under certain circumstances, return incorrect results.
Returning incorrect results is a definite, high-priority bug. However, the upstream fix was to disable all caching of queries from …[Read more]
A customer opened an issue recently to ask why the query cache wasn't working after he upgraded to MySQL 5.5.25. The reason really ended up surprising me.
As of MySQL 5.5.23, the Query Cache is disabled for partitioned tables!
This is a "fix" for bug #53775.
At first I thought perhaps the fix for the bug had resulted in the query cache being inadvertently disabled for partitioned tables, but the comments that go along with the commit make it pretty clear that disabling the query cache was the intended "fix". You can review the commit message and the code changed at revision 2661.803.1 in the MySQL Server 5.5 repository.
I recently noticed both MariaDB and XtraDB (not MySQL yet) have a (newer) variable query_cache_strip_comments.
This variable is great for those who want to append comments to various queries, but still want the query cache to be able to serve such queries. Unfortunately, with MySQL, this is not currently possible.
In the past, I wrote a post on using MySQL Proxy which described a technique of monitoring queries through the proxy by appending IP addresses to the queries so one could track where they originated from. However, one pitfall to that was the MySQL query cache *does not* ignore the comment and treats them all as different queries (see the user comments for further discussion). (I did subsequently enhance that functionality implementing the …[Read more]
10 Older Entries »