Bug #69368 performance regression in 5.6.6+ for insert into .... select ... from
Submitted: 31 May 2013 20:36 Modified: 11 Oct 2013 16:23
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S5 (Performance)
Version:5.6.6 OS:Any
Assigned to: CPU Architecture:Any

[31 May 2013 20:36] Shane Bester
Description:
A SELECT query that takes < 1 second suddenly takes magnitudes 
longer when written in the form INSERT INTO .. SELECT ...

Version results
------------------
5.0.96:  0.72 sec
5.0.71:  0.79 sec
5.5.33:  0.77 sec
5.6.5 :  1.27 sec
5.6.6 :  24.75 sec  <--- 
5.6.13:  26.08 sec
5.7.2 :  27.07 sec

How to repeat:
set join_buffer_size=1024*1024;
set tmp_table_size=1024*1024;
set read_buffer_size=1024*1024;
set sql_mode='';
drop table if exists t1;
create table t1(a int)engine=myisam;
insert into t1 values (1),(2),(3),(4),(5),(6),(7);
select count(t1.a) from t1,t1 t2,t1 t3,t1 t4,t1 t5,t1 t6,t1 t7,t1 t8;
select version();
insert t1 select count(t1.a) from t1,t1 t2,t1 t3,t1 t4,t1 t5,t1 t6,t1 t7,t1 t8;
[31 May 2013 20:48] Shane Bester
profile showed time is spent writing to disk

Attachment: bug69368.jpg (image/jpeg, text), 235.97 KiB.

[2 Jul 2013 17:50] Olav Sandstå
The performance regression seems to be related to writing the temporary table into MyISAM. If I configure tmp_table_size and max_heap_table_size sufficiently high (in this case setting them to 96M) in order to ensure that the temporary table can be stored in the Memory engine, the performance regression is no longer present.

Work around for bug: configure tmp_table_size and max_heap_table_size so that the temporary table fits in the Memory storage engine.
[11 Oct 2013 16:23] Paul Dubois
Noted in 5.6.15, 5.7.3 changelogs.

Some INSERT INTO ... SELECT ... FROM statements were slow unless the
tmp_table_size and max_heap_table_size system variables were set
large enough to permit the temporary table used for query processing
to be stored in the MEMORY storage engine.
[4 Dec 2013 11:51] Laurynas Biveinis
5.6$ bzr log -r 5503
------------------------------------------------------------
revno: 5503
committer: Chaithra Gopalareddy <chaithra.gopalareddy@oracle.com>
branch nick: mysql-5.6
timestamp: Wed 2013-10-09 12:54:21 +0530
message:
  Bug#16894092: PERFORMANCE REGRESSION IN 5.6.6+ FOR INSERT
                INTO .... SELECT ... FROM
  
  Problem:
  The performance regression is related to writing the temporary table
  into MyISAM. If we configure tmp_table_size and max_heap_table_size
  sufficiently high (in this case setting them to 96M) in order to ensure that
  the temporary table can be stored in the Memory engine, the performance
  regression is no longer present.
  Regression source was found to be WL#6071.
  
  Analysis:
  While creating temporary table, a random scan is initialized.
  This initialization internally frees buffers in case of myisam.
  As a result writes/reads happen to disk rather than
  cache. And hence the performance hit when we try to create
  tmp table in myisam.
  
  Solution:
  Initialize the scan without freeing buffers which can be done
  by calling ha_rnd_init(1).