| Bug #27531 | Query performance degredation in 4.1.22 and greater | ||
|---|---|---|---|
| Submitted: | 29 Mar 2007 18:00 | Modified: | 11 May 2007 18:54 |
| Reporter: | Todd Farmer (OCA) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 4.1.22 5.0 | OS: | Any |
| Assigned to: | Georgi Kodinov | CPU Architecture: | Any |
| Tags: | bfsm_2007_04_05, bfsm_2007_04_19, DoS | ||
[10 Apr 2007 0:05]
Trudy Pelzer
I'm removing the "regression" tag; it was added under a misapprehension. Note that, without Joro's fix for bug#17212, the relevant query returned the *wrong* result in 0.02 seconds. With the fix, the query now returns the *correct* result, albeit slowly. We cannot consider it a regression if an incorrect result is returned much sooner than the correct result (no fair comparing the speed unless the same result is also returned). Updating the optimizer so that this query will run much faster *and* return the correct result is a larger task; when this will be done still needs to be determined.
[30 Apr 2007 12: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/25759 ChangeSet@1.2442, 2007-04-30 15:22:58+03:00, gkodinov@magare.gmz +3 -0 Bug #27531: When checking for applicability of join cache we must disable its usage only if there is no temp table in use. When a temp table is used we can use join cache (and it will not make the result-set unordered) to fill the temp table. The filesort() operation is then applied to the data in the temp table and hence is not affected by join cache usage. Fixed by narrowing the condition for disabling join cache to exclude the case where temp table is used.
[30 Apr 2007 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/25774 ChangeSet@1.2508, 2007-04-30 17:53:01+03:00, gkodinov@magare.gmz +31 -0 bug #27531: 5.1 part of the fix: - added join cache indication in EXPLAIN (Extra column). - prefer filesort over full scan over index for ORDER BY (because it's faster). - when switching from REF to RANGE because RANGE uses longer key turn off sort on the head table only as the resulting RANGE access is a candidate for join cache and we don't want to disable it by sorting on the first table only.
[4 May 2007 13:43]
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/26097 ChangeSet@1.2653, 2007-05-04 16:43:29+03:00, gkodinov@magare.gmz +3 -0 Bug #27531: the 4.1 fix. When checking for applicability of join cache we must disable its usage only if there is no temp table in use. When a temp table is used we can use join cache (and it will not make the result-set unordered) to fill the temp table. The filesort() operation is then applied to the data in the temp table and hence is not affected by join cache usage. Fixed by narrowing the condition for disabling join cache to exclude the case where temp table is used.
[7 May 2007 18:15]
Bugs System
Pushed into 5.1.18-beta
[10 May 2007 6:44]
Bugs System
Pushed into 4.1.23
[10 May 2007 6:52]
Bugs System
Pushed into 5.0.42
[11 May 2007 18:54]
Paul DuBois
Noted in 4.1.23, 5.0.42, 5.1.18 changelogs. The fix for Bug #17212 provided correct sort order for misordered output of certain queries, but caused significant overall query performance degradation. (Results were correct (good), but returned much more slowly (bad).) The fix also affected performance of queries for which results were correct. The performance degradation has been addressed. Also updated EXPLAIN section in 5.1 manual.
[29 May 2007 14:08]
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/27583 ChangeSet@1.2515, 2007-05-29 15:58:18+03:00, gkodinov@macbook.gmz +31 -0 Bug #27531: 5.1 part of the fix - Renamed "Using join cache" to "Using join buffer". - "Using join buffer" is now printed on the last table that "reads" from the join buffer cache.
[4 Jun 2007 21:21]
Bugs System
Pushed into 5.1.20-beta
Description: In 4.1.22, you have fixed the following bug: * Use of the join cache in favor of an index for ORDER BY operations could cause incorrect result sorting. (Bug#17212: http://bugs.mysql.com/17212) A commit is the following one: http://lists.mysql.com/commits/8916 This truly made sorting correct again , but it had DEVASTATING effects on the performance. See the example in "how to repeat". This is observed on both Windows and Linux platforms. How to repeat: mysql> select version(); +---------------------+ | version() | +---------------------+ | 4.1.21-community-nt | +---------------------+ 1 row in set (0.00 sec) mysql> select SQL_NO_CACHE -> oid_id, -> enum_name, -> enum_value, -> enum_syntax -> from -> type_enums, -> mib_oids -> where -> type_enums.module_id=1 and -> type_enums.type_id=mib_oids.oid_type -> order by -> type_id, -> enum_order; Empty set (0.02 sec) mysql> select version(); +---------------+ | version() | +---------------+ | 4.1.22-pro-nt | +---------------+ 1 row in set (0.00 sec) mysql> select SQL_NO_CACHE -> oid_id, -> enum_name, -> enum_value, -> enum_syntax -> from -> type_enums, -> mib_oids -> where -> type_enums.module_id=1 and -> type_enums.type_id=mib_oids.oid_type -> order by -> type_id, -> enum_order; Empty set (3 min 40.98 sec) ----------------------------------------------------------------------------- So, from 0.02 seconds to almost 4 minutes. Nice. All versions since 4.1.22, including latest 5.0 are in the range of more then 3 minutes.