I have been told that mysql 5.5 has a significant performance boost compared to 5.0
Our server handles a lot of data (around 30 million records processed per 5-10 seconds) and requires every drop of performance boost we can give. Will it be beneficial if we upgrade from 5.0.23 to mysql 5.5?
Also, we have lots of database indexes setup on the tables and I've been told that sometimes the indexes become corrupt after a version upgrade and they have to be rebuilt. Is this true?
The answer with MySQL is always, "it depends". That being said where 5.5 is going to give you a huge improvement on boxes with 16 cores and higher. If you use the InnoDB storage engine, then there is a good chance that you will get better performance too. The best way to find out is to get a test box and run it through a performance test of your system.
As far as upgrades go, the safest way to upgrade tends to be a dump and reload. It eliminates most possible issues. I have successfully upgrade from 5.0 to 5.1 just using mysql_upgrade. I don't know of any particular issues with indexes getting hosed by an upgrade to 5.5. (Though I haven't upgraded any of our production boxes to 5.5 yet. Test boxes haven't had issues.) If you do go the upgrade route without a dump/restore, make sure to read the docs on the web site. 5.5 behaves differently than the upgrade from 5.0 to 5.1 did.
There are actually very significant upgrades in performance withing 5.0.x. I would recommend upgrading to 5.0.92 (current latest 5.0 release) as soon as possible regardless of figure plans to move to 5.5. Any version below 5.0.37 has some serious performance problems and it wasn't until 5.0.52 or so that 5.0 really got going.
There's huge performance improvements from mysql 5.0.x to 5.5.x. Check out these benchmarks http://vbtechsupport.com/606/ and http://vbtechsupport.com/657/. Most of the focus is on InnoDB storage engine performance. If you're still using MyISAM storage engine then check out MariaDB 5.2.5 and higher as it's the only MySQL performance fork alternative which has actually done any MyISAM/core performance improvements.
You could look forward to new features I am currently playing with in MySQL 5.5 that makes it worth the effort to upgrade
Multiple InnoDB Buffer Pools
I discovered that you can have up to 64 innodb buffer pools
Currently I am working with a server that has 192G of RAM
I have 50 innodb buffer pools with 3G each
Smeisynchronous Replication
When entries in a master binary log is sent to a slave, the query is executed and then the slave reads the next SQL statement. Under semisynch rep, the acknowledges receipt of the SQL instead of waiting until the query is done.
You can also tune the read and write IO capacity to control the amount of threads dedicated to each operation.
