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:
None 
Category:MySQL Server: Optimizer Severity:S4 (Feature request)
Version:5.0.41, 5.1, 4.1 OS:Mac OS X
Assigned to: CPU Architecture:Any
Triage: D5 (Feature request)

[28 Jun 2007 14:59] Tobias Asplund
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?
[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?