mySQL分区多文件与单文件性能? [英] mySQL partitioning multi-file vs. one-file performance?
问题描述
当对一个大表进行分区时，我可以选择将标志-innodb_file_per_table设置为TRUE或FALSE. True将创建许多文件(每个分区一个)，并极大地增加了磁盘使用率，但允许我将分区分布在不同的卷上(我不打算这样做). FALSE将表保留为一个大文件.假设我将所有文件都放在相同的逻辑卷上，那么我可以期望这两个选项之间在查询性能上有明显的不同吗?或者，更笼统地说，除了磁盘使用和管理之外，在这两个选项之间进行选择时是否还有其他问题要考虑?
When partitioning a large table, I have a choice to set the flag -innodb_file_per_table to TRUE or FALSE. True will create many files (one per partition) and greatly increase my disk usage, but allows me to spread partitions on different volumes (which I do not plan to do). FALSE will keep the table as one big file. Assuming I keep all files on the same logical volume, can I expect any significant query performance difference between the two options? Or, more generally, are there any issues to consider when making the choice between the two options besides disk usage and management?
一些统计:
- 表总数:20(我只对几个分区感兴趣- 参见我的其他问题)
- 最大的表具有1亿条记录.
- 总数据库大小约为60G.
- total number of tables: 20 (only a few I am interested in paritioning - see my other question)
- largest tables have 100M records.
- total db size is about 60G.
推荐答案
正如您已经说过的，-innodb_file_per_table将决定是将一个表存储在一个文件中还是(如果已分区)在许多文件中.
As you've already stated -innodb_file_per_table will decide whether one table will be stored in one file or (if partitioned) in many files.
以下是每种方法的利弊(不一定是完整列表).
Here are some pros and con's of each approach (not necessary a complete list).
Single file per table Multiple files per (partitioned) table
-------------------------------------- --------------------------------------
+ System uses less filehandles - System uses more filehandles
+ One one fsync per second per table - Possibly many more fsync calls (bottleneck)
(less fs overhead (journal etc)) (more fs overhead)
+ Single file uses less space overall - Much larger disk space usage
- Single file fragments badly + Less fragmentation
- Optimize table (et al) takes longer + You can choose to optimize just one file
- One file = one filesystem + You can put heavy traffic files on a fast fs
(e.g. on a solid state disk)
- Impossible to reclaim disk space + possible to emergency-reclaim disk space
in a hurry (truncate table takes long) fast (just delete a file)
- ALTER TABLE can use large % of disk- + rebuilding with ALTER TABLE will use less
space for temp tables while rebuilding temp disk space
通常，我会不推荐多个文件.
但是，如果您的工作负载导致严重的碎片化并且 optimize table花费的时间太长，则使用多个文件将很有意义.
In general I would not recommend multiple files.
If however your workload leads to heavy fragmentation and optimize table takes too long, using multiple files will make sense.
忘记回收空间
有些人大惊小怪，在InnoDB中，表文件总是增长而从不收缩，如果删除行，则会浪费空间.
然后，他们提出了回收该空间的方案，以免耗尽可用磁盘空间. (truncate table x).
这对于处理多个文件而言会更快得多，但是所有这些都是胡说八道，因为数据库几乎总是增长并且(几乎)永远不会收缩，因此，所有的空间回收将浪费大量时间(CPU和IO)，而您的表将被占用.完全锁定(不允许读取和写入).
只是发现下个月添加数据后，您的90％完整磁盘(回收后为50％)将达到99％.
Forget about reclaiming space
Some people make a lot of fuss about the fact that in InnoDB table files always grow and never shrink, leading to wasted space if rows are deleted.
Then they come up with schemes to reclaim that space so as to not run out of free disk space. (truncate table x).
This will work much faster with multiple files, however all of this is nonsense, because databases almost always grow and (almost) never shrink, so all that reclaiming of space will waste lots of time (CPU and IO) during with your table will be fully locked (no reads and no writes allowed).
Only to find that your 90% full disk (50% after reclaim) will be 99% full after next months data additions.
但是在使用ALTER TABLE时要当心...
请考虑以下情形:
-磁盘已满60％.
-数据库占50％，其他文件占10％.
如果在任何表上执行alter table，则如果所有表都在一个文件中，则会用完磁盘空间.
如果您有多个文件，则应该没有问题(除了咖啡因中所有过量的咖啡因).
However when using ALTER TABLE beware...
Consider the following scenario:
- Disk is 60% full.
- database takes up 50%, other files takes up 10%.
If you do an alter table on any table, you will run out of disk space if you have all tables in one file.
If you have it in multiple files, you should not have problems (other than caffeine overdose from all that waiting).
这篇关于mySQL分区多文件与单文件性能?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
