谨慎设置innodb_io_capacity_max

原文: https://www.percona.com/blog/2019/12/18/give-love-to-your-ssds-reduce-innodb_io_capacity_max/

前言

innodb_io_capacity 和 innodb_io_capacity_max 是经常被误解为两个 InnoDB 参数。作为数据库技术顾问，我们至少每个月都会看到客户根据存储的最高 IO 写入负载来设置这两个变量。这是正确的选择吗？它是最佳性能的值吗？SSD/闪存磨损均衡怎么样？

Innodb_io_capacity

我们先从 官方手册的介绍说 innodb_io_capacity :

“The innodb_io_capacity variable defines the number of I/O operations per second (IOPS) available to InnoDB background tasks, such as flushing pages from the buffer pool and merging data from the change buffer.“

这到底是什么意思呢？像大多数数据库存储引擎一样，当您在 InnoDB 中更新一条数据时，更新是在内存中进行的，并且在命令实际返回之前，只会将修改的简短描述写入redo log文件。缓冲池中涉及到变更操作的页面（或多个页面）被标记为脏页。随着您写入更多数据，脏页的数量会增加，并且在某些时候需要将它们写入磁盘。此过程发生在后台，称为刷新。innodb_io_capacity 定义了 InnoDB 刷新页面的速率。为了更好地说明，让我们考虑下图：

innodb_io_capacity 对 idle flushing 的影响

我们使用压测工具 sysbench 在buffer pool 中制造大约 45000 个脏页，然后我们为innodb_io_capacity设置三种值:300,200,100, 以便控制刷新的速率。 正如我们所看到的，每秒写入的页面数与 innodb_io_capacity 值相匹配。这种类型的冲洗称为空闲冲洗。idle flushing 只有在 InnoDB 不处理写入时才会发生。这是唯一一次由 innodb_io_capacity 主导的刷新。变量 innodb_io_capacity 也会作用于自适应刷新和 由更改缓冲区线程用于辅助索引更新的后台合并。在繁忙的服务器上，当自适应刷新算法处于活动状态时，innodb_io_capacity_max 变量更为重要。一篇专门介绍 InnoDB 自适应刷新算法内部的博文正在准备中。

脏页

数据库拥有大量脏页的好处和坏处的是什么？有没有非常好的理由 越快越好的刷新脏页

从缺点说起，大量的脏页会导致MySQL关闭时耗费大量的时间把脏页刷新到磁盘，通过变更计划，很容易缓解停机时间过长的问题。大量脏页的另一个负面影响是崩溃后的恢复时间，但这是非常特殊的。

如果一个脏页在缓冲池中保持一段时间，它有机会在刷新到磁盘之前接收额外的写入。最终结果是写入负载的缩减。有些模式和查询模式更容易受到写入负载减少的影响。例如，如果您要在具有以下表结构的表中插入收集的指标：

