MySQL Bugs: #69368: performance regression in 5.6.6+ for insert into .... select ... from

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

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;

profile showed time is spent writing to disk

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

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.

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.

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).