Bug #36725 UPDATE takes too long to execute
Submitted: 14 May 2008 20:19 Modified: 25 Nov 2008 17:26
Reporter: Shawn Hsiao Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.1.22/5.0BK/5.1BK OS:Linux (Ubuntu 7.04)
Assigned to: CPU Architecture:Any
Tags: subquery benchmark

[14 May 2008 20:19] Shawn Hsiao
Description:

Execute the following update

UPDATE ue_B1B548EE8CB781002D9F9CCA1361C4FE ue SET ue.readflag = 3,  ue.atommodified = ue.atommodified WHERE  ue.id IN  (SELECT id FROM (SELECT a.id FROM ue_B1B548EE8CB781002D9F9CCA1361C4FE a WHERE a.feedid = UNHEX('345BBB162554A4BFE67CF7E8816876B6')  AND a.readFlag = 0) as ids) AND  ue.readFlag = 0;

hangs the connection, and locks all other connections to the same database.

How to repeat:

Execute the query on the table.
[14 May 2008 20:22] Shawn Hsiao
The dump is uploaded through ftp.

Thanks.
[14 May 2008 21:36] MySQL Verification Team
Thank you for the bug report. Which is the dump file's name you have uploaded?.
Thanks in advance.
[15 May 2008 0:47] Shawn Hsiao
It was mysql-bug-36725.tgz.
[15 May 2008 7:41] Susanne Ebrecht
This could be related to bug #33362.

Please can you give result from:

$ uname -a
$ gcc --version
$ lsb_release -a
$ getconf GNU_LIBPTHREAD_VERSION
[15 May 2008 14:28] Shawn Hsiao
Hi, thanks for replying.

$ uname -a
Linux frank 2.6.20-16-generic #2 SMP Sun Sep 23 19:50:39 UTC 2007 i686 GNU/Linux

$ gcc --version
gcc (GCC) 4.1.2 (Ubuntu 4.1.2-0ubuntu4)
Copyright (C) 2006 Free Software Foundation, Inc.
This is free software; see the source for copying conditions.  There is NO
warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.

$ lsb_release -a
No LSB modules are available.
Distributor ID:	Ubuntu
Description:	Ubuntu 7.04
Release:	7.04
Codename:	feisty

$ getconf GNU_LIBPTHREAD_VERSION
NPTL 2.5
[15 May 2008 17:20] MySQL Verification Team
Thank you for the feedback. Verified on FC 8 64-bit with MyISAM and InnoDB engines.

mysql> show processlist\G
*************************** 1. row ***************************
     Id: 1
   User: root
   Host: localhost
     db: spuser002
Command: Query
   Time: 1438
  State: Sending data
   Info: UPDATE ue_B1B548EE8CB781002D9F9CCA1361C4FE ue SET ue.readflag = 3,  ue.atommodified =
ue.atommodifie
*************************** 2. row ***************************
     Id: 4
   User: root
   Host: localhost
     db: NULL
Command: Query
   Time: 0
  State: NULL
   Info: show processlist
2 rows in set (0.00 sec)

mysql> show variables like "%version%";
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| protocol_version        | 10                  | 
| version                 | 5.1.25-rc-debug     | 
| version_comment         | Source distribution | 
| version_compile_machine | x86_64              | 
| version_compile_os      | unknown-linux-gnu   | 
+-------------------------+---------------------+
5 rows in set (0.00 sec)

mysql>
[12 Jun 2008 16:24] MySQL Verification Team
I tested again and the query took 1.7h to return so it is a performance problem.
[12 Jun 2008 19:04] Shawn Hsiao
Hi,

Is it normal for that query to lock the entire database (ours is in myisam)?

Thanks
[27 Sep 2008 10:14] Konstantin Osipov
There is no data attached to the bug report that makes it impossible to ever "Fix the bug later".
It is not clear what exactly is the problem with the bug report -- the fact that the query takes too long to execute? Is it a bug?
[25 Nov 2008 15:16] Konstantin Osipov
The query examines 1741459832910 rows and uses subqueries and derived tables.
No wonder it takes a lot of time to execute.
[25 Nov 2008 15:18] Konstantin Osipov
I do not observe user or connection lock out while the query is running.
[25 Nov 2008 15:19] Konstantin Osipov
I'm using 6.0-latest, with thread priorities removed.
To summarize, I partially can not repeat the bug, partially do not see any issue with query taking so much time.
I checked in the debugger, the server is processing rows and is not blocked while the query is running.
[26 Nov 2008 1:35] Sergey Petrunya
With MySQL 6.0 and WL#3985 code ( lp:~sergefp/mysql-server/mysql-6.0-opt-subqueries) the query will still take too long (30 minutes so far) to execute.

This is because we don't have new subquery optimizations for single-table selects. If I make the query a multi-table UPDATE, I get this:

mysql> create table one (a int);
mysql> insert into one values (1);
mysql> UPDATE one,ue_B1B548EE8CB781002D9F9CCA1361C4FE ue SET ue.readflag = 3,  ue.atommodified = ue.atommodified WHERE  ue.id IN  (SELECT id FROM (SELECT a.id FROM ue_B1B548EE8CB781002D9F9CCA1361C4FE a WHERE a.feedid = UNHEX('345BBB162554A4BFE67CF7E8816876B6')  AND a.readFlag = 0) as ids) AND  ue.readFlag = 0;
Query OK, 50154 rows affected (5.90 sec)
Rows matched: 50154  Changed: 50154  Warnings: 0

It's possible that WL#3485 "Subquery optimization: FROM (SELECT)" will give further improvement. 

What we should do in order to consider this fixed is to activate semi-join optimizations for queries in form

(UPDATE|DELETE) tbl  ... WHERE  top-level-IN-subquery AND ...