We recently upgraded from MySQL 5.1 to Percona 5.6 and have had lots of problems and performance issues. Has anyone else noticed such problems?
Problems include:
MySQL server has gone away at random times
100% CPU for hours when a query uses block_nested_loop optimization (we now switch this off)
More deadlocks than we ever had with 5.1
More slow queries than we ever had with 5.1
I monitor the database using MONyog and didn’t get anywhere near as many emails as I do now when we were using 5.1
We had to migrate because we wanted to use the aes_encrypt and decrypt commands but I’m seriously regretting doing so now.
What was the process you used for upgrading? Upgrading to 5.6 can be rough sometimes, especially coming from that old of a version. The key is to start with a good base by upgrading from 5.1 to 5.5, then from 5.5 to 5.6, doing mysqldumps each time for the upgrade and not an in-place binary upgrade.
The next big thing is how you managed your config, i.e if you are using the same my.cnf from 5.1 in 5.6, or if you created a brand new one for 5.6. That’s a lot of moving parts, so you have to try and reduce the number of variables by making sure everything is configured the same way (as much as possible anyway), and then slowly make changes to take full advantage of 5.6 and monitor performance along the way.