亚马逊AWS官方博客
如何针对整合工作负载规划和优化兼容 MySQL 的 Amazon Aurora
定义数据库整合
对于整合用例，我将重点关注以下维度，然后更详细地讨论 Aurora MySQL 在相应环境中的运行情况：
- 表的大小。整合的通常结果是更大的表。如果您在广告技术、物联网或消费者应用程序领域，通常会将大型同类应用程序的数据库拆分为多个分片，每个分片包含一部分数据。使用 Aurora，您可能无法完全消除分片，但可以通过整合减少分片数，以减少运营开销。
- 表的数量。表的数量增加也是整合的结果。这种结果在需要租户隔离的 SaaS 应用程序中很常见，每个租户通常都有自己的数据库或一组表。这样的多个租户在较少且较大的 Aurora 集群上打包在一起，以降低每个租户的运营成本。
- 数据库的利用率。整合数据库工作负载的利用率在许多指标上有所增加，包括更多的并发连接数。
在实际运营中，您会发现同一个项目中这其中的几个维度上会出现利用率上升。以下指南可帮助您在这些维度上优化工作负载。
多大算是大？
Amazon Aurora 确实存在某些最大限制。对我们来说最重要的是 Aurora 集群的最大存储卷大小为 64 TB。卷最大大小规定了可以在 Aurora 集群中物理存储的数据量的上限。单个表的大小也存在上限。
此外，作为兼容 MySQL 的数据库引擎，Aurora MySQL 继承了 MySQL 和 InnoDB 存储引擎的许多特性。其中一些会影响整合的有效性。
如何针对大型表进行优化
Amazon Aurora 使用 16 KiB 页面存储数据。页面被分组到表空间中，表空间充当表和相关索引的容器。默认情况下，Aurora 对每个表使用单独的表空间，如果表已分区，则为表的每个分区使用单独的表空间。这样的表空间包含的大部分内容是以下数据结构：
- 包含表记录的聚集索引，按表的主键或唯一键排序。如果两个键都不可用，则使用内部单调递增的行 ID 来标识和排序记录。
- 表的辅助索引。
- 不适合在聚集索引记录储存的外部存储可变长度字段的值（BLOB、TEXT、VARCHAR、VARBINARY 等）。
前面的表结构意味着可以在给定表中存储的最大行数并没有固定值。最大行数取决于许多因素：
- 主键或唯一键选择支持的唯一值的最大数量。例如，如果对主键（流行的选择）使用无符号整数数据类型，则表最多支持 232或略多于 42.9 亿行。
- 辅助索引的数量和大小。
- 直接存储在聚集索引记录中或外部页面上的可变长度数据量。
- 数据页面得到有效利用的程度。
至于在实际中能够有效整合多大的表，架构设计和查询模式是比行数更重要的因素。随着表中行数的增加，聚集索引和辅助索引的大小以及遍历这些索引所花费的时间也会增加。因此，查询性能会随表的大小增大而下降。让我们来更深入地了解一些最佳实践和缓解性能下降的方法。
为具有大量记录的表设计架构
对于大型的表，在特定查询模式和架构下，每页的记录密度很关键。Aurora MySQL 中的最大行长度（不包括可变长度数据）的大小略小于 8 KB（数据页的一半）。数据库管理页面以保持存储效率，而不会牺牲性能。就像 MySQL 社区版本中的 InnoDB 一样，Aurora MySQL 会保留页面的一部分，不进行填充，以容纳未来的写入并减少页面拆分。如果填充率下降到 50％ 以下，它还会尝试将页面合并在一起。由于页面永远不会完全填满，因此始终存在一定的存储开销。
优化架构设计是确保有用开销不会变成过多存储浪费的最佳方法。这种浪费意味着更高的资源利用率和延迟，从而让工作负载的性能变得不可接受。
有效的架构设计有两个总体指导原则：
- 给定表行中的所有信息都应具有同等价值。也就是说，您应该以同样的频率查询和操作行所有字段中的数据。在同一行存储使用频繁和使用不频繁的数据会导致效率低下。
- 对于存储在给定列中的值，始终选择可用于表示它的最小的数据类型。在一行上的节省可能看起来很小，但如果有数十亿行，那所带来的节省将十分惊人。
如果架构包含可变长度字段，Aurora MySQL 会尝试将尽可能多的可变长度数据存储在聚集索引记录中，将其余数据存储在外部页面上。大数据记录导致数据页上的记录密度降低，进而造成查询性能下降。但是，如果查询主要影响包括可变长度字段在内的所有记录数据（读取和写入），您可能仍需要大的记录。如果不是这种情况，将这些大的字段转移到单独的表中可能更有利。更好的是，您可以将它们完全存储在数据库之外的对象存储中，例如 Amazon S3。
索引可有效提高查询性能。但是，它们需要额外的成本，消耗额外的存储和内存占用，并会降低写入性能。辅助索引记录不直接指向行的物理存储坐标。而是指向该行的主键值。因此，每个辅助索引记录都包含相应行的主键值的副本。
因此，复合主键也会导致更大的索引记录，并最终降低存储和 I/O 效率。仅使用需要的索引，并记住在复合索引中索引选择性从左向右移动。如果您的查询模式允许遵循该选择性规则，则可能有机会通过用较少的复合索引替换它们来减少索引数。
最终，通过使用有效的架构设计，在可接受的性能之下，您可以让表拥有更多的行。但是，实际的最大行数取决于数据内容以及您与数据的交互方式。
查询包含大量记录的表
分区（和子分区）是一种缓解大型表性能下降的工具。由于默认情况下每个分区都存储在单独的表空间中，因此它包含该分区表达式确定的特定数据子集的聚集索引、辅助索引和外部页面。每个表最多可以有 8,192 个分区和子分区。但是，大量分区本身会造成性能问题。其中包括内存利用率上升以及使用大量分区的查询的性能问题。
由于分区中的索引结构较小，因此遍历更快。如果查询模式针对单个分区或一小组分区（称为分区修剪的优化），那么可能会获得性能提升。但是，不是针对某些特定分区的查询（例如，带有不包含分区列的谓词的查询）可能会变慢。出现这种情况是因为对于分区，引擎必须遍历多个较小的索引而不是一个较大的索引。因此，大型分区表的性能影响取决于您在工作负载中利用分区修剪或选择的有效程度。
对于大型表，具有准确的统计信息对于查询优化器非常重要。准确的统计信息可确保查询优化器使用具有正确基数的最具针对性的索引，从而提高查询性能。默认情况下，Aurora MySQL 会对 20 个随机索引页进行采样，以估算统计信息和基数。但是，在处理非常大的表或列中具有不均匀值分布的表时，这个数字可能不够。此外，默认情况下，统计信息将持久保存在磁盘上，并在表经过重大更改后会自动重新计算。数据操作语言 (DML) 操作影响 10％ 以上的行时就会发生这种情况。
对于非常大的表，较少发生这种程度的变化，因此统计信息随着时间的推移会变得不那么准确。因此，受影响的查询的性能会随着时间的推移而逐渐降低，甚至会陡降，因为达到了关键点并且查询优化器更改了执行计划。如果您怀疑这是问题，请使用 EXPLAIN 语句查看查询执行计划，以查明预期行为变化的原因。
我们还建议您为关键工作负载查询建立基线预期性能，并监控其性能。慢查询日志在记录超过特定阈值的查询时有效，但在捕获随时间缓慢性能降级方面效果较差。要持续监视查询的性能，在 MySQL 5.6 兼容版本中，您可以使用 MySQL 性能架构。但请注意，启用它会增加内存消耗，还可能降低整体系统性能。
有两种机制可以提高统计信息的准确性：
- 使用信息架构（
INNODB_TABLE_STATS和INNODB_INDEX_STATS），然后运行ANALYZE TABLE根据需要更新统计信息，监控相关表的表的年龄和索引统计信息。 - 为数据库实例自定义数据库参数组，并增加采样的页数以提高准确性（请参阅下表）。但是，采样页面的增加也会增加计算统计信息所需的时间。
| 数据库参数 | 值 | 描述 |
| innodb_stats_persistent_sample_pages | 256 | 统计信息持久保存到磁盘时采样的页面的全局参数。您还可以针对每个表配置此参数。 |
| innodb_stats_transient_sample_pages | 256 | 与上面的参数类似的全局参数，但在统计信息未持久保存到磁盘时使用。 |
处理大型表的数据架构的频繁更改
表多大时才会导致数据定义语言 (DDL) 操作出现问题？ 大小是一个因素，但表的活跃程度可能更重要。如果表维持每秒数千次写入，即使是只有几百万条或更少记录的相对较小的表，也可能会在执行 DDL 操作时遇到问题。
如果您的工作负载或工作负载更新依赖于频繁的 DDL 操作和架构更改，那么这些操作可能会限制您使用非常大的表的能力。此行为类似于 MySQL 社区版本的运行方式。脱机 DDL 操作会将数据复制到正确架构中的新表空间；因此，您需要有足够的可用容量。它们还会在操作范围内锁定表，这会打断正常的工作负载。执行在线 DDL 操作时会直接更改表数据。但是，它们会在临时空间中缓冲对表的新写入，仅在将这些写入合并时锁定表。会进行长时间在线 DDL 操作的工作负载对表生成大量写入，因此要合并的更改量相对较大。这样的大小会导致锁定合并阶段持续更长时间。在极端情况下，临时空间可能在表更改操作完成之前耗尽，从而使得在线 DDL 操作无法完成。
Aurora MySQL 还支持快速 DDL，它允许您在表的末尾添加一个可空列作为准瞬时操作。此功能有助于缓解前面描述的一些 DDL 问题。对于无法通过常规 DDL 或快速 DDL 操作有效处理的 DDL 操作，您可以考虑使用 Percona Online Schema Change Tool 来执行操作。如果该工具适用于您的用例，它可以不那么具有破坏性的方式执行 DDL 操作，但是对于非常大的表，操作时间会更长。
如何针对大量的表进行优化
整合工作负载还可能导致 Aurora 集群中存储大量的表。实际上，可以合理地整合到单个 Aurora 集群上的表的数量取决于您的工作负载和访问模式。
MySQL 社区版本的文件系统特性会限制数据库的可扩展性（就表的大小和数量而言），与之不同，Aurora MySQL 使用专用的分布式日志结构化存储服务。因此，在 MySQL 中使用自定义表空间配置来缓解文件系统的继承限制的许多原因不适用于 Aurora。从操作上或从故障恢复的角度来看，拥有大量的表没有与文件系统相关的影响。虽然您可以使用 Aurora 自定义数据库集群参数组关闭 inndb_file_per_table 选项，但我们不建议这样做，因为它不再影响性能或恢复时间。
但是，Aurora 中的大量的表确实会影响内存利用率。具有默认参数的 Aurora 集群的内存消耗如下。
| 数据库实例使用的内存 | 消耗者 |
| 3/4 | 缓冲池（页面缓存），用于存储最近访问的数据页面。您可以在数据库参数组中更改此配置。但是，通常最好要减小缓冲池的大小。用于跟踪缓冲池有效性的相关 Amazon CloudWatch 指标有缓冲池缓存命中率和对存储的读取 IOPS。 |
| 1/24 | 查询缓存。MySQL 社区版本从版本 5.7.20 开始，不推荐使用并禁用查询缓存，与之不同，Aurora 拥有经过重新设计的查询缓存。此查询缓存不受社区版本中实现的限制。我们建议您启用查询缓存，除非您确信查询不可缓存，并且希望回收内存以用于其他缓冲区和缓存。 |
| 剩余部分 | 剩余可用内存（约 20.8％）由各种其他不太可预测的消耗者使用，例如全局或特定于连接或会话以及操作系统和托管服务进程的数据库引擎缓冲区和缓存。其中一些内存可能可用或可以变得可用。 |
使用大量的表时优化与表相关的缓存
有两个缓存与具有大量的表的工作负载尤其相关。它们的错误配置可能会导致性能和稳定性问题。
表缓存（或表打开缓存）存储由于用户活动导致的表打开的处理程序结构。每个会话都会单独打开一个表，因此如果有多个并发会话访问它，则缓存会包含同一个表的多个副本。在 r4 类 Aurora 数据库实例中，我们已将此缓存的默认大小增加到了最多 6,000 个打开表。但这是一个软限制。根据 SQL 语句中涉及的表（以及这些表中的分区）的数量，此缓存可能成为主要的内存消耗者。您的工作负载在数据库上运行的并发会话数会放大此影响。
表定义高速缓存存储内存中常用表的表定义（架构、元数据）。活动表越多，缓存其定义效果就越好。在 r4 类 Aurora 数据库实例中，我们已将该缓存的默认大小增加到了 20,000 个定义。因此，此缓存可能成为主要的内存消耗者。对于使用数十万个表的工作负载，如果大多数的表处于活跃状态，则可能需要将此缓存的大小增加到超出默认值。此缓存大小也是一个软限制。MySQL 数据库引擎不会移出父子外键相关表的表定义。所以缓存的总大小可能超过缓存大小限制。
因此，高效的内存利用率实际上会限制可以在具有给定大小的实例的 Aurora 集群上运行的表的数量。为了减少这些缓存占用的空间，您可能需要使用更大的数据库实例类来适应它们。或者，您可能需要减少分配给查询缓存或缓冲池的内存量以进行补偿。这种减少反过来可能会以其他方式影响工作负载性能，因为存放在内存中的工作数据集变少。至于多少表算是“太多”很难界定。 以下示例更好地说明了其中一些影响。
下图显示了通过 sysbench 读取和写入 OLTP 测试的增强监测指标报告的可用内存，该测试包含 1,000 个表，仅使用 40 个并发连接。这个工作负载在兼容 MySQL 5.6 的 Aurora db.r4.2xlarge 数据库实例（内存为 61 GB，是一种受欢迎的实例类）上只运行了 10 分钟。
对于此测试，以下命令在测试运行之前准备数据库和表：
当测试开始时，系统的可用内存突然从超过 12.2 GB 减少 5 GB，并且在整个测试过程中 CPU 资源几乎完全耗尽。与 MySQL 社区版本不同，Aurora 预先分配了缓冲池，并且在之前的测试中它已经预热。我们使用相对较少数量的活动表 (1,000) 和总并发连接 (40) 执行测试。内存消耗主要是由于表缓存和在涉及大量的活动表时每个连接的放大效应。
数据库实例参数组中的 table_open_cache 参数控制表缓存的大小。默认情况下，使用以下公式设置此参数：
为了便于比较，下面的图表代表了类似的测试。唯一的区别是测试只访问了 500 个表。这里，当测试开始时，系统的可用内存也会从超过 12.2 GB 突然下降约 2.5 GB。
以下示例说明了使用大量表时表定义缓存的影响。此测试中的示例工作负载创建 100,000 个简单表，每个表都具有自动递增的整数主键、时间戳列、浮点列和两个短字符串列。测试使用单个连接在每个简单表中插入一行，在具有 15.25 GB 内存的兼容 MySQL 5.7 的 Aurora db.r4.large 数据库实例上运行。Aurora 集群上没有其他活动正在运行，并且集群开始为空。
您可以看到可用内存随工作负载的增加而下降的情况。可用内存在达到缓存限制时稳定，总共消耗大约 700 MB 的内存，主要用于表定义缓存。
数据库实例参数组中的 table_definition_cache 参数控制表定义缓存的大小。默认情况下，使用以下公式设置此参数：
总之，实际可以整合的表的数量取决于几个因素。这些因素包括活动表的数量、可用内存大小以及需要支持的并发连接数。
如何优化以提高数据库资源利用率
在本文的前面部分，我们讨论了大型的表或数量巨大的表对某些服务器资源（如 CPU 或内存）利用率的影响。但工作负载整合本身会导致利用率上升。如果减少了数据库分片数，则为每个剩余的数据库分片建立的并发连接可能会更多。每个整合数据库进行更多工作，读写查询量增加。
Amazon Aurora for MySQL 具有内部服务器连接池和线程多路复用，有助于在处理数千个并发连接时减少争用并提高可扩展性。您可以配置每个 Aurora 数据库实例以允许最多 16,000 个并发连接。但是，您的工作负载和数据库实例类选择所能实现的实际最大值可能会低于此值。
每个连接、会话和线程根据当前运行的特定 SQL 语句为各种缓冲区、缓存和其他内存结构消耗可变数量的内存。这种消耗是不确定的，并且与本文前面讨论的其他结构竞争相同数量的可用内存。要了解有效连接管理和扩展的最佳实践， Amazon Aurora MySQL DBA 连接管理手册是一个很好的资源。它包含有用的建议，可帮助您优化更大、更高吞吐量工作负载的连接利用率。
小结
当您将兼容 MySQL 的 Amazon Aurora 作为整合多个数据库工作负载的解决方案时，需要考虑许多因素。我们在帮助客户实施此类整合的过程中，总结出了前面讨论到的一组常见的注意事项，尽管仍不够详尽。每个工作负载都不同，因此整合的实际限制在每种情况下都不同。
作为最佳实践，您应该在生产规模上彻底测试不同于默认值的配置更改，并且只在对性能和可靠性产生可量化的积极影响时才实施它们。从 MySQL 社区版本引入配置时，这种最佳实践尤其重要，因为 Aurora 的运作方式可能不同。有关如何执行工作负载整合项目的更多详细信息，请参阅 AWS 数据库博客上的帖子 Reduce Resource Consumption by Consolidating Your Sharded System into Aurora。
关于作者
Vlad Vlasceanu 是 Amazon Web Services 的专家级解决方案架构师。 他与客户一起工作，提供有关大数据项目的指导和技术协助，帮助客户在使用 AWS 时提高其解决方案的价值。