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:
- 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!