| Bug #38551 | query cache can still consume [very little] cpu time even when it is off. | ||
|---|---|---|---|
| Submitted: | 5 Aug 2008 0:08 | Modified: | 12 Nov 21:38 |
| 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 23:34]
Mark Callaghan
This would be easy to backport to 5.0. Are there plans for that?
[30 Jul 20:08]
Jeremy Zawodny
I'd like an answer to Mark's question too. What's the backport plan for this?
[4 Aug 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 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 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 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 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 16:34]
Paul DuBois
Already fixed in earlier 6.0.x release.
[12 Nov 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 21:38]
Paul DuBois
Noted in 5.5.0 changelog.
