mysql - InnoDB + innodb_file_per_table : Configurable extent size to avoid wasted disk space?

Question

我一直在尝试使用 MariaDB (Win32/5.5.34-MariaDB-31.1)，尝试将其用作实时存档应用程序的数据库服务器。我使用 innodb_file_per_table=1，因此每个表都是一个单独的 .ibd 文件。这便于维护，例如删除旧数据时OPTIMIZE TABLE。

我有一个小型测试应用程序，其中包含大约 2,600 个表(每个归档对象使用一个表)。在磁盘上，这些表消耗约 12 GB，有用数据 (data_length + index_length) 约为 4 GB(到目前为止，我只插入/更新行，没有删除)。添加 data_length + index_length + data_free 时，我看到 9 GB。

主要问题似乎是各个.ibd 文件的增长方式。我已经执行了 2 次测试(在 COMPACT 和 COMPRESSED 表上)。首先它们会增长几 kB，但随后它们的大小会跳跃。例如:163 kB、180 kB、229 kB ... 589 kB、606 kB，然后跳转到 2 MB，然后跳转到 9 MB。

这一观察结果与 the documentation 一致其中说:

This variable does not affect the per-table tablespace files that are created if you use innodb_file_per_table=1. Those files are auto-extending regardless of the value of innodb_autoextend_increment. The initial extensions are by small amounts, after which extensions occur in increments of 4 MB.

因此，似乎每个表(超过某个限制)都有 0-4 MB 未使用但已分配的空间(假设平均 2 MB)。我在文档中找不到任何表明可以配置此范围大小的内容。这就是我的问题:

可以在 MySQL/MariaDB/其他 MySQL 替代方案上更改此自动扩展大小吗？目前每个表 2 MB 的开销是无法接受的高开销(生产系统可能有 100,000 个表或更多，即浪费了 200 GB 空间)。

当我们使用Oracle时，它没有'one_file_per_table'选项，而是为了避免表空间的数据文件碎片(由于删除旧数据然后通过alter table Shrink Space整理而导致)使用 space EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128 K 创建，它告诉 Oracle 使用固定大小的范围而不是默认的增长范围大小。(默认的增长范围导致固定大小的表空间有超过 10 GB 的可用空间，但表无法扩展，因为没有足够大的连续 block 可用。删除旧数据后，所有 10 GB 都被回收范围很小)。

Answer 1

有一些事情需要澄清。 InnoDB 表被组织成 1 MB 的范围，未压缩表的默认页面大小为 64 页。 innodb_file_per_table = 1 的非常小的表允许小于一个范围，主要是为了对拥有许多非常小的表的人进行优化。然而，正如您所注意到的，当表增长到超过几页时，它开始增加分配大小，以避免开销并最大限度地减少表的碎片。对于像您这样的用例，没有太多的想法来改进这种策略，主要是因为您的用例有点像数据库反模式。

所以，真正的答案是您应该重新考虑您的数据库设计。

但是，根据您当前的数据库设计，innodb_file_per_table = 0 可能会更好。在这种情况下，所有表都将最终保存在一个名为 ibdata1 的文件中(如果您手动配置表空间大小，则为几个文件)，并且该文件将根据需要增长。如果您删除数据或重建表，例如OPTIMIZE TABLE，ibdata1 内的可用空间将根据需要有效地重用于其他表。目前没有办法缩小 InnoDB 中的任何表空间文件(.ibd 或 ibdataX)，因此您将无法将磁盘空间恢复到操作状态。系统，不幸的是。但是，您可以将其“恢复”，并且可以在 InnoDB 中重用，因此您不必担心它会无限期增长。