MySQL 5.0 - time to upgrade ?
Let me express my opinion on this matter.
First I guess most of you running live application would follow "do not fix what is not broken" rule, and I think this is right.
I know number of installations still running MySQL 3.23 quite successfully and even larger number of people happy with 4.0
I would not stay with 3.23 until now as it is barely supported - it might work well for you still but if you run into some issue
it will unlikely be fixed in 3.23 and you might be forced to upgrade to 4.0 while you're not ready. You will also likely find a lot of
gotchas in this release, which are often already erased from developers memory, also most mailing/forum members are running higher versions.
With MySQL 5.0 being released I expect limited fixes will be done in 4.0 so this is release I'd try to move from in the next 6 months or so,
possibly to 4.1 first. This would leave you with more mature version, and also upgrade to next version is much better tested. Do not
expect this upgrade to be fully painless however - there are some sorting order changes, timestamp format changes and other nasty surprises.
Performance may be affected one way or another, but in general unless you can get advantage of new MySQL 4.1 optimizations/features it will be
few percent lower. Character sets is one more story - if you're not using same latin1 charset everywhere you need to be careful and watch
which charset is used for input data and if it matches the encoding you supply it in. Also do not be mistaken - Unicode (utf8) is slower and
taking more space than single byte character sets - especially if you're moving from win1251 or other previously single byte encoding to utf8
you may be surprised with increased database size and resource consumption.
So What is about MySQL 5.0 ? My personal recommendation would be to wait in production applications, unless you really need some new 5.0 features.
Of course we at MySQL need extra testing for this version so we support every decision to move to MySQL 5.0 or start seriously testing it.
So what is about existing applications ? Is there any reason to move to MySQL besides staying on the edge ? The answer warries.
For management and control MySQL 5.0 is obviously more convenient - a lot of stuff was added to SHOW STATUS, which is also now
gathering data per connection, SHOW MUTEX STATUS is other convenient command for advanced performance optimizations. There are
also bunch of setting added usable for fine tuning.
There were numerous optimizations done in MySQL 5.0 which may seriously improve some of your queries. This is of course if you did not yet optimized
your queries to be optimally executed even with earlier versions. For automatically generated queries MySQL 5.0 might be a better choice as optimizer
got smarter. Innodb got new "Compact" storage which may decrese memory/disk footprint 20% positively affecting performance, there are certain optimizations in other code parts as well. Some features may only help you for certain scenarios - for example concurrent_insert now may be forced to work even fo tables with holes (set it to 2), Innodb innodb_locks_unsafe_for_binlog uses even more relaxed locking than in 4.1 - these are best understood by looking at release notes, change log and testing.... Yes testing - test 5.0 with your application and you'll know if it will work better or worse.
Can MySQL 5.0 slow things down ? Sure it can. New features comes at extent of more code and more complex algorithms - optimizer for example has to check more things to see if new optimizations apply, the parsing takes more as there are more keywords and so on. For simple queries, like in memory lookup by primary key things can be 5-10% slower. Optimizer is also massively changed so it may generate different query plans for your queries - typically these are better plans but sometimes they are worse. One more thing to watch out for now is group commit - it is expected to be fixed soon, but currently it does not work - meaning if you're running with durable disk subsystem without battery backed up cache and innodb_flush_logs_at_trx_commit=1 you may see performance dropping down several times for small transactions. If you suspect it might be the problem in your case - try disabling binary log - this performance bug only happens with enabled binary log, as it is related to new XA code.
So have fun upgrading to MySQL 5.0 but do not expect it to be solution for all problems.