内部（隐式）临时表的存储引擎限制内部内存中临时表的大小缓解 Aurora 副本上内部临时表的完整性问题读取器数据库实例上用户创建的（显式）临时表临时表创建错误和缓解

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 与 MEMORY 存储引擎之间进行选择。

使用 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 存储引擎。
对于写入器和读取器实例，temptable_max_mmap 的大小都原定设置为 1GiB，无论数据库实例内存大小如何。可以在写入器实例和读取器实例上调整此值。
将 temptable_max_mmap 设置为 0 可禁止在写入器实例上使用内存映射的临时文件。
您无法在读取器实例上将 temptable_max_mmap 设置为 0。

注意

建议您不要使用 temptable_use_mmap 参数。它已被弃用，预计将在将来的的 MySQL 版本中删除对它的支持。

限制内部内存中临时表的大小

如内部（隐式）临时表的存储引擎中所述，可以通过使用 temptable_max_ram 和 temptable_max_mmap 设置来全局控制临时表资源。

还可以使用 tmp_table_size 数据库参数，来限制任何单个内部内存中临时表的大小。此限制旨在防止各个查询消耗过量的全局临时表资源，这可能会影响需要这些资源的并发查询的性能。

tmp_table_size 参数定义了 Aurora MySQL 版本 3 中由 MEMORY 存储引擎创建的临时表的最大大小。

在 Aurora MySQL 版本 3.04 及更高版本中，tmp_table_size 还定义了当 aurora_tmptable_enable_per_table_limit 数据库参数设置为 ON 时，TempTable 存储引擎创建的临时表的最大大小。原定设置情况下，此行为处于禁用状态（OFF），这与 Aurora MySQL 版本 3.03 及更低版本中的行为相同。

当 aurora_tmptable_enable_per_table_limit 为 OFF 时，不考虑将 tmp_table_size 用于由 TempTable 存储引擎创建的内部内存中临时表。

但是，全局 TempTable 资源限制仍然适用。当达到全局 TempTable 资源限制时，Aurora MySQL 会出现以下行为：
- 写入器数据库实例 – Aurora MySQL 自动将内存中临时表转换为 InnoDB 磁盘上临时表。
- 写入器数据库实例 - 查询结束时出现错误。
```
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlxx_xxx' is full
```
当 aurora_tmptable_enable_per_table_limit 为 ON 时，如果达到 tmp_table_size 限制，Aurora MySQL 会出现以下行为：
- 写入器数据库实例 – Aurora MySQL 自动将内存中临时表转换为 InnoDB 磁盘上临时表。
- 写入器数据库实例 - 查询结束时出现错误。
```
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlxx_xxx' is full
```
  全局 TempTable 资源限制和每个表的限制都适用于这种情况。

注意

当 internal_tmp_mem_storage_engine 设置为 MEMORY 时，aurora_tmptable_enable_per_table_limit 参数不起作用。在这种情况下，内存中临时表的最大大小由 tmp_table_size 或 max_heap_table_size 值定义，以较小者为准。

以下示例显示了写入器和读取器数据库实例的 aurora_tmptable_enable_per_table_limit 参数的行为。

例 `aurora_tmptable_enable_per_table_limit` 设置为 `OFF` 的写入器数据库实例

内存中临时表未转换为 InnoDB 磁盘上临时表。


mysql> set aurora_tmptable_enable_per_table_limit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@innodb_read_only,@@aurora_version,@@aurora_tmptable_enable_per_table_limit,@@temptable_max_ram,@@temptable_max_mmap;
+--------------------+------------------+------------------------------------------+---------------------+----------------------+
| @@innodb_read_only | @@aurora_version | @@aurora_tmptable_enable_per_table_limit | @@temptable_max_ram | @@temptable_max_mmap |
+--------------------+------------------+------------------------------------------+---------------------+----------------------+
|                  0 | 3.04.0           |                                        0 |          1073741824 |           1073741824 |
+--------------------+------------------+------------------------------------------+---------------------+----------------------+
1 row in set (0.00 sec)

