Bug #76551 | Query hangs on INSERT INTO...SELECT... ON DUPLICATE KEY UPDATE | ||
---|---|---|---|
Submitted: | 31 Mar 2015 22:31 | Modified: | 1 Apr 2015 17:15 |
Reporter: | Tianshi Wang | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: InnoDB storage engine | Severity: | S1 (Critical) |
Version: | 5.6 | OS: | Linux (centos) |
Assigned to: | CPU Architecture: | Any | |
Tags: | hang, ON DUPLICATE KEY UPDATE |
[31 Mar 2015 22:31]
Tianshi Wang
[1 Apr 2015 5:31]
MySQL Verification Team
My testcase: ------------ drop table if exists t2,t1; create table t2 (id serial,a varchar(100),b varchar(100),created timestamp,unique(a)) engine=innodb default charset=utf8; create table t1 (id serial,a varchar(100),b varchar(100)) engine=innodb default charset=utf8; insert into t1 (a, b) values('a', rand()),('a', rand()),('a', rand()),('a', rand()),('a', rand()); insert into t1 (a, b) select 'a', rand() from t1 a,t1 t1,t1 t2,t1 t3,t1 t4,t1 t5; select count(*)from t1; insert into t2 (a, b) select a, b from t1 q on duplicate key update a = q.a, b = q.b,created = now(); -------- Do you think this might be the same problem as described in http://bugs.mysql.com/bug.php?id=71507 ?
[1 Apr 2015 5:33]
MySQL Verification Team
in that bug report I had concluded it takes about 1 minute to replace 5000 duplicate rows in innodb.
[1 Apr 2015 14:26]
Tianshi Wang
Hi Shane, Thanks for quickly reply! I have noticed that the small batches dataset would get through. Nonetheless, the large batch take much longer than 5000 records/min. In other word, the performance is not linear when working with large batches. In my 1 million record case, I have to kill it after 8 hour overnight run. Other than the large batch test we had, I think this is the same bug as you had. http://bugs.mysql.com/bug.php?id=71507
[1 Apr 2015 17:15]
Tianshi Wang
Here is what I observed. Apparently the performance degrades exponentially while the source data increases. Source table records vs the time completed. Rows Time (seconds) 1K : 1.87 2K : 5.30 5K : 31.16 10K: 132.24 20K: 472
[16 Jan 2016 13:44]
Zach Brown
I think we are hitting this same issue on 5.6. I'm not totally convinced this is a duplicate of that other bug. We are doing insert/select/on duplicate key update with 8+ million rows with a similar key setup. We've let it run over 13 hours and never had it complete. What is then curious about this is that if we run just the select portion of the insert/select it completes in a reasonable amount of time. (I assume it's priming the cache at this point maybe?) Then, if we then re-run the exact same insert/select/on duplicate key update it will complete in a matter of minutes.
[16 Jan 2016 20:29]
Zach Brown
Upgrading to 5.7 resolves the issue.
[16 Jan 2016 20:31]
Zach Brown
I should extrapolate... Upgrading to: mysqld Ver 5.7.10 for Linux on x86_64 (MySQL Community Server (GPL)) resolved the issue.