I visited a customer some weeks back and saw some regression problem during an upgrade to MySQL 5.6. Problem was during initial setup of database, the CREATE TABLE statements was running much slower on MySQL 5.6 compared to MySQL 5.5.
I created a simple test case where I create one SQL file containing 1000 CREATE TABLE using the following statement syntax: CREATE TABLE TNNNN (i int, name VARCHAR(12))ENGINE=InnoDB;
Tested MySQL Versions:
OS: Ubuntu 14.04
HW: My Toshiba Portege laptop with 2 cores and SSD disk
MySQL 5.5.42 (Default settings)
Lets first get our baseline by running 10 runs: mysql test < /tmp/1000tables
Result: average execution time is 7.5 seconds
MySQL 5.6.22 (Default settings)
Lets first get our baseline by running 10 runs: mysql test < /tmp/1000tables
Result: average execution time is 23 seconds, more than 300% slower than MySQL 5.5
During this test we where mostly spending time working with disk-system, but why is MySQL 5.6 so much slower than MySQL 5.5 in creating tables?
One thing that changed in-between MySQL 5.5 and MySQL 5.6 that might impact performance of CREATE TABLE was InnoDB files per table, read more here.
Let's try going back to use one tablespace file for all tables and re-run test!
MySQL 5.6.22 (innodb_file_per_table=0)
Result: average execution time went down a bit, now at 16 seconds
This is stil far behind MySQL 5.5, something else is taking up resources during CREATE TABLE.
Another thing that was added into MySQL 5.6 was persistent optimizer statistics, read more here.
MySQL 5.6.22 (innodb_stats_persistent=0)
Result: average execution time went down a bit, now at 15.5 seconds
Lets try both options together!
MySQL 5.6.22 (innodb_file_per_table=0 and innodb_stats_persistent=0)
Result: average execution time is back at 7.5 seconds!
Conclusion
For most application I would not consider this as an huge problem, this is something done once and then you start working on the database. But for some applications where they CREATE/DROP tables as a part of normal work this might be important.
If you need to keep performance from MySQL 5.5 in your CREATE TABLE statements and new features like InnoDB files per table and persistent optimizer statistics are not important disable these features and you have the performance from MySQL 5.5 back again!
I created a simple test case where I create one SQL file containing 1000 CREATE TABLE using the following statement syntax: CREATE TABLE TNNNN (i int, name VARCHAR(12))ENGINE=InnoDB;
Tested MySQL Versions:
- MySQL 5.5.42
- MySQL 5.6.22
OS: Ubuntu 14.04
HW: My Toshiba Portege laptop with 2 cores and SSD disk
MySQL 5.5.42 (Default settings)
Lets first get our baseline by running 10 runs: mysql test < /tmp/1000tables
Result: average execution time is 7.5 seconds
MySQL 5.6.22 (Default settings)
Lets first get our baseline by running 10 runs: mysql test < /tmp/1000tables
Result: average execution time is 23 seconds, more than 300% slower than MySQL 5.5
During this test we where mostly spending time working with disk-system, but why is MySQL 5.6 so much slower than MySQL 5.5 in creating tables?
One thing that changed in-between MySQL 5.5 and MySQL 5.6 that might impact performance of CREATE TABLE was InnoDB files per table, read more here.
Let's try going back to use one tablespace file for all tables and re-run test!
MySQL 5.6.22 (innodb_file_per_table=0)
Result: average execution time went down a bit, now at 16 seconds
This is stil far behind MySQL 5.5, something else is taking up resources during CREATE TABLE.
Another thing that was added into MySQL 5.6 was persistent optimizer statistics, read more here.
MySQL 5.6.22 (innodb_stats_persistent=0)
Result: average execution time went down a bit, now at 15.5 seconds
Lets try both options together!
MySQL 5.6.22 (innodb_file_per_table=0 and innodb_stats_persistent=0)
Result: average execution time is back at 7.5 seconds!
Conclusion
For most application I would not consider this as an huge problem, this is something done once and then you start working on the database. But for some applications where they CREATE/DROP tables as a part of normal work this might be important.
If you need to keep performance from MySQL 5.5 in your CREATE TABLE statements and new features like InnoDB files per table and persistent optimizer statistics are not important disable these features and you have the performance from MySQL 5.5 back again!
