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
在某些情况下,当您使用 TempTable
存储引擎时,临时数据可能会超过内存池的大小。如果是这样,Aurora MySQL 会使用辅助机制存储溢出数据。
您可以设置 temptable_max_mmap
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_ram
和 temptable_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 SELECT
或 CREATE 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 存储引擎