| 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?
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?