One of my projects use the MyISAM engine in MySQL, but I'm considering changing it to InnoDB as I need transaction support here and there.
我的一个项目在MySQL中使用MyISAM引擎,但我正在考虑将其更改为InnoDB,因为我需要事务支持。
- What should I look at or consider before doing this?
- Can I just change the engine, or should the data be prepared for it?
在做这个之前我应该看什么或考虑什么?
我可以只更改引擎,还是应该为它准备数据?
6 个解决方案
#1
8
Yes absolutely, there are many things, you should test your application extremely thoroughly:
是的,绝对有很多东西,你应该非常彻底地测试你的应用程序:
- Transactions can deadlock and need to be repeated. This is the case (in some circumstances) even with an autocommitted transaction which only inserts one row.
- Disc usage will almost certainly increase
- I/O load during writes will almost certainly increase
- Behaviour of indexing will change because InnoDB uses clustered indexes - this may be a beneficial effect in some cases
- Your backup strategy will be impacted. Consider this carefully.
事务可能会死锁,需要重复。即使使用仅插入一行的自动提交事务,情况也是如此(在某些情况下)。
光盘使用几乎肯定会增加
写入期间的I / O负载几乎肯定会增加
索引的行为将发生变化,因为InnoDB使用聚簇索引 - 这在某些情况下可能会产生有益效果
您的备份策略将受到影响。仔细考虑一下。
The migration process itself will need to be carefully planned, as it will take a long time if you have a lot of data (during which time the data will be either readonly, or completely unavailable - do check!)
迁移过程本身需要仔细规划,因为如果你有大量数据需要很长时间(在此期间数据将是只读或完全不可用的 - 请检查!)
#2
7
There is one big caveat. If you get any kind of hardware failure (or similar) during a write, InnoDB will corrupt tables.
有一个很大的警告。如果在写入期间遇到任何类型的硬件故障(或类似),InnoDB将损坏表。
MyISAM will also, but a mysqlcheck --auto-repair will repair them. Trying this with InnoDB tables will fail. Yes, this is from experience.
MyISAM也会,但mysqlcheck --auto-repair会修复它们。使用InnoDB表尝试此操作将失败。是的,这是来自经验。
This means you need to have a good regular data backup plan to use InnoDB.
这意味着您需要有一个良好的常规数据备份计划才能使用InnoDB。
#3
5
Some other notes:
其他一些说明:
InnoDB does not reallocate free space on the filesystem after you drop a table/database or delete a record, this can be solved by "dumping and importing" or setting innodb_file_per_table=1 in my.cnf.
删除表/数据库或删除记录后,InnoDB不会在文件系统上重新分配可用空间,这可以通过在my.cnf中“转储和导入”或设置innodb_file_per_table = 1来解决。
Adding/removing indexes on a large InnoDB table can be quite painfull, because it locks the current table, creates a temporary one with your altered indexes and inserts data - row by row. There is a plugin from Innobase, but it works only for MySQL 5.1
在大型InnoDB表上添加/删除索引可能非常痛苦,因为它会锁定当前表,使用更改的索引创建临时表并逐行插入数据。 Innobase有一个插件,但它只适用于MySQL 5.1
InnoDB is also MUCH MORE memory intense, I suggest you to have as large innodb_buffer_pool_size variable as your server memory allows (70-80% should be a safe bet). If your server is UNIX/Linux, consider reducing sysctl variable vm.swappiness to 0 and use innodb_flush_method=O_DIRECT to avoid double buffering. Always test if you hit swap when toggling those values.You can always read more at Percona blog, which is great.
InnoDB的内存也非常紧张,我建议您拥有与服务器内存允许一样大的innodb_buffer_pool_size变量(70-80%应该是一个安全的赌注)。如果您的服务器是UNIX / Linux,请考虑将sysctl变量vm.swappiness减少为0并使用innodb_flush_method = O_DIRECT以避免双缓冲。总是测试你是否在切换这些值时点击交换。你可以在Percona博客上阅读更多内容,这很棒。
Also, you can run mysqlbackup with --single-transaction --skip-lock-tables and have no table locks while the backup is commencing.
此外,您可以使用--single-transaction --skip-lock-tables运行mysqlbackup,并且在备份开始时没有表锁。
In any case, InnoDB is great, do not let some pitfalls discourage you.
无论如何,InnoDB很棒,不要让一些陷阱劝阻你。
#4
3
Just altering the table and setting the engine should be fine.
只是改变表格和设置引擎应该没问题。
- One of the big ones to watch out for is that
select count(*) from MyTableis much slower in InnoDB than MyISAM.
- auto_increment values will reset to the highest value in the table +1 after a server restart -- this can cause funny problems if you have a messy db with some deletes.
- Optimum server settings are going to be different to a mainly MyISAM db.
- Make sure the size of the innodb file is big enough to hold all your data or you'll be crucified by constant reallocation when you change the engines of the tables.
其中一个大的要留意的是,从MyTable的SELECT COUNT(*)是InnoDB的比的MyISAM慢得多。
AUTO_INCREMENT值将恢复到最高值在服务器重新启动后的表+1 - 这可能会导致有趣的问题,如果你有一些删除一个混乱的分贝。
最佳服务器设置将与主要的MyISAM数据库不同。
确保innodb文件的大小足以容纳所有数据,或者在更改表的引擎时通过不断重新分配将其钉在十字架上。
#5
1
If you are intending to use InnoDB as a way to get concurrent queries, then you will want to set innodb_file_trx_commit=1 so you get some performance back. OTOH, if you were looking to re-code your application to be transaction aware, then deciding this setting will be part of the general performance review needed of the InnoDB settings.
如果您打算使用InnoDB作为获取并发查询的方法,那么您将需要设置innodb_file_trx_commit = 1以便获得一些性能。 OTOH,如果您希望重新编写应用程序以识别事务,那么决定此设置将成为InnoDB设置所需的一般性能审查的一部分。
The other major thing to watch out for is that InnoDB does not support FullText indices, nor INSERT DELAYED. But then, MyISAM doesn't support referential integrity. :-)
另外需要注意的是InnoDB不支持FullText索引,也不支持INSERT DELAYED。但是,MyISAM不支持参照完整性。 :-)
However, you can move over only the tables you need transaction aware. I've done this. Small tables (up to several thousand rows) can often be changed on-the-fly, incidentally.
但是,您只能移动需要事务感知的表。我做到了这一点。通常情况下,小表(最多几千行)通常可以即时更改。
#6
0
The performance characteristics can be different, so you may need to keep an eye on the load.
性能特征可能不同,因此您可能需要密切关注负载。
The data will be fine.
数据没问题。
#1
8
Yes absolutely, there are many things, you should test your application extremely thoroughly:
是的,绝对有很多东西,你应该非常彻底地测试你的应用程序:
- Transactions can deadlock and need to be repeated. This is the case (in some circumstances) even with an autocommitted transaction which only inserts one row.
- Disc usage will almost certainly increase
- I/O load during writes will almost certainly increase
- Behaviour of indexing will change because InnoDB uses clustered indexes - this may be a beneficial effect in some cases
- Your backup strategy will be impacted. Consider this carefully.
事务可能会死锁,需要重复。即使使用仅插入一行的自动提交事务,情况也是如此(在某些情况下)。
光盘使用几乎肯定会增加
写入期间的I / O负载几乎肯定会增加
索引的行为将发生变化,因为InnoDB使用聚簇索引 - 这在某些情况下可能会产生有益效果
您的备份策略将受到影响。仔细考虑一下。
The migration process itself will need to be carefully planned, as it will take a long time if you have a lot of data (during which time the data will be either readonly, or completely unavailable - do check!)
迁移过程本身需要仔细规划,因为如果你有大量数据需要很长时间(在此期间数据将是只读或完全不可用的 - 请检查!)
#2
7
There is one big caveat. If you get any kind of hardware failure (or similar) during a write, InnoDB will corrupt tables.
有一个很大的警告。如果在写入期间遇到任何类型的硬件故障(或类似),InnoDB将损坏表。
MyISAM will also, but a mysqlcheck --auto-repair will repair them. Trying this with InnoDB tables will fail. Yes, this is from experience.
MyISAM也会,但mysqlcheck --auto-repair会修复它们。使用InnoDB表尝试此操作将失败。是的,这是来自经验。
This means you need to have a good regular data backup plan to use InnoDB.
这意味着您需要有一个良好的常规数据备份计划才能使用InnoDB。
#3
5
Some other notes:
其他一些说明:
InnoDB does not reallocate free space on the filesystem after you drop a table/database or delete a record, this can be solved by "dumping and importing" or setting innodb_file_per_table=1 in my.cnf.
删除表/数据库或删除记录后,InnoDB不会在文件系统上重新分配可用空间,这可以通过在my.cnf中“转储和导入”或设置innodb_file_per_table = 1来解决。
Adding/removing indexes on a large InnoDB table can be quite painfull, because it locks the current table, creates a temporary one with your altered indexes and inserts data - row by row. There is a plugin from Innobase, but it works only for MySQL 5.1
在大型InnoDB表上添加/删除索引可能非常痛苦,因为它会锁定当前表,使用更改的索引创建临时表并逐行插入数据。 Innobase有一个插件,但它只适用于MySQL 5.1
InnoDB is also MUCH MORE memory intense, I suggest you to have as large innodb_buffer_pool_size variable as your server memory allows (70-80% should be a safe bet). If your server is UNIX/Linux, consider reducing sysctl variable vm.swappiness to 0 and use innodb_flush_method=O_DIRECT to avoid double buffering. Always test if you hit swap when toggling those values.You can always read more at Percona blog, which is great.
InnoDB的内存也非常紧张,我建议您拥有与服务器内存允许一样大的innodb_buffer_pool_size变量(70-80%应该是一个安全的赌注)。如果您的服务器是UNIX / Linux,请考虑将sysctl变量vm.swappiness减少为0并使用innodb_flush_method = O_DIRECT以避免双缓冲。总是测试你是否在切换这些值时点击交换。你可以在Percona博客上阅读更多内容,这很棒。
Also, you can run mysqlbackup with --single-transaction --skip-lock-tables and have no table locks while the backup is commencing.
此外,您可以使用--single-transaction --skip-lock-tables运行mysqlbackup,并且在备份开始时没有表锁。
In any case, InnoDB is great, do not let some pitfalls discourage you.
无论如何,InnoDB很棒,不要让一些陷阱劝阻你。
#4
3
Just altering the table and setting the engine should be fine.
只是改变表格和设置引擎应该没问题。
- One of the big ones to watch out for is that
select count(*) from MyTableis much slower in InnoDB than MyISAM.
- auto_increment values will reset to the highest value in the table +1 after a server restart -- this can cause funny problems if you have a messy db with some deletes.
- Optimum server settings are going to be different to a mainly MyISAM db.
- Make sure the size of the innodb file is big enough to hold all your data or you'll be crucified by constant reallocation when you change the engines of the tables.
其中一个大的要留意的是,从MyTable的SELECT COUNT(*)是InnoDB的比的MyISAM慢得多。
AUTO_INCREMENT值将恢复到最高值在服务器重新启动后的表+1 - 这可能会导致有趣的问题,如果你有一些删除一个混乱的分贝。
最佳服务器设置将与主要的MyISAM数据库不同。
确保innodb文件的大小足以容纳所有数据,或者在更改表的引擎时通过不断重新分配将其钉在十字架上。
#5
1
If you are intending to use InnoDB as a way to get concurrent queries, then you will want to set innodb_file_trx_commit=1 so you get some performance back. OTOH, if you were looking to re-code your application to be transaction aware, then deciding this setting will be part of the general performance review needed of the InnoDB settings.
如果您打算使用InnoDB作为获取并发查询的方法,那么您将需要设置innodb_file_trx_commit = 1以便获得一些性能。 OTOH,如果您希望重新编写应用程序以识别事务,那么决定此设置将成为InnoDB设置所需的一般性能审查的一部分。
The other major thing to watch out for is that InnoDB does not support FullText indices, nor INSERT DELAYED. But then, MyISAM doesn't support referential integrity. :-)
另外需要注意的是InnoDB不支持FullText索引,也不支持INSERT DELAYED。但是,MyISAM不支持参照完整性。 :-)
However, you can move over only the tables you need transaction aware. I've done this. Small tables (up to several thousand rows) can often be changed on-the-fly, incidentally.
但是,您只能移动需要事务感知的表。我做到了这一点。通常情况下,小表(最多几千行)通常可以即时更改。
#6
0
The performance characteristics can be different, so you may need to keep an eye on the load.
性能特征可能不同,因此您可能需要密切关注负载。
The data will be fine.
数据没问题。