mysql> show status like '%created_tmp_disk%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
+-------------------------+-------+
1 row in set (0.00 sec)

mysql> set cte_max_recursion_depth=4294967295;
Query OK, 0 rows affected (0.00 sec)

mysql> WITH RECURSIVE cte (n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM cte WHERE n < 60000000) SELECT max(n) FROM cte;
+----------+
| max(n)   |
+----------+
| 60000000 |
+----------+
1 row in set (13.99 sec)

mysql> show status like '%created_tmp_disk%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
+-------------------------+-------+
1 row in set (0.00 sec)

例 `aurora_tmptable_enable_per_table_limit` 设置为 `ON` 的写入器数据库实例

内存中临时表转换为 InnoDB 磁盘上临时表。


mysql> set aurora_tmptable_enable_per_table_limit=1;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@innodb_read_only,@@aurora_version,@@aurora_tmptable_enable_per_table_limit,@@tmp_table_size;
+--------------------+------------------+------------------------------------------+------------------+
| @@innodb_read_only | @@aurora_version | @@aurora_tmptable_enable_per_table_limit | @@tmp_table_size |
+--------------------+------------------+------------------------------------------+------------------+
|                  0 | 3.04.0           |                                        1 |         16777216 |
+--------------------+------------------+------------------------------------------+------------------+
1 row in set (0.00 sec)

mysql> set cte_max_recursion_depth=4294967295;
Query OK, 0 rows affected (0.00 sec)

mysql> show status like '%created_tmp_disk%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
+-------------------------+-------+
1 row in set (0.00 sec)

mysql> WITH RECURSIVE cte (n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM cte WHERE n < 6000000) SELECT max(n) FROM cte;
+---------+
| max(n)  |
+---------+
| 6000000 |
+---------+
1 row in set (4.10 sec)

mysql> show status like '%created_tmp_disk%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 1     |
+-------------------------+-------+
1 row in set (0.00 sec)

例 `aurora_tmptable_enable_per_table_limit` 设置为 `OFF` 的读取器数据库实例

查询完成时没有出现错误，因为 tmp_table_size 不适用，并且尚未达到全局 TempTable 资源限制。


mysql> set aurora_tmptable_enable_per_table_limit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@innodb_read_only,@@aurora_version,@@aurora_tmptable_enable_per_table_limit,@@temptable_max_ram,@@temptable_max_mmap;
+--------------------+------------------+------------------------------------------+---------------------+----------------------+
| @@innodb_read_only | @@aurora_version | @@aurora_tmptable_enable_per_table_limit | @@temptable_max_ram | @@temptable_max_mmap |
+--------------------+------------------+------------------------------------------+---------------------+----------------------+
|                  1 | 3.04.0           |                                        0 |          1073741824 |           1073741824 |
+--------------------+------------------+------------------------------------------+---------------------+----------------------+
1 row in set (0.00 sec)

mysql> set cte_max_recursion_depth=4294967295;
Query OK, 0 rows affected (0.00 sec)

mysql> WITH RECURSIVE cte (n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM cte WHERE n < 60000000) SELECT max(n) FROM cte;
+----------+
| max(n)   |
+----------+
| 60000000 |
+----------+
1 row in set (14.05 sec)

例 `aurora_tmptable_enable_per_table_limit` 设置为 `OFF` 的读取器数据库实例

此查询达到全局 TempTable 资源限制，aurora_tmptable_enable_per_table_limit 设置为 OFF。查询结束时读取器实例上出现错误。


mysql> set aurora_tmptable_enable_per_table_limit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@innodb_read_only,@@aurora_version,@@aurora_tmptable_enable_per_table_limit,@@temptable_max_ram,@@temptable_max_mmap;
+--------------------+------------------+------------------------------------------+---------------------+----------------------+
| @@innodb_read_only | @@aurora_version | @@aurora_tmptable_enable_per_table_limit | @@temptable_max_ram | @@temptable_max_mmap |
+--------------------+------------------+------------------------------------------+---------------------+----------------------+
|                  1 | 3.04.0           |                                        0 |          1073741824 |           1073741824 |
+--------------------+------------------+------------------------------------------+---------------------+----------------------+
1 row in set (0.00 sec)

