One of the most important variables for InnoDB is innodb_buffer_pool_size. This is the amount of memory allocated to load tablespace information for InnoDB only.
Since you are mixing MyISAM and InnoDB, you will need to find a good balance between key_buffer_size (for MyISAM indexes) and innodb_buffer_pool_size (for InnoDB reads).
In general, you want to fit your entire data into memory if possible, as disk reads are (obviously) very expensive.
You didn't mention which MySQL version you are running, but I would recommend reading through the documentation of the appropriate version for tuning InnoDB for more detailed information.
If you need concurrency of heavy UPDATEs and INSERTs, you will want InnoDB
If you need deadlock resolution, you will want InnoDB
If you want a storage engine that caches both data and indexes, you will want InnoDB
If you want to access multiple CPUs effective, you will want InnoDB (and tune it to do so)
Please refer to my past articles on InnoDB:
Feb 12, 2011 : How do you tune MySQL for a heavy InnoDB workload?Apr 14, 2011 : What are the main differences between InnoDB and MyISAM?Aug 04, 2011 : Optimizing InnoDB default settingsOct 05, 2011 : Query runs a long time in some newer MySQL versionsDec 19, 2011 : Which first: upgrade mysql version or convert storage engine?Jan 09, 2012 : Any gotchas at all with converting from MyISAM to InnoDB?Jan 27, 2012 : importing myisam 5.0 database into a 5.5 innodb serverJan 18, 2012 : Insert-heavy InnoDB table won't use all my CPUJan 21, 2012 : Is InnoDB Engine up to speed against Memory Engine?Jul 16, 2012 : decreased performance of stored procedure when migrated from mysql server 5.0 to 5.5
You said you heard that MySQL 5.5 is faster than MySQL 5.0. Out of the box, that's not true. YOU MUST TUNE MySQL 5.5 TO GET GREAT PERFORMANCE OUT OF IT !!! Here are my past articles on that subject:
You mentioned MyISAM being faster than InnoDB. If there are a lot of wide VARCHAR columns in the MyISAM with the default ROW_FORMAT=Dynamic (Default), that can degrade MyISAM performance. Saying one storage engine is faster than the other depends on the application, read/write I/O ratio, storage engine tuning.
See my posts around this subject
Addressing your issue of whether to convert now or later to InnoDB:
In the fifth link I provided ( Which first: upgrade mysql version or convert storage engine? ), I recommend converting first. However, in your particular case, I would recommend converting later. The reason is simple: You are moving the data to a different server. MyISAM tables are very easy to port. Doing an rsync or scp of /var/lib/mysql is more expedient (faster) that converting to InnoDB because the data will be slightly (sometimes significantly) big when existing as InnoDB.
There are two things you will need to do:
NEED #1
If you rysnc/scp /var/lib/mysql you will overwrite /var/lib/mysql/mysql. Therefore, I propose you move your data like this:
NEED #2
You need a straightforward script to convert MyISAM to InnoDB. Please run the following on the MySQL 5.5 DB Server :
echo "SET SQL_LOG_BIN = 0;" > /root/ConvertMyISAMToInnoDB.sql
MYSQL_CONN="-u... -p..."
mysql ${MYSQL_CONN} -AN -e"SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;') InnoDBConversionSQL FROM information_schema.tables WHERE engine='MyISAM' AND table_schema NOT IN ('information_schema','mysql','performance_schema') ORDER BY (data_length+index_length)" > /root/ConvertMyISAMToInnoDB.sql
less /root/ConvertMyISAMToInnoDB.sql
When you are satisfied with the conversion script, login to mysql on MySQL 5.5 and run
mysql> source /root/ConvertMyISAMToInnoDB.sql
Give it a Try !!!
Best Answer
The process you mentioned works fine but it takes much amount of time if the tables are bigger in size Here the way you can do it quickly .but it requires some down time depends on the table size .