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:MacOS
Assigned to: CPU Architecture:Any

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

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.