mysql> set cte_max_recursion_depth=4294967295;
Query OK, 0 rows affected (0.01 sec)

mysql> WITH RECURSIVE cte (n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM cte WHERE n < 120000000) SELECT max(n) FROM cte;
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_1586_2' is full

例 `aurora_tmptable_enable_per_table_limit` 设置为 `ON` 的读取器数据库实例

如果达到 tmp_table_size 限制，查询结束时出现错误。


mysql> set aurora_tmptable_enable_per_table_limit=1;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@innodb_read_only,@@aurora_version,@@aurora_tmptable_enable_per_table_limit,@@tmp_table_size;
+--------------------+------------------+------------------------------------------+------------------+
| @@innodb_read_only | @@aurora_version | @@aurora_tmptable_enable_per_table_limit | @@tmp_table_size |
+--------------------+------------------+------------------------------------------+------------------+
|                  1 | 3.04.0           |                                        1 |         16777216 |
+--------------------+------------------+------------------------------------------+------------------+
1 row in set (0.00 sec)

mysql> set cte_max_recursion_depth=4294967295;
Query OK, 0 rows affected (0.00 sec)

mysql> WITH RECURSIVE cte (n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM cte WHERE n < 6000000) SELECT max(n) FROM cte;
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_8_2' is full

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

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

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

监控本地存储的大小和临时表空间占用情况也很重要。可以使用 FreeLocalStorage Amazon CloudWatch 指标监控特定数据库实例可用的临时存储，如 Amazon Aurora 的 Amazon CloudWatch 指标中所述。

注意

当 aurora_tmptable_enable_per_table_limit 参数设置为 ON 时，此过程不起作用。有关更多信息，请参阅限制内部内存中临时表的大小。

例 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 模式开启时失败。当 NO_ENGINE_SUBSTITUTION 处于关闭状态（原定设置）时，将进行适当的引擎替换，并成功创建临时表。


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> show create table tt3;
+-------+----------------------------------------------------------+
| Table | Create Table                                             |
+-------+----------------------------------------------------------+
| tt3   | CREATE TEMPORARY TABLE `tt3` (
  `id` int DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+----------------------------------------------------------+
1 row in set (0.00 sec)

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

Javascript 在您的浏览器中被禁用或不可用。

要使用 Amazon Web Services 文档，必须启用 Javascript。请参阅浏览器的帮助页面以了解相关说明。

文档惯例

与 MySQL 8.0 兼容的 Aurora MySQL 版本 3

比较 Aurora MySQL 版本 2 和 Aurora MySQL 版本 3

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

主题

内部（隐式）临时表的存储引擎

提示

注意

限制内部内存中临时表的大小

注意

例 aurora_tmptable_enable_per_table_limit 设置为 OFF 的写入器数据库实例

例 aurora_tmptable_enable_per_table_limit 设置为 ON 的写入器数据库实例

例 aurora_tmptable_enable_per_table_limit 设置为 OFF 的读取器数据库实例

例 aurora_tmptable_enable_per_table_limit 设置为 OFF 的读取器数据库实例

例 aurora_tmptable_enable_per_table_limit 设置为 ON 的读取器数据库实例

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

注意

例 1

例 2

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

临时表创建错误和缓解

例 `aurora_tmptable_enable_per_table_limit` 设置为 `OFF` 的写入器数据库实例

例 `aurora_tmptable_enable_per_table_limit` 设置为 `ON` 的写入器数据库实例

例 `aurora_tmptable_enable_per_table_limit` 设置为 `OFF` 的读取器数据库实例

例 `aurora_tmptable_enable_per_table_limit` 设置为 `OFF` 的读取器数据库实例

例 `aurora_tmptable_enable_per_table_limit` 设置为 `ON` 的读取器数据库实例