I'm doing a lot of INSERTs via LOAD DATA INFILE on MySQL 5.0. After many inserts, say a few hundred millions rows (InnoDB, PK + a non-unique index, 64 bit Linux 4GB RAM, RAID 1), the inserts slow down considerably and appear IO bound. Are partitions in MySQL 5.1 likely to improve performance if the data flows into separate partition tables?
我在MySQL 5.0上通过LOAD DATA INFILE做了很多INSERT。在许多插入之后,比如几亿行(InnoDB,PK +非唯一索引,64位Linux 4GB RAM,RAID 1),插入速度显着减慢并出现IO限制。如果数据流入单独的分区表,MySQL 5.1中的分区是否可能提高性能?
4 个解决方案
#1
3
The previous answer is erroneous in his assumptions that this will decrease performance. Quite the contrary.
之前的答案在他的假设中是错误的,这会降低性能。恰恰相反。
Here's a lengthy, but informative article and the why and how to do partitioning in MySQL:
这是一篇冗长但内容丰富的文章,以及为什么以及如何在MySQL中进行分区:
http://dev.mysql.com/tech-resources/articles/partitioning.html
Partitioning is typically used, as was mentioned, to group like-data together. That way, when you decided to archive off or flat out destroy a partition, your tables do not become fragmented. This, however, does not hurt performance, it can actually increase it. See, it is not just deletions that fragment, updates and inserts can also do that. By partitioning the data, you are instructing the RDBMS the criteria (indeces) by which the data should be manipulated and queried.
如上所述,通常使用分区将类似数据分组在一起。这样,当您决定归档或平展销毁分区时,您的表不会变得碎片化。但是,这不会影响性能,实际上可以增加性能。请注意,片段,更新和插入不仅可以删除,也可以执行此操作。通过对数据进行分区,您将指示RDBMS应该操作和查询数据的标准(indeces)。
#2
1
Edit: SiLent SoNG is correct. DISABLE / ENABLE KEYS only works for MyISAM, not InnoDB. I never knew that, but I went and read the docs. http://dev.mysql.com/doc/refman/5.1/en/alter-table.html#id1101502.
编辑:SiLent SoNG是正确的。 DISABLE / ENABLE KEYS仅适用于MyISAM,而不适用于InnoDB。我从来不知道,但我去阅读文档。 http://dev.mysql.com/doc/refman/5.1/en/alter-table.html#id1101502。
Updating any indexes may be whats slowing it down. You can disable indexes while your doing your update and turn them back on so they can be generated once for the whole table.
更新任何索引可能会减慢它的速度。您可以在执行更新时禁用索引并将其重新打开,以便为整个表生成一次。
ALTER TABLE foo DISABLE KEYS; LOAD DATA INFILE ... ; ALTER TABLE ENABLE KEYS;
This will cause the indexes to all be updated in one go instead of per-row. This also leads to more balanced BTREE indexes.
这将导致索引全部一次更新而不是每行更新。这也导致更平衡的BTREE指数。
#3
0
No improvement on MySQL 5.6
MySQL 5.6没有改进
"MySQL can apply partition pruning to SELECT, DELETE, and UPDATE statements. INSERT statements currently cannot be pruned."
“MySQL可以对SELECT,DELETE和UPDATE语句应用分区修剪。目前无法修剪INSERT语句。”
http://dev.mysql.com/doc/refman/5.6/en/partitioning-pruning.html
#4
-5
If the columns INSERT checks (primary keys, for instance) are indexed - then this will only decrease the speed: MySQL will have to additionally decide on partitioning.
如果列INSERT检查(例如主键)被索引 - 那么这只会降低速度:MySQL将不得不另外决定分区。
All queries are only improved by adding indexes. Partitioning is useful when you have tons of very old data (e.g. year<2000) which is rarely used: then it'll be nice to create a partition for that data.
只有通过添加索引才能改进所有查询。当你有很多非常旧的数据(例如年份<2000)很少使用时,分区很有用:那么为这些数据创建一个分区会很好。
Cheers!
#1
3
The previous answer is erroneous in his assumptions that this will decrease performance. Quite the contrary.
之前的答案在他的假设中是错误的,这会降低性能。恰恰相反。
Here's a lengthy, but informative article and the why and how to do partitioning in MySQL:
这是一篇冗长但内容丰富的文章,以及为什么以及如何在MySQL中进行分区:
http://dev.mysql.com/tech-resources/articles/partitioning.html
Partitioning is typically used, as was mentioned, to group like-data together. That way, when you decided to archive off or flat out destroy a partition, your tables do not become fragmented. This, however, does not hurt performance, it can actually increase it. See, it is not just deletions that fragment, updates and inserts can also do that. By partitioning the data, you are instructing the RDBMS the criteria (indeces) by which the data should be manipulated and queried.
如上所述,通常使用分区将类似数据分组在一起。这样,当您决定归档或平展销毁分区时,您的表不会变得碎片化。但是,这不会影响性能,实际上可以增加性能。请注意,片段,更新和插入不仅可以删除,也可以执行此操作。通过对数据进行分区,您将指示RDBMS应该操作和查询数据的标准(indeces)。
#2
1
Edit: SiLent SoNG is correct. DISABLE / ENABLE KEYS only works for MyISAM, not InnoDB. I never knew that, but I went and read the docs. http://dev.mysql.com/doc/refman/5.1/en/alter-table.html#id1101502.
编辑:SiLent SoNG是正确的。 DISABLE / ENABLE KEYS仅适用于MyISAM,而不适用于InnoDB。我从来不知道,但我去阅读文档。 http://dev.mysql.com/doc/refman/5.1/en/alter-table.html#id1101502。
Updating any indexes may be whats slowing it down. You can disable indexes while your doing your update and turn them back on so they can be generated once for the whole table.
更新任何索引可能会减慢它的速度。您可以在执行更新时禁用索引并将其重新打开,以便为整个表生成一次。
ALTER TABLE foo DISABLE KEYS; LOAD DATA INFILE ... ; ALTER TABLE ENABLE KEYS;
This will cause the indexes to all be updated in one go instead of per-row. This also leads to more balanced BTREE indexes.
这将导致索引全部一次更新而不是每行更新。这也导致更平衡的BTREE指数。
#3
0
No improvement on MySQL 5.6
MySQL 5.6没有改进
"MySQL can apply partition pruning to SELECT, DELETE, and UPDATE statements. INSERT statements currently cannot be pruned."
“MySQL可以对SELECT,DELETE和UPDATE语句应用分区修剪。目前无法修剪INSERT语句。”
http://dev.mysql.com/doc/refman/5.6/en/partitioning-pruning.html
#4
-5
If the columns INSERT checks (primary keys, for instance) are indexed - then this will only decrease the speed: MySQL will have to additionally decide on partitioning.
如果列INSERT检查(例如主键)被索引 - 那么这只会降低速度:MySQL将不得不另外决定分区。
All queries are only improved by adding indexes. Partitioning is useful when you have tons of very old data (e.g. year<2000) which is rarely used: then it'll be nice to create a partition for that data.
只有通过添加索引才能改进所有查询。当你有很多非常旧的数据(例如年份<2000)很少使用时,分区很有用:那么为这些数据创建一个分区会很好。
Cheers!