Bug #69219 CREATE TEMPORARY TABLE ... SELECT is slower on 5.6 than on 5.1

• Submitted: 13 May 2013 20:14
• Modified: 13 May 2013 20:30
• Reporter: Sveta Smirnova
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S5 (Performance)
• Version: 5.6.12
• OS: Any
• CPU Architecture: Any
• Tags: regression

[13 May 2013 20:14] Sveta Smirnova

Description:
Originally posted at http://forums.mysql.com/read.php?20,585692,585692#msg-585692

CREATE TEMPORARY TABLE ... SELECT is slower on 5.6 than on 5.1

How to repeat:
Run attached test

Results in 5.6.12:

select now();
now()
2013-05-13 23:01:59
create temporary table norepl_t1 engine=myisam select amount, price, money, id_product from scj_tt_ships;
select now();
now()
2013-05-13 23:02:13

Results in 5.1.70:

select now();
now()
2013-05-13 23:05:30
create temporary table norepl_t1 engine=myisam select amount, price, money, id_product from scj_tt_ships;
select now();
now()
2013-05-13 23:05:33

3 seconds vs 14!

[13 May 2013 20:15] Sveta Smirnova

test case for MTR

Attachment: young.test (application/octet-stream, text), 4.41 KiB.

[13 May 2013 20:30] Sveta Smirnova

Results for 5.6.10-linux-glibc2.5-x86_64:

select now();
now()
2013-05-13 23:25:23
create temporary table norepl_t1 engine=myisam select amount, price, money, id_product from scj_tt_ships;
select now();
now()
2013-05-13 23:25:39

And for 5.5.31-linux2.6-x86_64:

select now();
now()
2013-05-13 23:27:33
create temporary table norepl_t1 engine=myisam select amount, price, money, id_product from scj_tt_ships;
select now();
now()
2013-05-13 23:27:34

[13 May 2013 22:43] Sveta Smirnova

If create temporary table using storage engine InnoDB things go even worse:

=====mysql-5.5.31-linux2.6-x86_64=====
=====young=====
select now();
now()
2013-05-13 23:31:44
create temporary table norepl_t1 engine=innodb select amount, price, money, id_product from scj_tt_ships;
select now();
now()
2013-05-13 23:31:48

=====mysql-5.6.10-linux-glibc2.5-x86_64=====
=====young=====
select now();
now()
2013-05-13 23:40:05
create temporary table norepl_t1 engine=innodb select amount, price, money, id_product from scj_tt_ships;
select now();
now()
2013-05-13 23:40:47

42 seconds vs 4

[22 Jun 2013 10:52] MySQL Verification Team

related:
Bug #69368 Performance regression in 5.6.6+ for insert into .... select ... from

[21 Aug 2013 9:59] Sveta Smirnova

Original reporter config:

innodb_file_per_table=1 
binlog_cache_size = 4M 
key_buffer = 128M 
key_buffer_size = 128M 
max_allowed_packet = 64M 
table_cache = 32K 
table_open_cache = 16K

sort_buffer_size = 64M 
read_buffer_size = 32M 
read_rnd_buffer_size = 16M 
join_buffer_size = 32M

thread_cache_size = 8

thread_concurrency = 16

innodb_support_xa=0 
innodb_thread_concurrency=16

innodb_log_buffer_size = 64M 
innodb_flush_log_at_trx_commit = 2

He reports issue is still repeatable with 5.6.13 and 5.5.33 for him.

[7 Nov 2013 14:01] Sveta Smirnova

option file for the test

Attachment: young-master.opt (application/octet-stream, text), 287 bytes.