Aurora MySQL 版本 3 中的新临时表行为 - Amazon Aurora
Amazon Web Services 文档中描述的 Amazon Web Services 服务或功能可能因区域而异。要查看适用于中国区域的差异,请参阅 中国的 Amazon Web Services 服务入门 (PDF)

Aurora MySQL 版本 3 中的新临时表行为

Aurora MySQL 版本 3 处理临时表的方式与早期的 Aurora MySQL 版本不同。这种新行为继承自 MySQL 8.0 社群版。使用 Aurora MySQL 版本 3 可以创建两种类型的临时表:

  • 内部(或隐式)临时表 – 由 Aurora MySQL 引擎创建,以处理诸如对聚合、派生表或公用表表达式 (CTE) 进行排序等操作。

  • 用户创建的(或显式)临时表 – 使用 CREATE TEMPORARY TABLE 语句时由 Aurora MySQL 引擎创建。

对于 Aurora 读取器数据库实例上的内部和用户创建的临时表,还有其他注意事项。我们将在以下各节中讨论这些变化。

内部(隐式)临时表的存储引擎

在生成中间结果集时,Aurora MySQL 最初尝试写入内存中临时表。这可能不成功,原因是数据类型不兼容或配置了限制。如果是这样,临时表将转换为磁盘上的临时表,而不是保留在内存中。有关这方面的更多信息,请参阅 MySQL 文档中的在 MySQL 中使用内部临时表

在 Aurora MySQL 版本 3 中,内部临时表的工作方式与早期的 Aurora MySQL 版本不同。现在,您不必在 InnoDB 与 MyISAM 存储引擎之间选择此类临时表,而是在 TempTable 与 InnoDB 存储引擎之间选择。

使用 TempTable 存储引擎,您可以另外选择如何处理某些数据。受影响的数据溢出了保存数据库实例的所有内部临时表的内存池。

这些选择可能会影响生成大量临时数据的查询的性能,例如,在大型表上执行诸如 GROUP BY 之类的聚合操作。

提示

如果您的工作负载包括生成内部临时表的查询,请通过运行基准测试和监控与性能相关的指标来确认应用程序如何执行此更改。

在某些情况下,临时数据量适合 TempTable 内存池或者只有少量溢出内存池。在这些情况下,我们建议将 TempTable 设置用于内部临时表和内存映射文件以保存任何溢出数据。此设置是原定设置。

TempTable 存储引擎为原定设置引擎。TempTable 对使用此引擎的所有临时表使用公用内存池,而不是每个表的最大内存限制。此内存池的大小由 temptable_max_ram 参数指定。对于具有 16 GiB 或更多内存的数据库实例,其原定设置为 1 GiB;而对于内存小于 16 GiB 的数据库实例,其原定设置为 16 MB。内存池的大小会影响会话级别的内存消耗。

在某些情况下,当您使用 TempTable 存储引擎时,临时数据可能会超过内存池的大小。如果是这样,Aurora MySQL 会使用辅助机制存储溢出数据。

您可以设置 temptable_max_mmap 参数来指定数据是溢出到内存映射的临时文件,还是磁盘上的 InnoDB 内部临时表。这些溢出机制的不同数据格式和溢出标准可能会影响查询性能。他们通过影响写入磁盘的数据量和对磁盘存储吞吐量的需求来实现这一目标。

Aurora MySQL 存储溢出数据的方式有所不同,具体取决于若干注意事项。包括您选择的数据溢出目标,以及查询是在写入器还是读取器数据库实例上运行:

  • 在写入器实例上,溢出到 InnoDB 内部临时表的数据存储在 Aurora 集群卷中。

  • 在写入器实例上,溢出到内存映射临时文件的数据驻留在 Aurora MySQL 版本 3 实例上的本地存储中。

  • 在读取器实例上,溢出数据始终驻留在本地存储上的内存映射临时文件上。这是因为只读实例无法在 Aurora 集群卷上存储任何数据。

注意

与内部临时表相关的配置参数对集群中的写入器和读取器实例的应用方式不同。对于读取器实例,Aurora MySQL 始终将 TempTable 存储引擎和值 1 用于 temptable_use_mmap。对于写入器和读取器实例,temptable_max_mmap 的大小都原定设置为 1 GiB,无论数据库实例内存大小如何。您可以像在写入器实例上那样调整此值。但是,您不能在读取器实例上为 temptable_max_mmap 指定零值。

缓解 Aurora 副本上内部临时表的完整性问题

为避免临时表的大小限制问题,请将 temptable_max_ramtemptable_max_mmap 参数设置为可以满足工作负载要求的组合值。

设置 temptable_max_ram 参数的值时要小心。将该值设置为过高会减少数据库实例上的可用内存,这可能会导致内存不足的情况。监控数据库实例上的平均可用内存。然后,确定 temptable_max_ram 的适当值,以便实例上仍剩余合理的可用内存量。有关更多信息,请参阅Amazon Aurora 中的可用内存问题

