How to improve MySQL 5.7 Performance

MySQL 5.7 How to fix performance issuesWe upgraded to MySQL 5.7 and have had numerous problems with performance. We upgraded because we needed the new support for fractional timestamps but we’ve since learnt that this functionality has been ported back to MySQL v5.6.35.  So in some situations the best way to fix MySQL 5.7 performance issues is to downgrade to V5.6.35 which actually came out after 5.7.

We also struggled with some indexes which MySQL 5.7 ignored despite everything we tried. The queries kept coming back up in the slow running query log. These indexes then just started working after we had rebooted the whole server for another reason. So if you are finding MySQL 5.7 slow, it’s worth trying a reboot.

The other problem we had was that 5.7 can take a very long time to startup. We still haven’t found a solution for this other than to downgrade back to 5.6.35. It in part seems to be caused by having very large record sizes on some tables but then we also saw it affecting some very short tables. It seems to be that after a restart MySQL 5.7 rebuilds the query optimisation statistics cache and buffer pools each time. In previous versions these can be preserved when the Database is closed down. We haven’t managed to make this work in 5.7.

In my view as of January 2017 5.7 is not production ready and the only real solution to addressing the performance issues is to downgrade back to v5.6.35.  For the record I’ve found MySQL V5.6.35 reliable and performant so far.