将表从MyISAM更改为InnoDB会使系统变慢 [英] Changing tables from MyISAM to InnoDB make the system slow

问题描述

我正在使用Mysql 5.0.x

Hi I am using Mysql 5.0.x

我刚刚将许多表从MyISAM更改为InnoDB

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

使用MyISAM表安装数据库大约需要 1分钟 使用InnoDB，安装同一数据库大约需要 15分钟

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

为什么InnoDB需要这么长时间?

我该怎么做才能加快速度?

数据库安装执行以下步骤

The Database install does the following steps

1)删除架构

2)创建架构

3)创建表

4)创建存储过程

5)插入默认数据

6)通过存储过程插入数据

6) Insert data via stored procedure

默认数据的插入花费了大部分时间

The Inserting of default data takes most of the time

推荐答案

修改插入数据"步骤以从头开始事务并在末尾提交事务.我保证，您会得到改善. (如果您有大量数据，则可能需要按表划分事务.)

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.)

如果您的应用程序根本不使用事务，则应将参数innodb_flush_log_at_trx_commit设置为2.这将带给您很多性能，因为您几乎肯定会启用auto_commit，并且生成的事务比配置了InnoDB的默认参数.此设置将阻止它在每次提交时不必要地刷新磁盘缓冲区.

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.

这篇关于将表从MyISAM更改为InnoDB会使系统变慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！