MySQL 5.4 comes with Innodb engine which seems to have much better performance than MySQL 5.0 – this is due to locking and IO patches from Google integrated in this release (which are similar to appropriate Percona patches) as well as some unique fixes such as different innodb_thread_concurrency handling and other optimization.
Should we take Innodb from MySQL 5.4 and merry it with unique Percona patches (adaptive checkpoints, additional undo slots, profiling, etc) and integrate it with MySQL 5.0 ? How useful would you find it ?
Currently we see a lot of customers not quite ready to update to MySQL 5.1 in particular as there is little in this version which benefits their workload, which consists of the queries running just fine on 5.0.
5.4 especially introduces optimizer changes which besides positive impact for some queries may break something which works well now.
In general I find the current situation with Innodb is quite strange – leaving apart Innodb derivatives, like XtraDB there are 3 innodb versions around. There is MySQL 5.0 version which did not get significant changes for around 2 years now. There is Innodb Plugin which has significant architectural changes such as new storage format, compression and fast index build. Innodb plugin 1.0.3 got some Google changes for CPU scalability but not IO patches. I expected Innodb to announce plugin is now stable at users conference and may be release the version with complete set of changes from google but instead we get MySQL 5.4 release with yet another set of Innodb changes. Why not to include plugin in MySQL 5.4 with relevant performance changes instead ? This would highlight it status as future Innodb code base.
Now I’m confused – if Innodb Plugin is not taking place of default Innodb in MySQL 5.4 will this ever going to happen ?
Looking at initial benchmarks I’m very impressed with Sun Performance Team work though it looks like there was not a lot of cooperation with Innodb team (otherwise I’d expect plugin to have same codebase). I also do not think development was done in true open source spirit – trading making a big marketing splash for true openness.
Where does this leave XtraDB ? The new MySQL 5.4 release is faster than XtraDB in some workloads and this is great. Competition makes the blood move and keep developer excited. We will look into the changes and adopt the best to XtraDB.
Really interesting what is background under this.
As I know MySQL was not allowed to make changes into InnoDB, so or Sun ignored this or get approval from InnoDB to make significant changes. If second, why would InnoDB need two versions: “plugin” and “5.4” ?
And as sooner or later both will become under Oracle ownership I guess only single version will make sense.
Hi Peter!
“5.4 especially introduces optimizer changes which besides positive impact for some queries may break something which works well now.”
just curious, did you write “may break” because this is a young product and you’re not sure yet? I’d be interested to know of breakage due to optimizer changes introduced in 5.4.
“Competition makes the blood move and keep developer excited. We will look into the changes and adopt the best to XtraDB.”
This is great! keep it up 😉
kind regards,
Roland.
Can we call it the Pergoogle patch? Or is it the Goocona patch?
Roland,
“may break” is experience based. All optimizer changes I’ve seen over last 10 years broke something. They typically fixed more things than they broke but it is not good enough for applications already in production – they typically have worked around things and tuned them for old optimizer behavior so new behavior may be unwanted. At least it should not be treated lightly and requires good testing on upgrade.
Some old regressions in 6.0 (I’m not sure how many of them are fixed now) can be seen here:
http://www.mysqlperformanceblog.com/2008/04/10/tpc-h-run-on-mysql-51-and-60/
Peter, all clear, thanks!
I’d love to see some of these patches backported.
I’ve seen people willing to upgrade to 5.1 (whether on their own or after someone recommends it).
I’ve also seen people willing to use XtraDB.
I’m now seeing people willing to try out 5.4.
5.4 and XtraDB are both alpha products.
I don’t see people who would be on the cutting edge with 5.4 or XtraDB, but are currently on 5.0. Especially since for many, 5.1 has given a performance improvement. So in addition to not seeing a desire (other than the one commenter above — nobody I’ve talked to has expressed this as an interest), I can’t imagine someone would want alpha patches on 5.0….anyone willing to take that risk should be willing to risk trying 5.1.
Sheeri,
People are often scared about freshness of the patches without getting into specifics. A lot of these performance optimization changes are rather local and so safe. It is like ant and elephant in comparison if you look at MySQL 5.1 or MySQL 5.4 changes. XtraDB is a more tricky beast mainly because it is based on Innodb plugin which is in active development itself.
I’d love to see the patches backported, also.
Add my vote for back-porting! I haven’t had the time to test 5.1 enough to roll it out yet. I’ve used the 5.0.67 and 5.0.75 Percona builds, and they offer some real improvements over the vanilla build in our environment.
Yes, please!!
Here I was excited to move to either InnoDB Plugin or XtraDB… BUT cPanel does not support MySQL 5.1 yet so basically have to wait until they get around to supporting MySQL 5.1 (not savvy enough to manually upgrade mysql and deal with cPanel breakages). Then can try out XtraDB or InnoDB Plugin