有很多文章夸大了(当然是恕我直言)对innodb_file_per_table的需要。我了解使用innodb_file_per_table可以更好地控制各个表;例如分别备份每个表。但是,声称性能更好的说法值得怀疑。

在我的测试中,对于60 GB的数据库,innodb_file_per_tableibdata1的性能没有差异。当然,这是对普通查询的简单测试,对于现实生活中的复杂查询,情况可能会有所不同(这就是我问这个问题的原因)。带有ext4的64位linux可以有效地处理大文件。

使用innodb_file_per_table,需要更多的磁盘I / O操作;因此,请执行以下操作。

表空间是在单个JOIN上共享的;这在复杂的FOREIGN KEYibdata约束中很重要。单独表的专用表空间如何节省磁盘空间?当然,使用ALTER为每个表释放表空间比较容易,但是它仍然是一个昂贵的过程(带有表锁)。

问题:innodb_file_per_table是否会对mysql的更好性能产生影响?如果是,为什么?

评论

请参阅我的问题的以下答案:dba.stackexchange.com/questions/7924/…可能也有帮助。

#1 楼

我认为这不是性能问题,而是管理问题。

每个表具有单独的文件,例如,您可以将不同的数据库存储在不同的存储设备中。

您可以处理无法处理大文件的文件系统中非常大的数据库的情况(至少将问题推迟到一个表达到文件大小限制之前)。

您没有不受控制的表空间增长。如果要删除一些大表,则ibdata文件将保持较小。

可能会影响性能的一个方面是表数据和索引的碎片化,每个表都会受到限制。但这需要进行测试才能确认。

评论


表空间的增长正是您想要innodb_file_per_table的原因。

– sjas
17年6月15日在19:34

#2 楼


为什么要使用innodb_file_per_table?


因为可以在文件级完成个人管理,因此更容易管理。这意味着即使服务器关闭,您仍然可以通过复制表文件来复制数据,而使用共享表空间意味着要么复制不必要的大量文件,要么寻找某种方法使服务器运行以提取数据(您确实不想使用十六进制编辑器手动提取数据。)有人警告说,您不能简单地将.ibd文件从一台服务器复制并粘贴到另一台服务器。这可能是正确的,但不适用于同一台服务器上的备份(我在这里使用术语``备份''的传统意义是制作副本;即不是彻底改变整个内容)。此外,ibdata1会在启动时自动重新创建(如大多数“转换为每个表的文件”指南的delete ibdata1步骤所示)。因此,除了ibdata1文件(及其对应的.ibd等文件)外,您无需复制.frm

如果要恢复丢失的表,应该足以复制其表.ibd.frm文件,以及information_schema(比ibdata1小得多)。这样,您可以将它们放置在虚拟服务器中并提取表,而不必复制整个庞大的东西。


但是,关于获得更好性能的说法令人质疑。 …使用innodb_file_per_table,需要更多的磁盘I / O操作;这在复杂的JOIN和FOREIGN KEY约束中非常重要。


不足为奇,性能将完全取决于所使用的特定数据库。一个人与另一个人会有(甚至很大)不同的结果。

的确,将会有更多的磁盘I / O操作和每个表文件,但只会更多。考虑一下系统如何工作。



对于整体数据库:


服务器已启动

ibdata1已打开
读取头和元数据
结构和元数据被缓存在内存中
查询发生

服务器访问磁盘并从中读取数据已打开的ibdata1

服务器可能会将数据缓存在内存中。对于每个表的数据库,请执行以下操作:


服务器已启动

ibdata1已打开
读取标头和元数据
每个.ibd文件都已打开
从每个.ibd文件中读取标头和元数据
结构和元数据被缓存在内存中
发生查询

服务器访问磁盘并从已读取的数据中读取数据打开的.ibd文件
服务器可能会将数据缓存在内存中





您会注意到,当服务器运行时,您不能移动数据文件,因为服务器对它们具有打开的句柄。这是因为启动时会打开它们并使它们保持打开状态。它不会为每个单独的查询打开和关闭它们。它不在运行时。此外,尽管每个单独的.ibd文件都有其自己的单独开销(文件签名,结构等),但它们会缓存在内存中,并且不会为每个查询重新读取。而且,即使使用共享的表空间,也可以读取相同的结构,因此几乎不需要(如果有的话)需要更多的内存。
mysql的性能?


实际上,如果有的话,性能实际上可能会更差。

使用共享表空间时,读写操作可以有时/经常合并,以便服务器从ibdata一次读取多个表中的数据样本。

但是,如果数据分散在多个文件中,则它必须单独执行每个单独的I / O操作。

