| Bug #38551 | query cache can still consume [very little] cpu time even when it is off. | ||
|---|---|---|---|
| Submitted: | 5 Aug 2008 0:08 | Modified: | 1 Dec 2009 17:07 |
| Reporter: | Neel Nadgir | ||
| Status: | Closed | ||
| Category: | Server: Query Cache | Severity: | S5 (Performance) |
| Version: | 5.1, 6.0 | OS: | Any |
| Assigned to: | Kristofer Pettersson | Target Version: | 6.0-rc |
| Tags: | performance | ||
| Triage: | Triaged: D3 (Medium) | ||
[5 Aug 2008 0:08]
Neel Nadgir
[7 Aug 2008 8:09]
Susanne Ebrecht
Bug #38207 seems to be related to this bug here.
[8 Sep 2008 13:30]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/53515 2735 Kristofer Pettersson 2008-09-08 Bug#38551 query cache can still consume [very little] cpu time even when it is off. When the query cache size is zero, all query cache functionallity should be skipped. Since query_cache_size is protected from any changes by the barrier in Query_cache::resize() we can safely add a test at the begining of the invalidate* functions to avoid having mutexes locked when the query cache is ment to be disabled.
[10 Sep 2008 8:35]
Kristofer Pettersson
..of course to be fully protected we can't by pass the QC in case of a flush by must hold.
[9 Oct 2008 13:04]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/55912 2768 Kristofer Pettersson 2008-10-09 Bug#38551 query cache can still consume [very little] cpu time even when it is off. When the query cache size is zero, all query cache functionallity should be skipped. By checking query_cache_size before attempting to invalidate a table or db we avoid grabbing the query cache mutex. If the rare cases the query cache size would change so we get a false read the cache has either been cleared or
[13 Oct 2008 14:08]
Kristofer Pettersson
progress report: There is a confusion about the visibility and atomicity which can be expected. This makes it difficult to make a good decision without showing the concrete performance consequences to each decision. Currently nobody wants: 1) a blocking mutex 2) a client barrier on a write. 3) Only be able to disable query cache at compile time/startup time. What is left to investigate is if it really is possible to read query_cache_size in a concurrent environment and if the rare but occasional lack of consistence when the query_cache_size change is an issue for the overall system performance.
[16 Oct 2008 22:03]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/56418 2666 Kristofer Pettersson 2008-10-16 Bug#38551 query cache can still consume [very little] cpu time even when it is off. When the query cache is disabled, the server shouldn't attempt to take the query cache mutex. By using the command line option --query_cache_type=0, the user can disable the query cache permanently and avoid taking the query cache mutex.
[27 Oct 2008 17:38]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/57125 2742 Kristofer Pettersson 2008-10-27 Bug#38551 query cache can still consume [very little] cpu time even when it is off. When the query cache is disabled, the server shouldn't attempt to take the query cache mutex. By using the command line option --query_cache_type=0, the user can disable the query cache permanently and avoid taking the query cache mutex.
[27 Oct 2008 17:42]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/57127 2743 Kristofer Pettersson 2008-10-27 Bug#38551 query cache can still consume [very little] cpu time even when it is off. Adding additional test case.
[28 Oct 2008 13:14]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/57199 2742 Kristofer Pettersson 2008-10-28 Bug#38551 query cache can still consume [very little] cpu time even when it is off. When the query cache is disabled, the server shouldn't attempt to take the query cache mutex. By using the command line option --query_cache_type=0, the user can disable the query cache permanently and avoid taking the query cache mutex.
[29 Oct 2008 13:23]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/57302 2742 Kristofer Pettersson 2008-10-29 Bug#38551 query cache can still consume [very little] cpu time even when it is off. When the query cache is disabled, the server shouldn't attempt to take the query cache mutex. By using the command line option --query_cache_type=0, the user can disable the query cache permanently and avoid taking the query cache mutex.
[29 Oct 2008 17:19]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/57327 2742 Kristofer Pettersson 2008-10-29 Bug#38551 query cache can still consume [very little] cpu time even when it is off. When the query cache is disabled, the server shouldn't attempt to take the query cache mutex. By using the command line option --query_cache_type=0, the user can disable the query cache permanently and avoid taking the query cache mutex.
[7 Nov 2008 14:53]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/58178 2917 Kristofer Pettersson 2008-11-07 Bug#38551 query cache can still consume [very little] cpu time even when it is off. When the query cache is disabled, the server shouldn't attempt to take the query cache mutex. By using the command line option --query_cache_type=0, the user can disable the query cache permanently and avoid taking the query cache mutex.
[10 Nov 2008 11:53]
Bugs System
Pushed into 6.0.9-alpha (revid:kristofer.pettersson@sun.com-20081107140159-6unylckzptcal73g) (version source revid:kristofer.pettersson@sun.com-20081107140159-6unylckzptcal73g) (pib:5)
[12 Nov 2008 1:13]
Paul DuBois
Noted in 6.0.9 changelog. The server unnecessarily acquired a query cache mutex even with the query cache disabled, resulting in a small performance decrement.
[15 Jul 2009 23:34]
Mark Callaghan
This would be easy to backport to 5.0. Are there plans for that?
[30 Jul 2009 20:08]
Jeremy Zawodny
I'd like an answer to Mark's question too. What's the backport plan for this?
[4 Aug 2009 19:39]
Davi Arnaut
At this time there is no plan to backport the fix to a stable release as it changes the behavior of the query_cache_type command line option. Also, if query cache is not used at all, one can build (or request a build) with query cache disabled (see --without-query-cache).
[16 Sep 2009 8:45]
Bugs System
Pushed into 5.4.4-alpha (revid:alik@sun.com-20090916063112-8hjmu6wkxfx5qxf4) (version source revid:guilhem@mysql.com-20090708213845-36vjraclcpz7mwlq) (merge vers: 5.4.4-alpha) (pib:11)
[27 Oct 2009 20:32]
Paul DuBois
Updated changelog entry: The server unnecessarily acquired a query cache mutex even with the query cache disabled, resulting in a small performance decrement. Now if the server is started with query_cache_type set to 0, it does not acquire the query cache mutex. This has the implication that the query cache cannot be enabled at runtime.
[29 Oct 2009 12:09]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/88574 2900 Kristofer Pettersson 2009-10-29 Bug#38551 query cache can still consume [very little] cpu time even when it is off. When the query cache is disabled, the server shouldn't attempt to take the query cache mutex. By using the command line option --query_cache_type=0, the user can disable (backport from mysql-pe) @ mysql-test/t/query_cache_disabled-master.opt * added test case for bug38551 @ mysql-test/t/query_cache_disabled.test * added test case for bug38551 @ sql/set_var.cc * Added before-trigger to verify that query_cache_type wasn't turned off or on during runtime. @ sql/set_var.h * Changed order on how the enumeration is processed. By first projecting the character representation of the variable to a temporary integer we can have one function instead of two to check if the value is valid. @ sql/share/errmsg-utf8.txt * Added error message for query cache disabled state @ sql/sql_cache.cc * If the query cache is disabled at start up, shorten the execution path and avoid grabbing the query cache mutex each time the invalidate interface methods are called. @ sql/sql_cache.h * Added new methods to set the query cache into a disabled state.
[3 Nov 2009 8:17]
Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091102151658-j9o4wgro47m5v84d) (version source revid:alik@sun.com-20091102151658-j9o4wgro47m5v84d) (merge vers: 6.0.14-alpha) (pib:13)
[3 Nov 2009 16:34]
Paul DuBois
Already fixed in earlier 6.0.x release.
[12 Nov 2009 9:16]
Bugs System
Pushed into 5.5.0-beta (revid:alik@sun.com-20091110093229-0bh5hix780cyeicl) (version source revid:mikael@mysql.com-20091103113702-p61dlwc6ml6fxg18) (merge vers: 5.5.0-beta) (pib:13)
[12 Nov 2009 21:38]
Paul DuBois
Noted in 5.5.0 changelog.
[1 Dec 2009 4:36]
James Day
Paul, please update the release note to: The server unnecessarily acquired a query cache mutex even with the query cache disabled, resulting in a small performance decrement which could show up as threads often in state "invalidating query cache entries (table)", particularly on a replication slave with row-based replication. Now if the server is started with query_cache_type set to 0, it does not acquire the query cache mutex. This has the implication that the query cache cannot be enabled at runtime. This is to note where we're commonly seeing customers encountering this issue and to give them an explicit clue that this fix will solve their problem. See bug #38207 for the symptom I've described being associated with this bug. Please also add the performance tag for this fix, because of the impact on CPU use for RBR.
[1 Dec 2009 17:07]
Paul DuBois
James, done, thanks.