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: | |
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
[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 ...