当然,这再次完全取决于所讨论的数据库。实际性能影响将取决于共享表空间的大小,查询频率和内部碎片。有些人可能会注意到差异很大,而其他人可能根本看不到任何影响。


表空间在单个ibdata上共享;单独表的专用表空间如何节省磁盘空间?


却不能。如果有的话,它会增加磁盘使用量。

我没有要测试的60GB数据库,但是我的“微不足道”的个人数据库包含我的WordPress安装和一些供个人使用的小表使用共享表空间时,开发测试的大小约为30MB。将其转换为每个表的文件后,它膨胀到约85MB。即使删除所有内容并重新导入,它仍大于60MB。

该增加归因于两个因素: (出于某种原因)10MB,即使其中仅存储了ibdata1

使用共享表空间,只有information_schema具有文件签名,元数据等开销,但是对于每个表,每个单独的ibdata1文件都具有所有这些。这意味着总数(即使假设的<10MB .ibd)至少要大一些:

GetTotalSizeofOverhead() * GetNumTables()



显然,这些不会巨大的增长(除非您正在使用限制数据库大小的主机或将其存储在闪存驱动器等上),但是它们仍然在增长,同时通过将(每个)表切换为每表文件,您可以缩小ibdata1降至10MB,总的来说总会超过以前。

#3 楼

这是我始终使用innodb_file_per_table的原因:

每张表没有文件,ibdata文件永远不会压缩或缩小或减少空间。删除行,删除表或数据库时不行。如果您有活动的排队系统,则2GB的数据可以立即变成20GB的文件。

假设您要在更改之前备份当前1GB表,然后再将其删除。您在ibdata中留出了GB的未使用空间。 Bummer。

可能有无数的实例,其中临时措施使单个数据文件膨胀,但是我认为,没有理由不使用innodb_file_per_table

此外,这是一篇不错的文章,可供阅读:
http://code.openark.org/blog/mysql/reasons-to-use-innodb_file_per_table

评论


我意识到始终也这样做是件好事。由SSD支持的磁存储阵列可以针对表的较小文件更有效地处理读/写缓存。对于一堆%99.99的时间仅被“读取”但未被写入的表,它们始终位于存储控制器的缓存中,这大大缩短了响应时间。

–sdkks
18年4月13日在8:15

#4 楼

我之所以不使用innodb_file_per_table是因为性能。

我在mysql 5.5.45 Linux CentOS 6.7版上对450个表进行了数据库测试,对于插入的单元测试在每次测试之前将其固定到数据库中(不是每次都使用所有表),并且测试本身在数据库(插入,更新,删除,选择)中确实可以正常工作(当数据库表没有分成更多文件时,性能提高了3-5倍)。

我建议您在决定使用innodb_file_per_table之前,先对要使用的查询进行数据库测试并进行比较。

也许您可以发现对于生产服务器,可以使用innodb_file_per_table但是对于CI环境(持续集成)来说,它会开始进行单元测试(经常使用DB),而对于经常进行单元测试的开发人员来说,由于性能原因,最好不要使用它。

评论


我猜这是由于分配所有450个表的初始文件与分配一个文件所需要的时间。在生产中,这种情况只会发生一次,因此不应该成为问题,但是您要指出的一点是,对于快速创建数据库然后将其完全拆解并在单个ibdata文件中重复一遍是更好的选择。

– ColinM
17年4月1日在16:18

#5 楼

它使数据更易于管理,因为您可以回收未使用的空间,这很好。

我认为,如果您的数据库主要用于选择查询,则不会对性能产生太大影响。它仍然必须读取大约相同数量的数据。我认为从哪个文件读取数据并不重要。

但是,这可能会使执行大量插入和更新的数据库的性能变差。这是因为提交事务后,mysql在存储文件上调用fsync()。如果只有一个文件,它将发出一个呼叫并等待该呼叫完成。如果有很多文件,则必须多次进行调用,并等待所有这些调用返回,然后才能提交commit命令。

这是有人遇到此问题的帖子:http:/ /umangg.blogspot.com/2010/02/innodbfilepertable.html

#6 楼

如下面的文章所述,性能与管理数据(粗略的操作本身)无关,而是与创建和删除对象有关。 '不适用,但对于连续测试应该是有意义的。

https://www.percona.com/blog/2015/02/24/mysqls-innodb_file_per_table-slowing/

#7 楼

恕我直言,最好使用innodb_file_per_table,它更安全。如果不使用它,则在仅允许4 GB文件的FAT32系统上可能会出现问题。
我用斯洛伐克语(https://www.itsoft.sk/preco-sa -neuvolni-miesto-na-disku-po-zmazani-mysql-tabulky /)。