亚马逊AWS官方博客
Amazon Aurora MySQL 数据库配置的最佳实践
在 AWS 云中迁移或启动新的 Amazon Aurora MySQL 实例后，您是否问过自己类似以下的问题？
- “接下来要做什么？ 如何让它以最佳方式运行？”
- “是否应该修改任何现有参数？”
- “我应该修改哪些参数？”
如果您问过这样的问题，我希望这篇博文可以就应做事项（以及不应做事项）提供一些指导。
在本文中，我将讨论、阐明并提供有关 Amazon Aurora 的 MySQL 兼容性配置参数的建议。此等数据库参数及其值对于在 AWS 云中传递、调整或重新配置新创建或迁移的实例非常重要。我还会讨论哪些参数会从 Amazon RDS for MySQL 实例传递到 Aurora 实例。相关内容将涉及哪些是默认值，以及哪些参数对于实例的稳定性和最佳性能至关重要。
更改参数之前最重要的考量因素是了解更改背后的需求和动机。 虽然大多数参数设置都可以使用默认值，但是应用程序工作负载的变化可能会导致这些参数需要调整。在进行任何更改之前，请先问自己以下问题：
- 我是否遇到稳定性问题，例如重启或故障转移？
- 我能让我的应用程序更快地运行查询吗？
Aurora 参数组快速入门
Aurora MySQL 参数组有两种类型：数据库参数组和数据库集群参数组。一些参数会影响整个数据库集群的配置，如 binlog 格式、时区和默认字符集。另一些参数将其作用范围限制为单个数据库实例。
在这篇博文中，我会从另一种意义上将其做以分类：哪些参数会影响您 Aurora 集群的行为、稳定性和功能，以及哪些参数会在修改时影响性能。
请记住，两种参数都是预设默认值的即用式参数，有些参数允许修改。
如果想要回顾和深入了解修改和使用参数组的基础知识，请参阅 Aurora 用户指南中的以下主题：
在更改生产数据库之前
参数更改可能会产生意外结果，包括性能下降和系统不稳定。在更改任何数据库配置参数之前，请遵循以下最佳做法：
- 通过创建克隆或恢复生产实例的快照在测试环境中进行更改（如文档中所述）。这样，您的设置将最大程度地贴近生产环境。
- 模拟生产工作负载为您的测试实例生成工作负载。
- 检查系统性能的关键性能指标，如 CPU 使用率、数据库连接数、内存使用率、缓存命中率和查询吞吐量，还有延迟。在更改前执行此检查以获得基线数值，更改后再次检查以观察结果。
- 一次只更改一个参数，避免混乱。
- 如果更改对测试系统没有产生可测量的影响，请考虑将参数恢复为默认值。
- 记录哪个参数具有您期望的积极影响以及哪些关键绩效指标显示出改进。
默认参数值及其重要性
某些数据库实例参数包含变量或公式，其中值由常量决定。这包括实例的大小和内存占用、实例的网络端口及其分配的存储空间。此类参数最好保持不变，因为当执行实例放大或缩小操作时它们会自动调整。
例如，Aurora DB 参数 innodb_buffer_pool_size 的默认值为：
DBInstanceClassMemory 是一个变量，表示实例的内存大小 (GiB)。
举例来说：对于具有 30.5 GiB 内存的 db.r4.xlarge 实例，此值为 20,090,716,160 字节或 18.71 GiB。
假设我们决定将此参数设置为固定值，比如设置为 18,000,000,000 字节，之后我们对db.r4.large 执行缩减操作，将内存减半（15.2 GiB）。在进行此改动后，我们很可能在数据库引擎上遇到内存不足的情况，并且实例无法正确启动。
要快速浏览系统变量自动计算的参数，可以在参数组定义中搜索这些参数。为此，请搜索大括号字符“{”。
如果要查询实例使用的实际值，可以通过两种方式在命令行中实现。即分别使用 SHOW GLOBAL VARIABLES 或 SELECT 语句：
参数值设置错误的症状和诊断
当某些参数配置错误时，可能会在 MySQL 错误日志中以内存不足的记录体现出来。在这种情况下，实例进入滚动重启状态并生成类似于以下内容的事件日志，且会指明需要调整的参数值：
参数分类
针对本文讨论的内容，我们可以将 Aurora MySQL 参数分为两大类：
- 控制数据库行为和功能但不影响资源利用率和实例稳定性的参数
- 通过管理实例中资源（如缓存和基于内部内存的缓冲区）的分配方式而可能影响性能的参数
让我们看一下其中的一些参数、它们的默认值以及它们在变动时如何影响实例的行为或性能。下表描述了在参数组中会遇到的参数名称、Aurora 和 MySQL 的默认值以及修改此参数会影响到的功能的摘要。
参数名称 |
影响 |
Aurora 默认值 |
MySQL 5.6/5.7 值 |
参数描述 |
| autocommit | 功能 | 1 (ON) | 1 (ON) | 启用时，会自动向磁盘提交事务。如果禁用，多语句事务必须要显式地启动并提交或回滚。如果未显式启动事务，则会自动提交每个成功语句。 |
| max_connections | 功能 | {Variable} | {Variable} | 限制数据库并发连接的最大数量。 |
| max_allowed_packet | 功能 | 4194304（字节） | 4194304（字节） | 服务器可接收包的最大大小。 |
| group_concat_max_len | 功能 | 1024（字节） | 1024（字节） | 服务器返回的 GROUP_CONCAT() 函数的最大结果长度。 |
| innodb_ft_result_cache_limit | 功能 | 2000000000（字节） | 2000000000（字节） | 限制 InnoDB 全文搜索查询结果的缓存大小。 |
| max_heap_table_size | 功能 | 16777216 （字节） |
16777216 （字节） |
限制用户新定义的 MEMORY 表的大小。不会限制已有表的大小。 |
| performance_schema | 功能 | OFF | OFF | 启用或禁用 MySQL 性能架构。 |
| binlog_cache_size | 性能 | 32768 （字节） |
32768（字节） | 控制二进制日志缓存大小，增加其值可改善处理大事务的系统的性能。在具有大量数据库连接的环境中应限制该值。 |
| bulk_insert_buffer_size | 性能 | – | – | 控制 MyISAM 缓存大小以加速批量插入操作。不用于 Aurora MySQL。 |
| innodb_buffer_pool_size | 性能 | {Variable} 实例内存的 3/4 |
134217728 (128 MB) |
控制缓存表和索引数据的 InnoDB 缓冲池的内存大小。 |
| innodb_sort_buffer_size | 性能 | 1048576 （字节） |
1048576 （字节） |
定义在写入磁盘之前，将多少数据读取到内存进行排序操作。 |
| join_buffer_size | 性能 | 262144 （字节） |
262144 （字节） |
未索引的连接、索引以及范围扫描的最小缓冲区大小。 |
| key_buffer_size | 性能 | 16777216 （字节） |
8388608 （字节） |
MyISAM 表的键缓存。不用于 Aurora。 |
| myisam_sort_buffer_size | 性能 | 8388608 （字节） |
8388608 （字节） |
MyISAM 索引缓冲区。不用于 Aurora。 |
| query_cache_size | 性能 | {Variable} 1/24 内存大小 | 1048576 （字节） |
缓存结果集的预留内存量，为 1,024 的倍数。 |
| query_cache_type | 性能 | 1 | 0 | 启用或禁用查询缓存。 |
| read_buffer_size | 性能 | 262144 （字节） |
262144 （字节） |
控制多种类型缓冲区的内存分配，例如为ORDER BY 子句、分区插入和嵌套查询排序行时。 |
| read_rnd_buffer_size | 性能 | 524288 （字节） |
262144 （字节） |
改善使用多范围读取查询的系统的性能。 |
| table_open_cache | 性能 | 6000 | 2000 | 限制为所有线程在内存中打开的表数量。 |
| table_definition_cache | 性能 | 20000 | {Variable} 小于 2000 | 限制不使用文件描述符存储在缓存中的表定义的数量。 |
| tmp_table_size | 性能 | 16777216 （字节） |
16777216 （字节） |
限制引擎内部内存中临时表的大小。 |
建议和影响
以下是关于每个关键参数如何影响数据库的简要说明，以及有关如何调整它们的一些用例：
autocommit
推荐设置：使用默认值（1 或 ON）确保每个SQL 语句在运行时自动提交，除非它是用户打开的事务的一部分。
影响：值为 OFF 可能会导致不正确的使用模式，例如事务保持打开时间超过要求、事务未关闭或根本未提交。这可能会影响数据库的性能和稳定性。
max_connections
推荐设置：默认（变量值）。使用自定义值时，仅按照应用程序主动用于执行工作的连接数进行配置。
影响：即使没有主动使用连接，配置过高的连接限制也会导致过高的内存使用量。它还可能导致高数据库连接峰值，进而影响数据库的性能和稳定性。
此变量参数使用以下公式根据实例的内存分配和大小自动填充，因此请首先使用默认值：
例如，对于 15.25 GiB 内存的 Aurora MySQL db.r4.large 实例，该值设置为 1,000：
如果在错误日志中遇到连接错误并且有大量 Too many connections 消息，则可以将此参数设置为固定值而不是变量设置。
当您考虑将 max_connections 设置为固定值时，如果您的应用程序需要更多的连接，请考虑在应用程序和数据库之间使用连接池或代理。如果无法可靠地预测或控制连接，也可以执行此操作。
手动配置此参数的值超过建议的连接数时，数据库连接的 Amazon CloudWatch 指标会在超出阈值处显示一条红线。这是 CloudWatch 使用的公式：
例如，对于内存大小为 15.25 GiB（15.25 x 1024 x 1024 x 1024 = 16374562816 字节）的 db.r4.large 实例，警告阈值大约为 1,300 个连接。您仍然可以使用最大数量的配置连接，只要实例上有足够的资源。
max_allowed_packet
推荐设置：默认值（4,194,304 字节）。仅在数据库工作负载需要时使用自定义值。在处理返回大型元素（如长字符串或 BLOB）的查询时，请调整此参数。
影响：在此处设置较大的值不会影响消息缓冲区的初始大小。相反，如果查询需要，它允许缓冲区增大到定义的大小。大参数值配上符合条件的大量并发查询，会增加内存不足的风险。
将此参数设置得太小时，会显示以下示例错误：
group_concat_max_len
推荐设置：默认值（1,024 字节）。仅在工作负载需要时才使用自定义值。仅当您想要更改 GROUP_CONCAT() 语句的返回值并允许引擎返回更长的列值时，才需要调整此参数。此值应与 max_allowed_packet 一同使用，因为这将确定响应的最大大小。
影响：将此参数设置得过高的一些症状是高内存使用量和内存不足。将其设置得太低会导致查询失败。
innodb_ft_result_cache_limit
推荐设置：默认值（2,000,000,000 字节）。根据您的工作负载使用自定义值。
影响：由于该值已接近 1.9 GiB，将从其默认值调高可能会导致内存不足。
max_heap_table_size
推荐设置：默认值（16,777,216 字节）。限制用户定义的在内存中创建的表的最大大小。 更改此值仅对新创建的表有影响，不会影响现有表。
影响：如果内存中表增多，则将此参数设置得太高会导致内存使用量过大或内存不足。
performance_schema
推荐设置：由于内存使用率高，t2 实例禁用。
影响：在 Aurora MySQL 5.6 中，性能架构内存是启发式预分配的。此预分配基于其他配置参数，如 max_connections、table_open_cache 和 table_definition_cache。在 Aurora MySQL 5.7 中，性能架构内存按需分配。性能架构通常使用约 1 到 3 GB 的内存，具体取决于实例类、工作负载和数据库配置。如果数据库实例内存不足，启用性能架构可能会导致内存耗尽。
binlog_cache_size
推荐设置：默认值（32,768 字节）此参数控制二进制日志缓存可以使用的内存量。增加该值可以使用缓冲区来避免过量的磁盘写入，提高具有大事务的系统的性能。此缓存按连接分配。
影响：在具有大量数据库连接的环境中限制此值，以避免内存不足的情况。
bulk_insert_buffer_size
推荐设置：保持原样，因为它不适用于 Aurora MySQL。
innodb_buffer_pool_size
推荐设置：默认（变量值），因为它在 Aurora 中预配置为实例内存大小的 75％。您可以在 SHOW ENGINE INNODB STATUS 的输出中看到缓冲池使用量。
影响：较大的缓冲池通过在重复访问相同的表数据时允许较少的磁盘 I/O 来提高整体性能。加上 InnoDB 引擎的开销，实际分配的内存量可能略高于实际配置的值。
innodb_sort_buffer_size
推荐设置：默认值（1,048,576 字节）
影响：高于默认值会增加具有大量并发查询的系统的总体内存压力
join_buffer_size
推荐设置：默认值（262,144 字节）。此值预先分配用于各种类型的操作（例如连接），单个查询可以分配此缓冲区的多个实例。如果要改善连接的性能，我们建议您为这些表添加索引。
影响：更改此参数可能会在具有大量并发查询的环境中导致严重的内存压力。即使添加索引，增加此值也不会提供更快的 JOIN 查询性能。
key_buffer_size
建议设置：保留默认值（16,777,216 字节），因为它与 Aurora 无关并且仅影响 MyISAM 表性能。
影响：对 Aurora 的性能没有影响。
myisam_sort_buffer_size
推荐设置：保留默认值（8,388,608 字节）。它不适用于 Aurora，因为它对 InnoDB 没有影响。
影响：对 Aurora 的性能没有影响。
query_cache_size
推荐设置：默认（变量值）。该参数在 Aurora 中进行了预调整，并且该值远大于 MySQL 默认值。Aurora 的查询缓存不会受到扩展性问题的拖累（如 MySQL 中的查询缓存一样）。可以修改它以满足高吞吐量、高要求的工作负载。
影响：通过此缓存访问查询时，查询性能会受到影响。您可以在“QCache”部分下的 SHOW STATUS 命令的输出中看到查询缓存的使用情况。
query_cache_type
推荐设置：启用。默认情况下，在 Aurora 中启用查询缓存，建议将其保持启用以提高性能，降低开销。但是，如果您知道工作负载不会从中受益，则可以禁用查询缓存。一个例子是工作负载中写入操作繁重，但很少或没有读取查询的情况。
影响：如果工作负载复用查询（如可重复的 SQL 语句），则在 Aurora 中禁用查询缓存可能会影响数据库性能。您可以在“Qcache”部分下的 SHOW STATUS 命令的输出中看到查询缓存的使用情况。
read_buffer_size
推荐设置：默认值（262,144 字节）。
影响：较大的值会导致较高的总体内存压力并引发内存不足问题。除非您能够证明较高的值可以在不影响稳定性的情况下提高性能，否则请不要提高此设置。
read_rnd_buffer_size
推荐设置：默认值（524,288 字节）。由于底层存储集群的性能特征，Aurora 无需增加该设置。
影响：较大的值可能会导致内存不足问题。
table_open_cache
建议设置：保持不变，除非您的工作负载需要同时访问大量表。表缓存是主要的内存使用者，Aurora 中的默认值明显高于 MySQL 默认值。此参数基于实例大小自动调整。
影响：具有大量表（数十万级别）的数据库需要更大的设置，因为并非所有表都适合内存。此值设置太高可能会导致内存不足。如果启用了性能架构，此设置也会间接提高性能架构内存使用量。
table_definition_cache
推荐设置：默认值。此设置在 Aurora 中被预先调整为远大于 MySQL，并且它会根据实例大小和类自动调整。如果您的工作负载需要它，并且您的数据库需要同时打开大量表，增加此值可能会加快打开表操作。此参数与 table_open_cache 一起使用。
影响：如果启用了性能架构，此设置也会间接增加性能架构内存使用量。请注意高于默认值的设置，因为它们可能会引发内存不足问题。
tmp_table_size
推荐设置：默认值（16,777,216 字节）。此参数与 max_heap_table_size 一起用于限制查询处理所使用的内存表的大小。当超出临时表大小限制时，表将交换到磁盘。
影响：非常大的值（数百 MB 或更大）众所周知或会引起内存问题和内存不足错误。此参数不会影响使用 MEMORY 引擎创建的表。
结论和关键要点
在部署新的 Aurora MySQL 实例时，许多参数已经过优化，在执行任何参数更改之前，它们都是一个很好的基准。各参数值的确切组合在很大程度上取决于各个系统、应用程序工作负载和所需的吞吐量特性。此外，在具有高变化率、增长率、数据提取率和动态工作负载的数据库系统上，这些参数还需要持续监控和评估。随着您基于应用程序和业务需求不断调整数据库，我们建议您每隔几个月（可能每隔几周）进行一次监控和评估。
为了执行成功的参数调整，将之转化为可量度的性能提升，您最好进行实验，建立基线并比较执行更改后的结果。我们建议您在将更改提交到实时生产系统之前执行此操作。
如果您想了解有关特定参数的更多信息，请联系 AWS Support 或您指定的 AWS 技术账户团队。