01-07-2007, 11:58 AM #1Web Hosting Evangelist
- Join Date
- Feb 2006
MySQL 5.0 performance better than 4.1 ?
I am considering to upgrade to MySQL 5.0. I am using 4.1 at present. Now I wonder if it will really improve performance... I really have some busy databases... Also wonder if 5.0 is fully downwards compatible to 4.1
01-07-2007, 12:29 PM #2Retired Moderator
- Join Date
- Oct 2004
- Southwest UK
I don't know what you mean by 'downwards compatible', but if you mean will my old 4.1 queries work on a v5 DB, the answer is 'mostly yes'. The syntax for joins changed to the standard (in practise this means you have to put () around the join syntax sometimes).
I think they improved performance, but you'd have to try for yourself and see.Do not meddle in the affairs of Dragons, for you are crunchy and taste good.
01-07-2007, 04:20 PM #3Junior Guru Wannabe
- Join Date
- Dec 2006
The first thing to consider is this. Does 5.0 give you any features that would help you to improve overall application performance?
What's new in 5.0?
Stored procedures, triggers, views, federated and archive engines and so on. It's truly what a major version upgrade should be. Drastically improving on the base of core functionality while maintaining, as much as possible, a back-port of all features from previous versions.
How will these things improve performance?
I've seen several benchmarks of MySQL performance in earlier versions of 5 that perform, in most cases, slighly worse than MySQL 4.1. You need to consider that 5.0 adds significantly to your arsenal and while these features do add more tools they also increase the complexity and therefor the likelyhood of bugs. When I was at MySQL last year, there was a general acknowledgement by most (in MySQL and the community) that early 5.0 releases had some performance issues. This was something that they spent a great deal of manpower addressing. I do feel that all subsequent versions of MySQL will most likely outperform 4.1, including 5.1, I do see a bit of an increase versus the 5.0 trunk and about even with 4.1.
The simple test is to install 5.1 on a test environment and benchmark your site performance against it. This will give you the most accurate answer to your questions about which version performs better. Again, be fair to 5.x, make use of some of it's new features in your benchmarking, things like triggers and stored procedures, perhaps even the Federated engine or even try out Partitioning.
What about 5.1?
For example, the cluster solution we built out runs 4.1.20 and 5.1.14. It all depends on your data set and your requirements. If you have a need to handle massive data sets and use stored procedures, triggers and things like Federated and then combine that with the new Partitioning possibilites in 5.1, you can see that it will only help an application that would benefit from such things.
Remember too, just like the debate with Apache 2, you need to consider weighing the value of new features versus the stability of the existing solution. In this respect 4.1 will win out for most for quite some time, unless you really require the 5.0/5.1 features set.
It depends on the nature of your data set and application and the best way is to benchmark your application.SupportLayer - Enterprise Linux Server Management