I recently upgraded my MySQL server from 5.1 to 5.6 (running on CentOS 6.x). Ever since the upgrade, when I try to perform certain table tasks the queries take a REALLY long time. This is really putting a damper on my development cycle iterations, because when a test should take 1 second to perform, it's taking 1-2 minutes to perform and that really kills my productivity.
Queries that are taking a really long time:
- DROP and CREATE
- INSERTS after that CREATE
- TRUNCATE
I've got the same version of MySQL (5.6.15) installed on my local Mac. I'm specifically working on 6 tables at the moment and so I want to truncate those tables frequently. When I truncate them on my local Mac, the query takes 21 ms (totally reasonably). When I truncate the same tables on my CentOS server, it takes 8.5 SECONDS. The tables are all InnoDB.
I've tried with other tables in the same database on my local Mac vs my CentOS server and the extreme slowness persists for those other tables. So, I tried a different database altogether and the extreme slowness also persists (restoring one of my dbs on my local Mac takes 25 seconds, but on the CentOS server it takes 4 mins and 14 seconds).
Since the MySQL version is the same, I figured maybe it was a configuration difference. I exported the "show variables;" query on CentOS and local Mac and the only differences (other than things like paths or log file names) are:
innodb_use_native_aio OFF // local Mac
innodb_use_native_aio ON // CentOS
lower_case_file_system ON // local Mac
lower_case_file_system OFF // CentOS
lower_case_table_names 2 // local Mac
lower_case_table_names 0 // CentOS
have_symlink YES // local Mac
have_symlink DISABLED // CentOS
