Reduced performance in mysql after upgrade to 5.6.27

Question

Our application was using MySql version 4.0.24 for a long time. We are trying to migrate it to version 5.6.27. But, on testing the performance on 5.6.27, even the simple selects and updates are 30-40% slower when we are doing load testing. The CPU and IO speeds are much better than the older server. The storage engine of the tables is MyIsam in both versions. There's only one connection to the database. We tried the following options:

1. Changing storage engine to InnoDb - this reduce the performance drastically (70% slower)
2. Changing the innodb log size and buffer size - didn't help much
3. Increasing key buffer size with MyIsam storage engine for tables. - It made no difference

We tried modifying other parameters like query cache, tmp_table_size, heap_table_size. But, none of them made any difference.

Can you please let me know if there's any other option that we can try?

Answer 1

Some things you can look at are whether the two servers have the same amount of RAM or not as it may be that the old server has more RAM and so can cache more things in memory.

You can also look at how are you connecting to the MySQL server - is it over a network? Is that network speed / quality different? Is one server accessed locally and the other over a network.

You tried tuning some good parameters, but in case there are ones you're missing, you can run mysql -e 'show variables' on both servers and then use a tool like Winmerge to compare the values of the two and see what might be different that you might not have thought of.

Answer 2

After trying multiple options, here are the configurations that worked for us. There may be other solutions as well but this worked for us.

Option 1:

1. Turned off the performance schema
2. Added a couple of jdbc connection parameters: useConfigs=maxPerformance&maintainTimeStats=false

Option 2:

Migrate to MariaDB. From a performance perspective, this worked out really well. It was giving a 5% better performance compared to mysql for our system. But, we couldn't pursue this option due to non-technical reasons.

Thank you for your inputs.