Hi I am using Mysql 5.0.x
嗨,我使用的是Mysql 5.0.x.
I have just changed a lot of the tables from MyISAM to InnoDB
我刚刚将很多表从MyISAM改为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
使用MyISAM表大约需要1分钟来安装我们的数据库使用InnoDB大约需要15分钟来安装相同的数据库
Why does the InnoDB take so long?
为什么InnoDB需要这么长时间?
What can I do to speed things up?
我该怎么做才能加快速度?
The Database install does the following steps
数据库安装执行以下步骤
1) Drops the schema
1)删除架构
2) Create the schema
2)创建架构
3) Create tables
3)创建表
4) Create stored procedures
4)创建存储过程
5) Insert default data
5)插入默认数据
6) Insert data via stored procedure
6)通过存储过程插入数据
EDIT:
The Inserting of default data takes most of the time
大多数情况下,插入默认数据
3 个解决方案
#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.
如果您的应用程序根本不使用事务,那么您应该将参数innodb_flush_log_at_trx_commit设置为2.这将为您提供大量性能,因为您几乎肯定会启用auto_commit,这会产生比InnoDB的默认参数配置更多的事务对于。此设置可以在每次提交时不必要地刷新磁盘缓冲区。
#2
15 minutes doesn't seem excessive to me. After all, it's a one-time cost.
15分钟对我来说似乎并不过分。毕竟,这是一次性费用。
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.
我不确定,但我想这部分解释是参照完整性不是免费的。 InnoDB必须做更多的工作来保证它,所以当然会花费更多的时间。
Maybe your script needs to be altered to add constraints after the tables are created.
在创建表之后,可能需要更改脚本以添加约束。
#3
Like duffymo said, disable your constraints(indexes and foreing/primary keys) before inserting the data.
像duffymo所说,在插入数据之前禁用你的约束(索引和foreing / primary key)。
Maybe you should restore some indexes before the data inserted via stored procedure, if its use a lot of select statements
也许你应该在通过存储过程插入数据之前恢复一些索引,如果它使用了很多select语句
#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.
如果您的应用程序根本不使用事务,那么您应该将参数innodb_flush_log_at_trx_commit设置为2.这将为您提供大量性能,因为您几乎肯定会启用auto_commit,这会产生比InnoDB的默认参数配置更多的事务对于。此设置可以在每次提交时不必要地刷新磁盘缓冲区。
#2
15 minutes doesn't seem excessive to me. After all, it's a one-time cost.
15分钟对我来说似乎并不过分。毕竟,这是一次性费用。
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.
我不确定,但我想这部分解释是参照完整性不是免费的。 InnoDB必须做更多的工作来保证它,所以当然会花费更多的时间。
Maybe your script needs to be altered to add constraints after the tables are created.
在创建表之后,可能需要更改脚本以添加约束。
#3
Like duffymo said, disable your constraints(indexes and foreing/primary keys) before inserting the data.
像duffymo所说,在插入数据之前禁用你的约束(索引和foreing / primary key)。
Maybe you should restore some indexes before the data inserted via stored procedure, if its use a lot of select statements
也许你应该在通过存储过程插入数据之前恢复一些索引,如果它使用了很多select语句