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
在某些情况下,当您使用 TempTable
存储引擎时,临时数据可能会超过内存池的大小。如果是这样,Aurora MySQL 会使用辅助机制存储溢出数据。
您可以设置 temptable_max_mmap
Aurora MySQL 版本 3 通过以下方式存储溢出数据:
-
在写入器数据库实例上,溢出到 InnoDB 内部临时表或内存映射临时文件的数据驻留在实例上的本地存储中。
-
在读取器数据库实例上,溢出数据始终驻留在本地存储上的内存映射临时文件中。
只读实例无法在 Aurora 集群卷上存储任何数据。
与内部临时表相关的配置参数对集群中的写入器和读取器实例的应用方式不同:
-
在读取器实例上,Aurora MySQL 始终使用
TempTable
存储引擎。 -
对于写入器和读取器实例,
temptable_max_mmap
的大小都默认设置为 1GiB,无论数据库实例内存大小如何。可以在写入器实例和读取器实例上调整此值。 -
将
temptable_max_mmap
设置为0
可禁止在写入器实例上使用内存映射的临时文件。 -
您无法在读取器实例上将
temptable_max_mmap
设置为0
。
注意
建议您不要使用 temptable_use_mmap
限制内部内存中临时表的大小
如内部(隐式)临时表的存储引擎中所述,可以通过使用 temptable_max_ram
还可以使用 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/#sql
xx_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/#sql
xx_xxx
' is full全局
TempTable
资源限制和每个表的限制都适用于这种情况。
-
注意
当 internal_tmp_mem_storage_engineMEMORY
时,aurora_tmptable_enable_per_table_limit
参数不起作用。在这种情况下,内存中临时表的最大大小由 tmp_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 临时表(在写入器数据库实例上)或导致错误(在读取器数据库实例上)。正确设置此数据库实例参数有助于优化数据库实例的性能。
- 先决条件
-
-
确保 Performance Schema 已启用。可以通过运行以下 SQL 命令进行验证:
SELECT @@performance_schema;
输出值为
1
表示已启用。 -
确认临时表内存检测已启用。可以通过运行以下 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
参数并为其设置适当的大小。-
查看上一个查询的输出,并确定临时表磁盘使用率峰值,如
high_alloc
列所示。 -
根据临时表磁盘使用峰值,调整 Aurora MySQL 数据库实例的数据库参数组中的参数
temptable_max_mmap
。将值设置为比临时表磁盘使用量峰值略高一点,以适应未来的增长。
-
将参数组更改应用于您的数据库实例。
-
在工作负载高峰期再次监控临时表磁盘使用情况,以确保新
temptable_max_mmap
值合适。 -
根据需要重复前面的步骤来微调
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 存储引擎