监控本地存储的大小和临时表空间占用情况也很重要。有关监控实例上的本地存储的更多信息,请参阅 Amazon 知识中心文章 Aurora MySQL 兼容的本地存储中存储了什么,如何解决本地存储问题?

例 1

您知道临时表增长到 20 GiB 的累计大小。您希望将内存中的临时表设置为 2 GiB,而在磁盘上增长到最大 20 GiB。

temptable_max_ram 设置为 2,147,483,648,将 temptable_max_mmap 设置为 21,474,836,480。这些值以字节为单位。

这些参数设置可确保临时表可以增长到累计 22 GiB 的总大小。

例 2

您当前的实例大小为 16xlarge 或更大。您不知道可能需要的临时表的总大小。您希望能够在内存中使用多达 4 GiB,并在磁盘上使用最大的可用存储大小。

temptable_max_ram 设置为 4,294,967,296,将 temptable_max_mmap 设置为 1,099,511,627,776。这些值以字节为单位。

在此,您将 temptable_max_mmap 设置为 1 TiB,这小于 16xlarge Aurora 数据库实例上的最大本地存储 1.2 TiB。

在较小的实例大小上,调整 temptable_max_mmap 的值,以使它不会填满可用的本地存储。例如,2xlarge 实例只有 160 GiB 的本地存储可用。因此,我们建议将该值设置为小于 160 GiB。有关数据库实例大小的可用本地存储的更多信息,请参阅 Aurora MySQL 的临时存储限制

读取器数据库实例上用户创建的(显式)临时表

您可以在 CREATE TABLE 语句中使用 TEMPORARY 关键字创建显式临时表。Aurora 数据库集群中的写入器数据库实例支持显式临时表。您还可以在读取器数据库实例上使用显式临时表,但这些表无法强制使用 InnoDB 存储引擎。

为避免在 Aurora MySQL 读取器数据库实例上创建显式临时表时出现错误,请确保按以下任一或两种方式运行所有 CREATE TEMPORARY TABLE 语句:

  • 不要指定 ENGINE=InnoDB 子句。

  • 请勿将 SQL 模式设置为 NO_ENGINE_SUBSTITUTION

临时表创建错误和缓解

您收到的错误会有所不同,具体取决于您使用的是简单 CREATE TEMPORARY TABLE 语句还是变体 CREATE TEMPORARY TABLE AS SELECT。以下示例显示不同类型的错误。

此临时表行为仅适用于只读实例。第一个示例证实了这是会话连接到的实例类型。

mysql> select @@innodb_read_only; +--------------------+ | @@innodb_read_only | +--------------------+ | 1 | +--------------------+

对于简单的 CREATE TEMPORARY TABLE 语句,语句会在 NO_ENGINE_SUBSTITUTION SQL 模式开启时失败。当 NO_ENGINE_SUBSTITUTION 处于关闭状态(原定设置)时,将进行适当的引擎替换,并成功创建临时表。

mysql> set sql_mode = 'NO_ENGINE_SUBSTITUTION'; mysql> CREATE TEMPORARY TABLE tt2 (id int) ENGINE=InnoDB; ERROR 3161 (HY000): Storage engine InnoDB is disabled (Table creation is disallowed). mysql> SET sql_mode = ''; mysql> CREATE TEMPORARY TABLE tt4 (id int) ENGINE=InnoDB; mysql> SHOW CREATE TABLE tt4\G *************************** 1. row *************************** Table: tt4 Create Table: CREATE TEMPORARY TABLE `tt4` ( `id` int DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

对于 CREATE TEMPORARY TABLE AS SELECT 语句,无论 NO_ENGINE_SUBSTITUTION SQL 模式是否开启,语句都将失败。MySQL 社群版不支持使用 CREATE TABLE AS SELECTCREATE TEMPORARY TABLE AS SELECT 语句替换存储引擎。对于这些语句,请从 SQL 代码中删除 ENGINE=InnoDB 子句。

mysql> set sql_mode = 'NO_ENGINE_SUBSTITUTION'; mysql> CREATE TEMPORARY TABLE tt1 ENGINE=InnoDB AS SELECT * FROM t1; ERROR 3161 (HY000): Storage engine InnoDB is disabled (Table creation is disallowed). mysql> SET sql_mode = ''; mysql> CREATE TEMPORARY TABLE tt3 ENGINE=InnoDB AS SELECT * FROM t1; ERROR 1874 (HY000): InnoDB is in read only mode.

有关 Aurora MySQL 版本 3 中临时表的存储方面和性能影响的更多信息,请参阅博客文章在 Amazon RDS for MySQL 和 Amazon Aurora MySQL 上使用 TempTable 存储引擎