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:

  • INSERTS after that CREATE

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
  • Tell us more about this CentOS system. – Michael Hampton Dec 23 '13 at 4:18
  • Sounds like you've mostly sorted out configuration differences. How about hardware differences? What other load is on your CentOS system? Like Michael asked, what is the nature of that server? Dedicated? Xen VM? etc? – EEAA Dec 23 '13 at 5:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.