MySQL 5.1 vs MySQL 5.5 (5.1 twice as fast)
There are times when MySQL 5.1 may outperform MySQL 5.5 under certain circumstances.
Percona performed a bake-off among multiple releases of MySQL
- MySQL 4.1
- MySQL 5.0
- MySQL 5.1 (with built-in InnoDB)
- MySQL 5.1 with InnoDB-plugin
- MySQL 5.5
- MySQL 5.6
All tests were performed with MySQL unconfigured (In other words, no my.cnf was made). The results?
- MySQL 4.1 performs the best with InnoDB single-threaded
- MySQL 5.1 with InnoDB plug-in scales on multiple cores better than 5.1 built-in InnoDB, 5.5 and 5.6
If you want newer versions of MySQL to perform better, you must tune for it. In fact, I described in the DBA StackExchange the idea of performing a MySQL Bakeoff.
What do I mean tune for it?
In MySQL 5.5, there are new InnoDB options for utilizing more dedicated read threads, write threads, and overall I/O capacity. This can engage more CPUs in multicore servers. Left unconfigured, MySQL 5.5 would operate on the same level playing field, in most cases, as older versions of MySQL. Sometimes, it could perform worse.
Think in terms of the following:
- Garbage in, garbage out
- You get what you pay for
- Two words : Due diligence
Bottom Line : MySQL 5.5 and Percona Server must be configured for the desired performance enhancements.
Keep this in mind
What is a Lamborghini ?
If you do not configure the database server or configure it badly, you get this Lamborghini
If you configure the database server properly, you get this Lamborghini (this is probably what you want)
The problems you're seeing could be caused by dozens of different root causes. You definitely should not guess; you certainly need to measure and diagnose carefully. If you gather enough information, the true cause will be evident, and the solution will be obvious (provided that you also understand enough about the server's internals to know what you're looking at). If you guess and try to do things such as reconfiguring the server, my experience shows that you can make the problem much worse, or cause other problems, and you'll never really know whether any specific change helped or not.
I would suggest using the pt-stalk tool from Percona Toolkit to capture a set of diagnostic data from the server when one of the spikes of slowness happens, and ditto for when it's running more quickly. There should almost certainly be enough information there to understand what's happening. If you are not comfortable making a diagnosis from it, any competent MySQL support provider should have no problem if you tarball the samples from pt-stalk and send them over.
I don't mean to be too repetitive or insistent, but again please don't use trial-and-error on this one.