0

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?

  • Did you try optimizing the tables after the migration? – Nitek Nov 17 '15 at 8:19
  • Its mostly transient data. So, we truncated the table and did the testing. Did the same process in both old and new servers. – Sri Nov 17 '15 at 16:04
  • Anything else different? Are you using a network share for the database storage? – Nitek Nov 17 '15 at 20:34
0

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.

  • Thanks for response. The new server has 8G RAM compared to 4G on the older one. Both servers have storage locally and there's no network involved in the process. One difference is that old server was a bare metal and the new one is a VM. We did check the variables and there isn't much difference as most of them are default parameters. We installed v4.1 on the new server and tested performance. With everything else remaining same, performance on 5.6 degraded by 20%. Will let you know if we find something more. – Sri Nov 18 '15 at 16:23
0

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.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.