使用较小页面大小的InnoDB浪费了高达6%的磁盘空间

In InnoDB bugs found during research on InnoDB data storage I mentioned MySQL Bug #67963 which was then titled “InnoDB wastes 62 out of every 16384 pages”. I said:

在InnoDB数据存储研究中发现的InnoDB错误中，我提到了MySQL Bug #67963，当时标题为“InnoDB浪费每16384个页面中的62个页面”。我说：

InnoDB needs to occasionally allocate some internal bookkeeping pages; two for every 256 MiB of data. In order to do so, it allocates an extent (64 pages), allocates the two pages it needed, and then adds the remainder of the extent (62 free pages) to a list of extents to be used for single page allocations called FREE_FRAG. Almost nothing allocates pages from that list, so these pages go to waste.

InnoDB需要偶尔分配一些内部簿记页面；每256 MiB数据需要两个。为了做到这一点，它分配一个extent（64个页面），分配它所需的两个页面，然后将extent的剩余部分（62个空闲页面）添加到一个用于单页分配的extent列表中，称为FREE_FRAG。几乎没有什么从该列表中分配页面，因此这些页面被浪费掉了。

This is fairly subtle, wasting only 0.37% of disk space in any large InnoDB table, but nonetheless interesting and quite fixable.

这是相当微妙的问题，在任何大型InnoDB表中只浪费了0.37%的磁盘空间，但仍然很有趣且可以很容易修复。

Wasting 0.37% of disk space was unfortunate, but not a huge problem…

浪费0.37%的磁盘空间是不幸的，但不是一个巨大的问题...

MySQL 5.6 brings adjustable page sizes

MySQL 5.6 带来了可调整的页面大小

Since MySQL 5.6, InnoDB supports adjustable page size through the new configuration parameter innodb_page_size1, allowing you to use 4 KiB or 8 KiB pages instead of the default 16 KiB pages. I won’t go into the reasons why you would want to reduce the page size here. Instead, coming back to MySQL Bug #67963… neither the number 62 nor 16384 are fixed; they are in fact variable.

自从MySQL 5.6以来，InnoDB通过新的配置参数innodb_page_size1支持可调整的页面大小，允许您使用4 KiB或8 KiB页面，而不是默认的16 KiB页面。我不会在这里详细介绍为什么要减小页面大小的原因。相反，回到MySQL Bug #67963... 62和16384都不是固定的；实际上它们是可变的。

The number 62 actually comes from the size of the extent, in pages. For 16 KiB pages, with 1 MiB extents, this works out to 1048576 / 16384 = 64 pages per extent. Since two pages are stolen for bookkeeping, that leaves the 62 pages above.

数字62实际上来自extent的大小，以页面为单位。对于16 KiB页面，1 MiB的extent计算结果为1048576 / 16384 = 64个页面。由于有两个页面被偷走用于簿记，所以剩下62个页面。

The num...