Migrating php4/mysql4 to php5/mysql5: switch to InnoDB?

Question

I have a legacy web application php4/mysql4 (MyISAM, db contains some cms, some user data, some calendar application). Now I am going to migrate to a new server with php5/mysql5.

Should I change to InnoDB while migrating a mysql database? - expected advantages / disadvantages / risks? - Is MyISAM deprecated or will it some day be? Or can I safely keep it? - I heard MyISAM easily corrupts, what about InnoDB, is it crash resistant? - Is InnoDB easier or more safely to backup and restore?

Can it break my code when I do change (break the SQL queries) or break the logic (because of the changed locking mechanism), and if so what are typical scenarios?

(For the php issues I created a different question: http://stackoverflow.com/questions/745806/migrating-php4-mysql4-to-php5-mysql5-expected-php-issues)

Answer 1

They key thing you should look into is, how is your database used. If it is more Read than Write, then you should stick with MyISAM, if its more Write than Read, you should look into InnoDB.

If you want to know the difference between InnoDB and MyISAM, then Wikipedia has a great list of their differences.

MyISAM uses table-level locking on writes to any existing row, whereas InnoDB uses row-level locking.

For large database applications where many rows are often updated, row-level locking is crucial because a single table-level lock significantly reduces concurrency in the database.

Answer 2

It is worthwhile separating the MySQL v4/v5 upgrade from the table upgrades. That reduces the potential range of problems.

That said, if restarting the database is a rare occurance, then take some time to review the InnoDB server options before the v4/v5 upgrade, anyway, because many of them need a database restart. The two recommended are innodb_file_per_table=1 and innodb_flush_log_at_tx_commit=1 (look them up) and you should also look at innodb_buffer_pool_size, as it is almost certainly going to be too low if no-one has changed it.

MyISAM is going to be around for a long time. It is a very robust on-disk format that has some qualities useful for many situations. In particular, it has a fast SELECT which can be useful for a smallish table that has no or very few updates. That said, a very hot table (lots and lots of SELECTs) will benefit from being migrated to InnoDB because MyISAM does not support concurrent read.

MyISAM almost always survives a database crash with nothing more than a REPAIR TABLE needed. InnoDB is not always so lucky. The MyISAM can also be backed-up out from under the database; even if you don't lock the table beforehand, you will very likely get a file that will just work. InnoDB files are not so kind; this is why innodb_hot_copy exists.

We recently went through a MySQL v4/v5 upgrade and we only had one SQL problem: mixed-mode JOINs. Version 4's parser was fairly forgiving when mixing implicit table joins with explicit LEFT JOIN clauses. Version 5 is not so forgiving. So we took the opportunity to scour the app and upgrade all the JOINs to explicit JOINs. Apart from one or two spots that were missed, this was very successful.

I'd recommend you setup a test environment with PHP 4 talking to MySQL v5. This will let you test all this out.