在 Amazon RDS for MySQL 和 Amazon Aurora MySQL 上使用 TempTable 存储引擎

MySQL 8.0引入了 TempTable 作为新的默认内部临时表存储引擎，以加快查询处理速度。MySQL 查询优化器在内部创建临时表，以便在处理复杂查询时存储中间数据集。在亚马逊云科技中，您可以使用 **Amazon Relational Database Service ([Amazon RDS](https://aws.amazon.com/cn/rds/?trk=cndc-detail)) for MySQL** 部署 MySQL 8.0，前者是一项针对 MySQL 社区版（CE）的托管服务，可自动执行耗时的管理任务，例如硬件预置、数据库设置、修补和备份。您还可以使用 **[Amazon Aurora](https://aws.amazon.com/cn/rds/aurora/?trk=cndc-detail)** 部署与 MySQL 8.0兼容的数据库集群，前者是一个专为云构建的兼容 MySQL 和 PostgreSQL 的关系数据库，既具有传统企业数据库的性能和可用性，又具有开源数据库的简单性和成本效益。 [Amazon Relational Database Service (Amazon RDS) for MySQL](https://aws.amazon.com/rds/mysql/?trk=cndc-detail) [Amazon Aurora](https://aws.amazon.com/rds/aurora/?trk=cndc-detail) 在本文中，我将分享在 RDS for MySQL 数据库实例和 Aurora MySQL 数据库集群中使用 TempTable 存储引擎的最佳实践。我将讨论在涉及内部临时表并需要分配内存或磁盘存储时影响查询性能的常见问题。此外，还会指出您应该考虑的亚马逊云科技托管式数据库服务的具体注意事项。 ## 01 MySQL 对内部临时表的处理 通常，内部临时表首先保存在内存中，以获得最佳查询性能。为了避免过多内存分配，MySQL 提供您可用于设置内存限制的参数。达到该限制时，内部临时表会溢出到磁盘存储中。MySQL 可以为内存（而不是为磁盘存储）中的内部临时表指定不同的存储引擎。 在8.0版本之前，MySQL 使用 **MEMORY 存储引擎**作为内存内部临时表的默认存储引擎。MySQL 8.0已将其替换为 **TempTable 存储引擎**，但它还提供了用于切换回 MEMORY 存储引擎的选项。 [MEMORY 存储引擎](https://dev.mysql.com/doc/refman/8.0/en/memory-storage-engine.html?trk=cndc-detail) [TempTable 存储引擎](https://dev.mysql.com/doc/refman/8.0/en/internal-temporary-tables.html?trk=cndc-detail) TempTable 存储引擎可管理性的一个关键简化之处在于，它对使用内存池的所有会话都使用内存池。由 **temptable_max_ram** 设置的内存限制适用于并发会话的内存消耗总和。这与 MEMORY 存储引擎不同，在 MEMORY 存储引擎中，由较小的 **tmp_table_size 和 max_heap_table_size** 设置的内存限制基于每个表，特定于会话。TempTable 存储引擎也有自身的磁盘溢出机制。您可以将其配置为溢出到**内存映射的临时文件或 InnoDB 磁盘内部临时表**。**内存映射文件**提供文件与内存空间之间的映射，从而加快文件读取和写入操作。除了内存限制之外，这两个独立的溢出路径还会显露出内部临时表存储引擎的独特之处，并直接影响查询性能。 [temptable_max_ram](https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_temptable_max_ram?trk=cndc-detail) [tmp_table_size](https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_tmp_table_size?trk=cndc-detail) [max_heap_table_size](https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_max_heap_table_size?trk=cndc-detail) [内存映射的临时文件](https://dev.mysql.com/doc/refman/8.0/en/internal-temporary-tables.html#internal-temporary-tables-engines?trk=cndc-detail) [InnoDB 磁盘内部临时表](https://dev.mysql.com/doc/refman/8.0/en/internal-temporary-tables.html#internal-temporary-tables-engines-disk?trk=cndc-detail) [内存映射文件](https://en.wikipedia.org/wiki/Memory-mapped_file?trk=cndc-detail) ## 02 配置参数差异 下图说明了 MySQL **8.0.23** (最新的 **MySQL CE版本**之一) 中 TempTable 存储引擎的四个参数行为。 [8.0.23](https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-23.html?trk=cndc-detail) [MySQL CE 版本](https://dev.mysql.com/doc/relnotes/mysql/8.0/en/?trk=cndc-detail)  具体参数如下： ● **internal_tmp_mem_storage_engine** 为允许的值为 TempTable (默认) 或 MEMORY 的内存内部临时表定义存储引擎。随着 TempTable 存储引擎的推出，在 MySQL **8.0.2** 中添加了此参数。 [internal_tmp_mem_storage_engine](https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_internal_tmp_mem_storage_engine?trk=cndc-detail) [8.0.2](https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-2.html?trk=cndc-detail) ● **temptable_max_ram** 也被引入到了 MySQL **8.0.2** 之中，它定义了 TempTable 存储引擎可以使用的最大内存量。 [temptable_max_ram](https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_temptable_max_ram?trk=cndc-detail) [8.0.2](https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-2.html?trk=cndc-detail) ● **temptable_max_mmap** 被引入到了 MySQL **8.0.23** 之中，它定义了允许 TempTable 存储引擎为内存映射的临时文件分配的最大磁盘存储量。将其设置为0可禁用内存映射的临时文件的使用，从而使溢出指向 InnoDB 磁盘上的内部临时表。 [temptable_max_mmap](https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_temptable_max_mmap?trk=cndc-detail) [8.0.23](https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-23.html?trk=cndc-detail) ● **temptable_use_mmap** 定义了 TempTable 存储引擎是否溢出到内存映射的临时文件。它被引入到了 MySQL **8.0.16** 之中，但在 **8.0.26** 中已弃用，因为它的函数变成了副本。 [temptable_use_mmap](https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_temptable_use_mmap?trk=cndc-detail) [8.0.16](https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-16.html?trk=cndc-detail) [8.0.26](https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-26.html?trk=cndc-detail) 如果您使用 [Amazon RDS](https://aws.amazon.com/cn/rds/?trk=cndc-detail) for MySQL，请记下这些参数的更改历史记录。在 TempTable 存储引擎不断发展的同时，随着时间的推移，已经进行了不少修订。由于 [Amazon RDS](https://aws.amazon.com/cn/rds/?trk=cndc-detail) for MySQL 支持 MySQL 8.0 CE 的各种次要版本，因此每个版本中的可用参数可能会有所不同。您可以检查**数据库参数组**以进行验证。 [数据库参数组](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithParamGroups.html?trk=cndc-detail) Aurora MySQL 3.0 版本与 MySQL 8.0.23 CE 有线兼容。您可以选择在数据库集群参数组或数据库参数组中配置所有这些参数，具体取决于您是否要对 Aurora 数据库集群的所有数据库实例应用统一设置。但是，Aurora 数据库集群的独特存储架构会导致 Aurora 副本（读取器）数据库实例上内部临时表的行为不同。 Aurora 数据库集群有两种类型的存储：所有数据库实例共享的集群卷和附加到每个数据库实例的本地存储。在 Aurora 主（写入程序）数据库实例上，TempTable 存储引擎的溢出路径定义如下： ● 在本地存储上创建内存映射的临时文件 ● InnoDB 磁盘内部临时表存储在共享集群卷中 由于 Aurora 副本数据库实例没有对共享集群卷的写入权限，因此无法使用 InnoDB 磁盘内部临时表。以下限制适用于 Aurora 副本上的参数： ● internal_tmp_mem_storage_engine 在内部设置为 TempTable 存储引擎，无法修改。如果您尝试将其设置为参数组中的 MEMORY 存储引擎，则更改不会生效。 ● temptable_use_mmap 在内部设置为1，无法修改。如果您在参数组中应用其他值，则更改不会生效。 ● temptable_max_mmap 无法设置为0。如果您尝试在参数组中将其设置为0，它将在内部重置为默认值 1GB。 总之，尽管 Aurora 数据库集群的主（写入程序）数据库实例具有与 MySQL 8.0.23 CE 相同的四个可用配置参数，但 Aurora 副本（读取器）数据库实例仅使用其中两个参数：temptable_max_ram 和 temptable_max_mmap，如下图所示。  ## 03 调整策略 内部临时表分配内存或存储（或很可能两者都分配）来处理数据。内部临时表存储引擎所需的资源消耗取决于工作负载。但是，RDS 数据库实例具有基于数据库实例类和存储架构的系统资源容量。好的调整策略需要在系统资源的需求和供应之间取得平衡，以使查询能够扩展并达到预期的性能目标。 [数据库实例类](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Concepts.DBInstanceClass.html?trk=cndc-detail) ### 内存使用情况 处理内存中的数据总是比溢出到磁盘要快。TempTable 存储引擎的内存限制 temptable_max_ram 在 MySQL CE 中默认为 1GB，一般受 [Amazon RDS](https://aws.amazon.com/cn/rds/?trk=cndc-detail) for MySQL 以及 Aurora MySQL 的支持。对于大多数使用**内存优化的数据库实例类**（如 db.r5 或 db.r6g）的数据库实例而言，这是一项保守型设置。您可以根据自己的工作负载调整此参数，并使用公式根据数据库实例 DBInstanceClassMemory 的内存容量调整其大小。这是一个动态参数，您可以在不重启 RDS 实例的情况下对其进行更改。 [内存优化的数据库实例类](https://aws.amazon.com/rds/instance-types/#Memory_Optimized?trk=cndc-detail) 将 temptable_max_ram 设置为较大的值可能会增加内存不足的风险，尤其是在某些内存容量有限的 db.t2 或 db.t3 实例类上。因此，Aurora MySQL 已将 db.t3.medium 和 db.t3.large 实例类的默认值调整为 16MB。建议您监控 **[Amazon CloudWatch](https://aws.amazon.com/cn/cloudwatch/?trk=cndc-detail)** 指标 FreeableMemory，以确保数据库实例上的内存使用量在预期范围内。 [Amazon CloudWatch](http://aws.amazon.com/cloudwatch?trk=cndc-detail) ### 存储使用情况 如果数据太大而无法容纳内存，则溢出到磁盘是允许继续处理查询的替代方法。当使用内部临时表的查询来自高度并发的工作负载或处理大量数据时，通常会发生这种情况。TempTable 存储引擎有两个溢出路径。默认情况下，当达到内存限制时，它会首先溢出到内存映射的临时文件。内存映射的临时文件有存储限制（由 temptable_max_mmap 设置）。当达到该限制时，溢出将沿第二条路径（InnoDB 磁盘内部临时表）向下移动。由于 temptable_max_mmap 是一个动态参数，因此，您可以在不重启实例的情况下对其进行更改。 默认配置有可能使溢出占用两跳。在存储分配和 I/O 操作方面，与使用内存映射的临时文件或 InnoDB 磁盘内部临时表（但不能同时使用两者）的一跳路径相比，两跳路径的成本更高。在 MySQL 中，temptable_max_mmap 的默认值为 1GB。将其保留为如此小的默认值会增加采用两跳路径的可能性。 如果您使用 [Amazon RDS](https://aws.amazon.com/cn/rds/?trk=cndc-detail) for MySQL，我建议尽可能优化以采用单跳路径。两个溢出路径都会导致连接到数据库实例的同一存储。当内存映射的临时文件存储不足时，InnoDB 磁盘内部临时表也会出现同样的情况。如果可用存储允许，我建议增加 temptable_max_mmap，以容纳工作负载可以使用的最大内存映射临时文件。如果您的工作负载偏爱 InnoDB 磁盘内部临时表，则您可以将 temptable_max_mmap 设置为 0，以阻止使用内存映射的临时文件。您可以纵向扩展存储，或调整工作负载以避免占用存储。 在 Aurora 数据库集群的主（写入程序）数据库实例上，分配了两个溢出路径以转到其他存储类型。内存映射的临时文件是在本地存储中创建的。您可以使用 CloudWatch 指标 FreeLocalStorage 来检查 Aurora 数据库实例上可用的免费本地存储，这是您可以设置的 temptable_max_mmap 最大值。InnoDB 磁盘内部临时表存储在具有 128 TiB 容量的集群卷中。单跳路径仍然是最佳选择，您可以选择最适合工作负载的溢出路径。您需要注意的一种情况是，当它驱动 InnoDB 磁盘内部临时表在集群卷上不断增长时，会出现失控查询。我建议配置 **max_execution_time** 来设置查询超时限制，以免出现这种情况。 [max_execution_time](https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_max_execution_time?trk=cndc-detail) 在 Aurora 副本（读取器）数据库实例上，TempTable 是内部临时表的专用存储引擎，溢出路径仅指向本地存储的内存映射临时文件。当内存映射的临时文件大小达到 temptable_max_mmap 设置的存储限制时，查询失败并显示“表已满”错误消息。您的工作负载可能需要主（写入程序）数据库实例与副本（读取器）数据库实例具有不同的临时表配置，您可以选择应用单独的数据库参数组，以确保在实例级别进行细粒度调整。但是，如果副本（读取器）数据库实例是故障转移目标，我建议将主（写入程序）和副本（读取器）数据库实例配置为使用相同实例类和数据库参数组，以免故障转移后可能出现不匹配问题。 ### 表结构 TempTable 存储引擎从表中检索数据，并以自身的格式对其进行操作。要处理的数据量反映了源表的数据结构，例如数据类型和字符集。下图显示了表字符集与分配给不同溢出路径的存储之间的关联示例。 通过在两个 [Amazon RDS](https://aws.amazon.com/cn/rds/?trk=cndc-detail) for MySQL 数据库实例上运行相同工作负载来收集数据。每个实例都有一组大小介于 10-100 GB 之间的 sysbench 表。这两组表之间的唯一区别是，一组使用 latin1 作为 DEFAULT CHARSET，另一组使用 utf8mb4。工作负载包括对 sysbench 表逐个运行单个 GROUP BY 查询。每次运行时，TempTable 存储引擎都会消耗所有可用默认 1GB 内存，并在单跳路径中溢出。  从测试中，您可以观察到以下情况： ● 将使用 utf8mb4 字符集的表与使用 latin1 的表进行比较，无论溢出路径如何，溢出期间分配的存储都有很大的差距 ● 不同的溢出路径也会造成差距，且在使用 utf8mb4 字符集的表上差距往往会更大 ● 在所有溢出情况下，表大小始终可以很好地指示溢出的大小 请注意，**MySQL 8.0** 使用 **utf8mb4** 作为默认字符集。请注意此更改的潜在影响，在为表格选择正确的字符集时要慎重考虑。 ### RDS 存储类型 TempTable 存储引擎利用系统资源的效率也存在差异。下图显示了在不同溢出情况下查询响应时间与 RDS 存储类型之间的关联示例。该实验通过在之前的测试环境中进行两次额外更改来完成。首先，两个 RDS for MySQL 数据库实例使用 latin1 作为 DEFAULT CHARSET 加载同一组 sysbench 表。其次，对于其中一个数据库实例，将 1TB **[Amazon Elastic Block Store](https://aws.amazon.com/cn/ebs/?trk=cndc-detail) ([Amazon EBS](https://aws.amazon.com/cn/ebs/?trk=cndc-detail))** 卷配置为 10000 预置 IOPS (io1)，而另一个使用通用型 SSD (gp2)。请注意，这个 1TB gp2 卷的基准性能为 3000 IOPS。 [Amazon Elastic Block Store](http://aws.amazon.com/ebs?trk=cndc-detail)  从上图中，您可以观察到以下情况： ● 使用内存映射的临时文件后，查询一开始在 io1 和 gp2 卷上以相同的速度运行。随着时间的推移，gp2 卷的突增积分将耗尽，对 io1 卷的查询开始表现出色。 ● 当溢出路径更改为使用 InnoDB 磁盘内部临时表时，无论表大小如何，不同的卷类型都会停止显示对查询响应时间的影响。 ● 对于在 gp2 卷上运行的查询，与内存映射的临时文件相比，InnoDB 磁盘内部临时表显示的查询响应时间可能更短。 如果您使用 [Amazon RDS](https://aws.amazon.com/cn/rds/?trk=cndc-detail) for MySQL 并可以选择 RDS 存储类型，则可以考虑使用高吞吐量磁盘存储来帮助您的工作负载实现最佳性能。Aurora MySQL 数据库实例根据实例类预配置了本地存储容量和 I/O 带宽。您可以纵向扩展到更大的实例类，以获得更多本地存储和每吞吐量 IOPS。调整查询以避免使用 TempTable 存储引擎处理大型数据集总是有益的。 ## 04 监控 CloudWatch 提供了一些指标，可让您全面了解数据库实例级别或数据库集群级别的内存和存储使用情况。以下指标非常有用： ● **[FreeableMemory](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/monitoring-cloudwatch.html?trk=cndc-detail)**（适用于 [Amazon RDS](https://aws.amazon.com/cn/rds/?trk=cndc-detail) for MySQL 和 Aurora MySQL）： ● [**FreeStorageSpace**](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/monitoring-cloudwatch.html?trk=cndc-detail)（适用于 [Amazon RDS](https://aws.amazon.com/cn/rds/?trk=cndc-detail) for MySQL）： ● **FreeLocalStorage**、**VolumeBytesUsed** 和 **AuroraVolumeBytesLeftTotal**（适用于 Aurora MySQL） [FreeLocalStorage](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Aurora.AuroraMySQL.Monitoring.Metrics.html?trk=cndc-detail) [VolumeBytesUsed](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Aurora.AuroraMySQL.Monitoring.Metrics.html?trk=cndc-detail) [AuroraVolumeBytesLeftTotal](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Aurora.AuroraMySQL.Monitoring.Metrics.html?trk=cndc-detail) 如果您为数据库实例启用 **[Amazon RDS](https://aws.amazon.com/cn/rds/?trk=cndc-detail) Performance Insights**，还可以查询 MySQL 的性能架构来监控 TempTable 存储引擎使用的内存和存储。您可以使用以下 SQL 查询从性能架构中获得更多见解： [Amazon RDS Performance Insights](https://aws.amazon.com/rds/performance-insights/?trk=cndc-detail) ```js -- SQL query to check the memory and storage usage of the TempTable storage engine at the DB instance level SELECT event_name, sum_number_of_bytes_alloc FROM performance_schema.memory_summary_global_by_event_name WHERE event_name like 'memory/temptable%'; +--------------------------------+---------------------------+ | event_name | sum_number_of_bytes_alloc | +--------------------------------+---------------------------+ | memory/temptable/physical_disk | 0 | | memory/temptable/physical_ram | 540016640 | +--------------------------------+---------------------------+ -- SQL query to check the size of memory-mapped temporary files at the session level SELECT event_name, sum_number_of_bytes_alloc FROM performance_schema.memory_summary_by_thread_by_event_name WHERE thread_id=( SELECT THREAD_ID FROM performance_schema.threads WHERE PROCESSLIST_ID=90) ORDER BY sum_number_of_bytes_alloc desc limit 5; +---------------------------------------+---------------------------+ | event_name | sum_number_of_bytes_alloc | +---------------------------------------+---------------------------+ | memory/temptable/physical_disk | 2684354560 | | memory/sql/thd::main_mem_root | 1182416 | | memory/mysqld_openssl/openssl_malloc | 150668 | | memory/sql/Filesort_buffer::sort_keys | 65536 | | memory/sql/String::value | 34936 | +---------------------------------------+---------------------------+ -- SQL query to check the size of the InnoDB internal temporary tables at the DB instance level SELECT * FROM INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACES; +----+------------+----------------------------+------------+----------+-----------+ | ID | SPACE | PATH | SIZE | STATE | PURPOSE | +----+------------+----------------------------+------------+----------+-----------+ | 69 | 4243767290 | ./#innodb_temp/temp_10.ibt | 163840 | ACTIVE | INTRINSIC | | 68 | 4243767289 | ./#innodb_temp/temp_9.ibt | 163840 | ACTIVE | INTRINSIC | | 81 | 4243767287 | ./#innodb_temp/temp_7.ibt | 81920 | ACTIVE | INTRINSIC | | 92 | 4243767288 | ./#innodb_temp/temp_8.ibt | 4714397696 | ACTIVE | INTRINSIC | | 0 | 4243767281 | ./#innodb_temp/temp_1.ibt | 81920 | INACTIVE | NONE | | 0 | 4243767282 | ./#innodb_temp/temp_2.ibt | 81920 | INACTIVE | NONE | | 0 | 4243767283 | ./#innodb_temp/temp_3.ibt | 81920 | INACTIVE | NONE | | 0 | 4243767284 | ./#innodb_temp/temp_4.ibt | 81920 | INACTIVE | NONE | | 0 | 4243767285 | ./#innodb_temp/temp_5.ibt | 81920 | INACTIVE | NONE | | 0 | 4243767286 | ./#innodb_temp/temp_6.ibt | 81920 | INACTIVE | NONE | +----+------------+----------------------------+------------+----------+-----------+ ``` 左滑查看更多 ## 05 总结 在本文中，我们介绍了为 RDS for MySQL 数据库实例或 Aurora MySQL 数据库集群配置 TempTable 存储引擎以获得最佳查询性能的一些最佳实践。我希望本文能让您更好地了解 MySQL 8.0 中的这项新功能，并使其更易于与 Amazon 托管式数据库服务配合使用。 ### 本篇作者 **Lei Zeng** 亚马逊云科技的一名高级数据库工程师。