MySQL optimization and upgrade

[sunroof]

Hello everyone!

I have a LAMP server with Plesk 7.5 and mysql version 3.2.

I also have cca 10 databases, some tables with up to 700.000 records, and up to 500 parallel users online.

My load average is normal (1-3) most of the time, but sometimes withing strong peak time and when some spiders and bots come along my server overloads and raises load average up to 50 or more.

What is your suggestion how to optimize my server performance?

Is there any easy way to upgrade my mysql to 4.x and move from myisam to inoDB tables without losing current tables and databases. Shall i have to make any changes to my current table structures before upgrade? Will this speed up mysql performance?

My server is Xeon Dual 2,8 Ghz with 2GB of RAM.

Thank you!

[physics]

If I was in your position I'd install the new MySQL version to a different location than your current version, i.e. so that your current version will continue to run. Then use
mysqldump --opt dbname > dbname.sql
with your current databases.
Then fire up my new mysql and load these up
mysql < dbname.sql
Then move them over to innoDB. At this point you could test the speed for each version/table type since both will be running (do the same queries on them). If the new way is faster and everything works OK then you can upgrade your main mysql version and move the tables over in the production database.
I don't know if this will solve all of your problems though. Do you use indexes properly in your database already? If it's getting hit hard by spiders you might have to address that problem more directly and/or look into caching.
Another thing to try is to turn on the slow query cache and try out mytop to see what's slowing the server down during those heavy traffic times. Sometimes the solution is to change the specific queries your code is making slightly.