Changing tables from MyISAM to InnoDB makes the system slow

Question

Hi, I am using Mysql 5.0.x

I just changed a lot of tables from MyISAM to InnoDB

Using MyISAM tables, it took 1 minute to install our database. In InnoDB, it takes 15 minutes to install the same database.

Why is InnoDB taking so long?

What can I do to speed things up?

Database installation performs the following steps

1) Drops the circuit

2) Create a circuit

3) Creating tables

4) Creating stored procedures

5) Insert default data

6) Insert data through a stored procedure

EDIT:

Inserting default data takes most of the time

Answer 1

Change the “Insert Data” step to start the transaction at the beginning and commit it at the end. You will get an improvement, I guarantee it. (If you have a lot of data, you can break the transaction down to the table.)

If the application does not use transactions at all, then you should set paramater innodb_flush_log_at_trx_commit to 2. This will give you more performance because you will almost certainly enable auto_commit, and this will result in a lot more transactions than InnoDB's default settings are. This option stops it from flushing disk buffers unnecessarily with each commit.

Answer 2

15 minutes for me does not seem excessive. In the end, this is a one-time cost.

I'm not sure, but I would suggest that part of the explanation is that referential integrity is not free. InnoDB needs to do more work to ensure this, so of course it will take more time.

Maybe your script should be modified to add constraints after creating the tables.

Answer 3

Like duffymo, disable your restrictions (indexes and priority / primary keys) before inserting data.

You may need to restore some indexes before the data inserted through the stored procedure if it uses many select statements