| Bug #81016 | Creating temporary table in MySQL 5.6 is slow | ||
|---|---|---|---|
| Submitted: | 8 Apr 2016 15:23 | Modified: | 11 Apr 2016 10:18 |
| Reporter: | Yiftach Kaplan | Email Updates: | |
| Status: | Won't fix | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.6.29 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[8 Apr 2016 16:29]
MySQL Verification Team
Can you kindly add ENGINE=MyISAM onto that CREATE TEMPORARY TABLE statement? The default engine changed in some version, so we have to make the test compare the same engine.
[8 Apr 2016 16:30]
MySQL Verification Team
Also, make sure innodb_file_per_table is set explicitly in both tests.
[8 Apr 2016 17:08]
Yiftach Kaplan
The problem is only with InnoDB engine. When I switch innodb_file_per_table OFF 5.6 performance improve significantly, but its still slower then 5.5
[8 Apr 2016 17:18]
MySQL Verification Team
can you try setting innodb_stats_persistent=0 on the 5.6 ? That is additional overhead that 5.5 didn't have, and that has been improved in 5.7.
[8 Apr 2016 17:53]
MySQL Verification Team
Thank you for the bug report. On my environment 5.6 has similar behavior than 5.5 so I recommend you to upgrade to 5.7 if the speed is a must for you.
[miguel@tikal tmp]$ 5.5/bin/mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.50 Source distribution
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> DROP DATABASE IF EXISTS ppp;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> CREATE DATABASE ppp;
Query OK, 1 row affected (0.00 sec)
mysql> USE ppp;
Database changed
mysql> DROP PROCEDURE IF EXISTS ppp.aaaa;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> DELIMITER DTR
mysql> CREATE PROCEDURE ppp.aaaa()
-> SQL SECURITY INVOKER
-> BEGIN
-> DROP TEMPORARY TABLE IF EXISTS hello;
-> CREATE TEMPORARY TABLE hello
-> (
-> h1 INT,
-> h2 INT,
-> PRIMARY KEY(h1)
-> );
-> DROP TEMPORARY TABLE IF EXISTS hello;
-> END
-> DTR
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> DROP PROCEDURE IF EXISTS ppp.bbbb;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> DELIMITER DTR
mysql> CREATE PROCEDURE ppp.bbbb()
-> SQL SECURITY INVOKER
-> BEGIN
-> DECLARE i INT;
-> SET i = 1;
-> WHILE i <= 1000 DO
-> call aaaa();
-> SET i = i + 1;
-> END WHILE;
-> END
-> DTR
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql>
mysql> call bbbb();
Query OK, 0 rows affected (1 min 43.07 sec)
[miguel@tikal tmp]$ 5.6/bin/mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.31 Source distribution
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> DROP DATABASE IF EXISTS ppp;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> CREATE DATABASE ppp;
Query OK, 1 row affected (0.00 sec)
mysql> USE ppp;
Database changed
mysql> DROP PROCEDURE IF EXISTS ppp.aaaa;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> DELIMITER DTR
mysql> CREATE PROCEDURE ppp.aaaa()
-> SQL SECURITY INVOKER
-> BEGIN
-> DROP TEMPORARY TABLE IF EXISTS hello;
-> CREATE TEMPORARY TABLE hello
-> (
-> h1 INT,
-> h2 INT,
-> PRIMARY KEY(h1)
-> );
-> DROP TEMPORARY TABLE IF EXISTS hello;
-> END
-> DTR
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> DROP PROCEDURE IF EXISTS ppp.bbbb;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> DELIMITER DTR
mysql> CREATE PROCEDURE ppp.bbbb()
-> SQL SECURITY INVOKER
-> BEGIN
-> DECLARE i INT;
-> SET i = 1;
-> WHILE i <= 1000 DO
-> call aaaa();
-> SET i = i + 1;
-> END WHILE;
-> END
-> DTR
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql>
mysql> call bbbb();
Query OK, 0 rows affected (1 min 34.44 sec)
[miguel@tikal 5.7]$ bin/mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.13 Source distribution
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> DROP DATABASE IF EXISTS ppp;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> CREATE DATABASE ppp;
Query OK, 1 row affected (0.00 sec)
mysql> USE ppp;
DROP PROCEDURE IF EXISTS ppp.aaaa;
DELIMITER DTR
Database changed
mysql> DROP PROCEDURE IF EXISTS ppp.aaaa;
CREATE PROCEDURE ppp.aaaa()
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> DELIMITER DTR
mysql> CREATE PROCEDURE ppp.aaaa()
-> SQL SECURITY INVOKER
-> BEGIN
-> DROP TEMPORARY TABLE IF EXISTS hello;
-> CREATE TEMPORARY TABLE hello
-> (
-> h1 INT,
-> h2 INT,
-> PRIMARY KEY(h1)
-> );
-> DROP TEMPORARY TABLE IF EXISTS hello;
-> END
-> DTR
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> DROP PROCEDURE IF EXISTS ppp.bbbb;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> DELIMITER DTR
mysql> CREATE PROCEDURE ppp.bbbb()
-> SQL SECURITY INVOKER
-> BEGIN
-> DECLARE i INT;
-> SET i = 1;
-> WHILE i <= 1000 DO
-> call aaaa();
-> SET i = i + 1;
-> END WHILE;
-> END
-> DTR
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql>
mysql> call bbbb();
Query OK, 0 rows affected (0.32 sec)
[11 Apr 2016 10:18]
Yiftach Kaplan
Which my.cnf(s) did you use to get the same performance from MySQL 5.5 and 5.6?
Description: Creating a temporary table in MySQL 5.6 is a lot slower than 5.5 or 5.7. How to repeat: In MySQL: DROP DATABASE IF EXISTS ppp; CREATE DATABASE ppp; USE ppp; DROP PROCEDURE IF EXISTS ppp.aaaa; DELIMITER DTR CREATE PROCEDURE ppp.aaaa() SQL SECURITY INVOKER BEGIN DROP TEMPORARY TABLE IF EXISTS hello; CREATE TEMPORARY TABLE hello ( h1 INT, h2 INT, PRIMARY KEY(h1) ); DROP TEMPORARY TABLE IF EXISTS hello; END DTR delimiter ; DROP PROCEDURE IF EXISTS ppp.bbbb; DELIMITER DTR CREATE PROCEDURE ppp.bbbb() SQL SECURITY INVOKER BEGIN DECLARE i INT; SET i = 1; WHILE i <= 1000 DO call aaaa(); SET i = i + 1; END WHILE; END DTR delimiter ; call bbbb(); The time it took: +--------------+---------+ | Version | Time | +--------------+---------+ | 5.5.48 | 2.76 | +--------------+---------+ | 5.6.29 | 8.25 | +--------------+---------+ | 5.7.11 | 0.29 | +--------------+---------+ Suggested fix: At least make it work as fast as 5.5