Bug #29423 Query gets stuck in the "PREPARING" stage
| Submitted: | 28 Jun 2007 14:59 | Modified: | 17 Aug 2007 21:39 |
|---|---|---|---|
| Reporter: | Tobias Asplund | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S4 (Feature request) |
| Version: | 5.0.41, 5.1, 4.1 | OS: | MacOS |
| Assigned to: | CPU Architecture: | Any |
[29 Jun 2007 12:38]
Sveta Smirnova
Thank you for the report. Verified as described.
[17 Jul 2007 15:45]
Hugo Lafayette
I do have the same bug. I add some observations in case it could help fixing the bug: * The "preparing" state appears only when using the clause "IN" in the parent query. This means that, if a subquery return a one row result, called "subquery_result", then: - the query (A) using "WHERE id = (subquery_result)" will take 7 seconds to execute (mostly "sending data"), - the very same query (B) with "WHERE id in (subquery_result)" will take 12 seconds "preparing" and 13 seconds overall. * The difference seems not so important but I face a nasty complication which make this bug very annoying: When I run several times the same query, the form (A) it will take almost constant time (from 8 seconds to 16 seconds for 5 simultaneous queries). But cumulate the form (B) query will result in desatrous performance, and exponential time, even on octo-core architecture: 1 query : 13 seconds 2 queries: 24 seconds (each) 3 queries: 41 seconds (each) 4 queries: 78 seconds (each) 4 queries: 78 seconds (each) 4 queries: 220 seconds (each) 7-8 queries: easly up to one hour each. I'm testing that on a octo-proc architecture, but anyway it's seems the bottelneck is not the CPU but more the disk I/O (lots of activities)
[17 Aug 2007 21:39]
Igor Babaev
- This problem will be resolved when WL#33485 is implemented (not started yet). By the above reason I move the bug to 'To be fixed later' and mark it as a 'Feature request'. Product management will decide in what version a fix for this problem appears.
[4 Nov 2008 18:21]
Valeriy Kravchuk
WL#3485 should be implemented in 6.0. In 6.0.7 I see query hanging in "Copying to tmp table" state for a long time. Is it expected result now?
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.040 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
Description: Easy to repeat case of getting a query stuck in "PREPARING" stage for an indefinite time. Use a standard copy of the world database as downloaded from the documentation section on mysql.com mysql> USE world; Database changed mysql> ALTER TABLE City ADD INDEX (District); Query OK, 4079 rows affected (0.04 sec) Records: 4079 Duplicates: 0 Warnings: 0 mysql> EXPLAIN SELECT DISTINCT Name FROM City WHERE District IN (SELECT District FROM City WHERE CountryCode = 'USA'); *************************** 1. row *************************** id: 1 select_type: PRIMARY table: City type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 4079 Extra: Using where; Using temporary *************************** 2. row *************************** id: 2 select_type: DEPENDENT SUBQUERY table: City type: index_subquery possible_keys: District key: District key_len: 20 ref: func rows: 3 Extra: Using where 2 rows in set (0.00 sec) mysql> ALTER TABLE City MODIFY Id INT UNSIGNED, DROP PRIMARY KEY; Query OK, 4079 rows affected (0.03 sec) Records: 4079 Duplicates: 0 Warnings: 0 Let's make the table bigger... mysql> INSERT INTO City SELECT * FROM City; Query OK, 4079 rows affected (0.05 sec) Records: 4079 Duplicates: 0 Warnings: 0 mysql> INSERT INTO City SELECT * FROM City; Query OK, 8158 rows affected (0.11 sec) Records: 8158 Duplicates: 0 Warnings: 0 mysql> INSERT INTO City SELECT * FROM City; Query OK, 16316 rows affected (0.20 sec) Records: 16316 Duplicates: 0 Warnings: 0 mysql> INSERT INTO City SELECT * FROM City; Query OK, 32632 rows affected (0.52 sec) Records: 32632 Duplicates: 0 Warnings: 0 mysql> INSERT INTO City SELECT * FROM City; Query OK, 65264 rows affected (0.99 sec) Records: 65264 Duplicates: 0 Warnings: 0 mysql> EXPLAIN SELECT DISTINCT Name FROM City WHERE District IN (SELECT District FROM City WHERE CountryCode = 'USA'); *************************** 1. row *************************** id: 1 select_type: PRIMARY table: City type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 130528 Extra: Using where; Using temporary *************************** 2. row *************************** id: 2 select_type: DEPENDENT SUBQUERY table: City type: index_subquery possible_keys: District key: District key_len: 20 ref: func rows: 3 Extra: Using where 2 rows in set (0.00 sec) mysql> -- Execute query mysql> show processlist; *************************** 1. row *************************** Id: 51 User: flupps Host: localhost db: world Command: Query Time: 0 State: NULL Info: show processlist *************************** 2. row *************************** Id: 56 User: flupps Host: localhost db: world Command: Query Time: 35 State: preparing Info: SELECT DISTINCT Name FROM City WHERE District IN (SELECT District FROM City WHERE CountryCode = 'USA The query now hangs for an extremely long time in this stage, once the prepare is done, the execution is instantaneous... How to repeat: USE world; ALTER TABLE City ADD INDEX (District); ALTER TABLE City MODIFY Id INT UNSIGNED, DROP PRIMARY KEY; INSERT INTO City SELECT * FROM City; INSERT INTO City SELECT * FROM City; INSERT INTO City SELECT * FROM City; INSERT INTO City SELECT * FROM City; INSERT INTO City SELECT * FROM City; INSERT INTO City SELECT * FROM City; INSERT INTO City SELECT * FROM City; INSERT INTO City SELECT * FROM City; -- Once this is executing, do a show processlist in another session... SELECT DISTINCT Name FROM City WHERE District IN (SELECT District FROM City WHERE CountryCode = 'USA'); Suggested fix: Probably need the optimizer to just shortcut and execute at some point, I'm guessing the problem is that it tries to build the whole dependant subquery list?