Aurora MySQL 版本 3 中的新临时表行为 - Amazon Aurora
内部（隐式）临时表的存储引擎限制内部内存中临时表的大小缓解 Aurora 副本上内部临时表的完整性问题优化 Aurora MySQL 数据库实例上的 temptable_max_mmap 参数读取器数据库实例上用户创建的（显式）临时表临时表创建错误和缓解
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 与 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 版本 3 通过以下方式存储溢出数据：

  • 在写入器数据库实例上，溢出到 InnoDB 内部临时表或内存映射临时文件的数据驻留在实例上的本地存储中。

  • 在读取器数据库实例上，溢出数据始终驻留在本地存储上的内存映射临时文件中。

    只读实例无法在 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 的临时存储限制。

优化 Aurora MySQL 数据库实例上的 temptable_max_mmap 参数

Aurora MySQL 中的 temptable_max_mmap 参数控制内存映射文件可以使用的最大本地磁盘空间量，超出此空间量之后，将溢出到磁盘上的 InnoDB 临时表（在写入器数据库实例上）或导致错误（在读取器数据库实例上）。正确设置此数据库实例参数有助于优化数据库实例的性能。

先决条件

  1. 确保 Performance Schema 已启用。可以通过运行以下 SQL 命令进行验证：

    SELECT @@performance_schema;

    输出值为 1 表示已启用。

  2. 确认临时表内存检测已启用。可以通过运行以下 SQL 命令进行验证：

    SELECT name, enabled FROM performance_schema.setup_instruments WHERE name LIKE '%memory%temptable%';

    对于相关的临时表内存检测条目，enabled 列显示 YES。

监控临时表使用情况

建议您在设置 temptable_max_mmap 初始值时，一开始先设置为正在使用的数据库实例类的本地存储大小的 80%。这样可以确保临时表有足够的磁盘空间来高效运行，同时为实例上的其他磁盘使用留出空间。

要查找您的数据库实例类的本地存储大小，请参阅 Aurora MySQL 的临时存储限制。

例如，如果您使用的是 db.r5.large 数据库实例类，则本地存储大小为 32 GiB。在本例中，最初应将 temptable_max_mmap 参数设置为 32 GiB 的 80%，即 25.6 GiB。

设置初始 temptable_max_mmap 值后，在 Aurora MySQL 实例上运行您的峰值工作负载。使用以下 SQL 查询监控当前的和较高的临时表磁盘使用率：

SELECT event_name, current_count, current_alloc, current_avg_alloc, high_count, high_alloc, high_avg_alloc
FROM sys.memory_global_by_current_bytes WHERE event_name LIKE 'memory/temptable/%';

此查询检索以下信息：

  • event_name – 临时表内存或磁盘使用事件的名称。

  • current_count – 当前分配的临时表内存或磁盘块的数量。

  • current_alloc – 当前为临时表分配的内存或磁盘量。

  • current_avg_alloc – 临时表内存或磁盘块的当前平均大小。

  • high_count – 分配的临时表内存或磁盘块的最大数量。

  • high_alloc – 为临时表分配的最大内存或磁盘量。

  • high_avg_alloc – 临时表内存或磁盘块的最大平均大小。

如果使用此设置时查询失败并出现表已满错误，则表示您的工作负载需要更多磁盘空间才能完成临时表操作。在这种情况下，可以考虑增加数据库实例大小，以便有更多本地存储空间。

设置最佳 temptable_max_mmap 值

使用以下步骤监控 temptable_max_mmap 参数并为其设置适当的大小。

  1. 查看上一个查询的输出，并确定临时表磁盘使用率峰值，如 high_alloc 列所示。

  2. 根据临时表磁盘使用峰值，调整 Aurora MySQL 数据库实例的数据库参数组中的参数 temptable_max_mmap。

    将值设置为比临时表磁盘使用量峰值略高一点，以适应未来的增长。

  3. 将参数组更改应用于您的数据库实例。

  4. 在工作负载高峰期再次监控临时表磁盘使用情况，以确保新 temptable_max_mmap 值合适。

  5. 根据需要重复前面的步骤来微调 temptable_max_mmap 参数。

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

您可以在 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 存储引擎。