Bug #70063 create table is slower in 5.6

• Submitted: 16 Aug 2013 19:47
• Modified: 17 Jan 2014 18:52
• Reporter: Arnaud Adant
• Status: Closed
• Category: MySQL Server: DDL
• Severity: S5 (Performance)
• OS: Any
• CPU Architecture: Any

[16 Aug 2013 19:47] Arnaud Adant

Description:
Create table is slower in 5.6 than in 5.5 (even with performance_schema = 0 and innodb_stats_persistent = 0) :

5.6.13 :

call create_tables();
Query OK, 0 rows affected (1 min 28.91 sec)

set global innodb_stats_persistent = 0;
Query OK, 0 rows affected (0.00 sec)

call create_tables();
Query OK, 0 rows affected (48.28 sec)

5.5.30 :

call create_tables();
Query OK, 0 rows affected (24.20 sec)

call create_tables();
Query OK, 0 rows affected (20.40 sec)

How to repeat:
--use performance_schema = 0

set global innodb_stats_persistent = 0;

drop database if exists test_tables;
create database test_tables;
use test_tables;

delimiter //
create procedure create_tables() begin
SET @x = 0;
REPEAT
set @s = "";
select concat("create table t",@x," (c char(100) character set utf8, key
(c(10))) engine=InnoDB") into @s;
prepare stmt from @s;
execute stmt;
SET @x = @x + 1;
UNTIL @x > 10000 END REPEAT;
end
//
delimiter ;

call create_tables();

set global innodb_stats_persistent = 0;

drop database test_tables;
create database test_tables;
use test_tables;

delimiter //
create procedure create_tables() begin
SET @x = 0;
REPEAT
set @s = "";
select concat("create table t",@x," (c char(100) character set utf8, key
(c(10))) engine=InnoDB") into @s;
prepare stmt from @s;
execute stmt;
SET @x = @x + 1;
UNTIL @x > 10000 END REPEAT;
end
//
delimiter ;

Suggested fix:
Fix this performance issue. 

2 fold :

- optimize the innodb_stats_persistent overhead

- fix the overhead minus innodb_stats_persistent

[16 Aug 2013 19:50] Arnaud Adant

Oups

set global innodb_stats_persistent = 1; to start with !

[16 Aug 2013 20:47] MySQL Verification Team

related: Bug 17193801 - DICT_TABLE_SCHEMA_CHECK CALLS DTYPE_SQL_NAME NEEDLESSLY - WASTING A LOT OF CPU

[17 Jan 2014 18:52] Daniel Price

Fixed as of 5.6.17, 5.7.4, and here's the changelog entry:

When creating a table there are a minimum of three separate inserts on
the "mysql.innodb_index_stats" table. To improve "CREATE TABLE"
performance, there is now a single "COMMIT" operation instead of one for
each insert. 

Thank you for the bug report.

[25 Feb 2014 19:54] Sveta Smirnova

Bug #35918 was marked as duplicate of this one.

[28 Mar 2014 19:11] Laurynas Biveinis

5.6$ bzr log -r 5758 -n0
------------------------------------------------------------
revno: 5758
committer: Thirunarayanan B<[e-mail]>
branch nick: test-5.6
timestamp: Fri 2014-01-17 10:00:21 +0530
message:
  Bug #17323202	 CREATE TABLE IS SLOWER IN 5.6
  
  Analysis:
  ========
  While creating a table, there will be minimum of 3 inserts
  in innodb_index_stats table. Instead of committing for each
  insert separately , we can commit for a innodb_index_stats table
  as a whole. This will make creation of table faster.
  
  	Approved by Jimmy [rb#4220]

[28 Mar 2014 19:12] Laurynas Biveinis

5.6$ bzr log -r 5760 -n0
------------------------------------------------------------
revno: 5760
committer: Vasil Dimov <[e-mail]>
branch nick: mysql-5.6
timestamp: Fri 2014-01-17 20:58:35 +0200
message:
  Followup to
  [e-mail]-20140117043021-02qex25bae6ac1ew :
  add a comment explaining why we sort by index name when saving stats.