Changing tables from MyISAM to InnoDB make the system slow

Question

Hi I am using Mysql 5.0.x

I have just changed a lot of the tables from MyISAM to InnoDB

With the MyISAM tables it took about 1 minute to install our database With the InnoDB it takes about 15 minute to install the same database

Why does the InnoDB take so long?

What can I do to speed things up?

The Database install does the following steps

1) Drops the schema

2) Create the schema

3) Create tables

4) Create stored procedures

5) Insert default data

6) Insert data via stored procedure

EDIT:

The Inserting of default data takes most of the time

Allanson

Which step is taking the majority of the time? – Ferrick

Answer 1

Modify the Insert Data step to start a transaction at the start and to commit it at the end. You will get an improvement, I guarantee it. (If you have a lot of data, you might want to break the transaction up to per table.)

If you application does not use transactions at all, then you should set the paramater innodb_flush_log_at_trx_commit to 2. This will give you a lot of performance back because you will almost certainly have auto_commit enabled and this generates a lot more transactions than InnoDB's default parameters are configured for. This setting stops it unnecessarily flushing the disk buffers on every commit.

Muzzle

I agree with you on "innodb_flush_log_at_trx_commit",. for instances when InnoDB are used as drop in place replacements for MyISAM then this is a better option,. – Lonnie

And another thing., insert the data in the innodb table in primary key order,. – Lonnie

Answer 2

15 minutes doesn't seem excessive to me. After all, it's a one-time cost.

I'm not certain, but I would imagine that part of the explanation is the referential integrity isn't free. InnoDB has to do more work to guarantee it, so of course it would take up more time.

Maybe your script needs to be altered to add constraints after the tables are created.

Chloroplast

Maybe also consider using a single transaction instead of auto commit. – Heartsick

@roe - thanks - The single transaction makes things go a lot faster – Allanson

Answer 3

Like duffymo said, disable your constraints(indexes and foreing/primary keys) before inserting the data.

Maybe you should restore some indexes before the data inserted via stored procedure, if its use a lot of select statements

Nigelniger