CREATE TABLE `Metrics` (
  `deviceId` int(10) unsigned NOT NULL,
  `metricId` smallint(5) unsigned NOT NULL,
  `Value` float NOT NULL,
  `TS` int(10) unsigned NOT NULL,
  PRIMARY KEY (`deviceId`,`metricId`,`TS`),
  KEY `idx_ts` (`TS`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

如果存在 20k 个设备并且每个设备收集8 个指标。结果会有 160k 热的page 。理想情况下，直到这些页面被写满之前不应该被写入磁盘，实际上是半满，因为它们是中间插入的 b-tree 的一部分。

另一个示例是记录最后活动时间的用户表。一个典型的表结构：

CREATE TABLE `users` (
  `user_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `last_login` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_activity` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `user_pref` json NOT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6521901 DEFAULT CHARSET=latin1

通常来说，只有一小部分用户会保持活跃，在此期间用户信息所在的也会随着用户使用 应用程序 不同的功能 而被更新多次。为了说明这种行为，我们使用上述表结构做实验: 主动更新大约 6.5M 行中只有 30k 行的随机子集。在实验过程中，使用了以下设置：

innodb_adaptive_flushing_lwm = 0  
innodb_io_capacity = 100
Innodb_flush_neighbors = off

我们每次测试时都改变 innodb_io_capacity_max的值并计算 30 分钟内刷新页面的比率。

从性能测试的结果来看， 当我们设置 innodb_io_capacity_max 为100 ， 大约每执行63个 update 便会有一个页面被刷新，当我们设置innodb_io_capacity_max 为 5000 ，大约每执行20个update 就会刷新一个页面。这意味着，仅通过调整 innodb_io_capacity_max，我们将整体写入负载增加了三倍。

过度 flush 对性能的影响

当 InnoDB 页面正在被刷新到磁盘时，它的访问会受到限制，需要其内容的访问可能必须等到 IO 操作完成。过多的写入负载也会对存储和 CPU 资源造成压力。上述实验中我们改变 innodb_io_capacity_max 的值，更新速率从 innodb_io_capacity_max 为 100 的 6000 trx/s 以上变为 innodb_io_capacity_max 为 4000 的小于 5400 trx/s。简单地超过 innodb_io_capacity 和 innodb_io_capacity_max 的值对于性能来说并不是最优解。

SSD/Flash Wear Leveling

yangyidba:SSD 这块翻译的不好，大家可以拍砖。。

但是为什么写入性能如此重要，它与闪存设备有什么关系呢？

我们知道，闪存设备很好，但这种性能提升也有一个缺点：耐用性。通常，SSD 在每个扇区中执行的写入操作比常规旋转驱动器要少得多。这一切都归结为使用与非门存储位的方式。这些位由一组栅极上的电压电平表示，并且当栅极在值之间循环时，栅极的最轻微劣化会影响这些电压电平。随着时间的推移，存储元件不再达到适当的电压。更便宜的闪存设备每组门、每个存储单元存储更多位，因此它们受电压电平恶化的影响更大。SSD 也有或多或少的备用存储单元来修复损坏的存储单元。

让我们看看一些SSD的耐用性。我们从英特尔网站上选择了一些型号，主要是因为提供了估计价格。

耐久性以完整的写入周期表示，即设备可以被完全覆盖的次数。耐久性是影响价格的主要变量之一。企业级 SSD 比消费级 SSD 具有更高的耐用性。Optane 系列处于企业产品的高端。

像DC P4610这样的设备相当普遍。上表中的驱动器规格显示总写入耐久性为 12.25 PB（7,840 次完整设备写入）以及执行 640k 读取 IOPS 和约 200k 写入 IOPS 的能力。如果我们假设服务器寿命为五年，这意味着平均写入带宽必须小于：

12.25 PB * 1024^3 MB/PB / (5y * 365 d/y * 24 h/d * 3600 s/h) ~ 83 MB/秒。

填充因子的影响

理论上，您可以以 83MB/秒的速度写入SSD 存储五年。这是非常高的，但是……这意味着一个空设备。如果有一个静态数据集，比如没人想修改，占 SSD 的 75%，情况就大不相同了。现在，只有 25% 的存储获得了所有写入，并且这些存储单元的循环速度要快得多。我们在五年内平均下降到大约 21 MB/秒(83的四分之一)。这仍然是一个不错的带宽，但它属于更现实的用例。

下图显示 达到 SSD 耐用性规格所需的平均写入带宽与填充因子的关系。对于 SSD，如果磁盘已满，最好定期（可能每年或每 6 个月）清除数据并重新加载。此过程重新排列数据并有助于将压力分散到所有存储单元。如果您使用的是Percona XtraDB Cluster，这相当于在删除数据集后触发完整的 SST，如果文件系统未使用丢弃选项挂载，则可能运行fstrim 。

现在，在 InnoDB 写入负载方面，由于双写缓冲区、redo log、undo log和 binary log 之类的东西，当 InnoDB 将 16KB 的页面写入磁盘时，实际写入的数据量更高，在 32KB 之间和 48KB(innodb 的写放大)。这个估计高度依赖于表结构设计和工作负载，但作为粗略估计，我们可以估计每刷新页面写入 36KB。

我们经常看到 innodb_io_capacity 和 innodb_io_capacity_max 的值都非常高，因为人们会查看他们的 SSD 的规格并设置一个非常高的数字。数以万计的值很常见；我们甚至多次看到超过 100k。如此高的值会导致激进的 InnoDB 刷新——远远超过需要。缓冲池中的脏页很少，性能下降。InnoDB checkpoint age 可能非常接近 innodb_adaptive_flushing_lwm 乘以最大 checkpoint age 。

在中等繁忙的服务器上，可以轻松达到每秒 2000 页的持续 InnoDB 刷新率。鉴于我们估计每刷新一页写入 36KB，这样的刷新率会产生 70 MB/s 的写入带宽。看上图，如果使用的SSD规格相近，填充率超过75%，用不了5年；相反，可能不到一年半。

结论

这篇文章试图阐明一个我们比我们想要的更频繁地观察到的常见问题。实际上，我们很惊讶地看到很多人建议几乎开箱即用地增加 IO 容量设置，而不是关注其他一些设置。

所以，很好，保持 io_capacity 设置尽可能低 - 你的 SSD 会感谢你的！🙂

深入浅出 InnoDB Flushing

针对 MySQL/InnoDB 刷盘调优