內部 (隱含) 暫時資料表的儲存引擎限制記憶體內部暫存資料表的大小緩解 Aurora 複本上內部暫時資料表的完整性問題讀取器資料庫執行個體上的使用者建立 (明確) 的暫時資料表暫時資料表建立時發生的錯誤和緩解措施

Aurora MySQL 第 3 版中的新臨時資料表行為

Aurora MySQL 第 3 版處理暫存資料表的方式與舊版 Aurora MySQL 版本不同。此新行為繼承自 MySQL 8.0 Community Edition。可以使用 Aurora MySQL 第 3 版建立兩種類型的暫存資料表：

內部（或隱含）臨時資料表 – 由 Aurora MySQL 引擎建立，用於處理排序彙總、衍生資料表或常見資料表表達式（）等操作CTEs。
使用者建立（或明確）的暫存資料表 – 當您使用CREATE TEMPORARY TABLE陳述式時，由 Aurora MySQL 引擎建立。

Aurora 讀取器資料庫執行個體上的內部暫時資料表和使用者建立的暫時資料表皆有其他注意事項。我們會在下列各節討論這些變更。

內部 (隱含) 暫時資料表的儲存引擎

產生中間結果集時，Aurora MySQL 一開始會嘗試寫入記憶體內暫存資料表。這可能不成功，因為資料類型不相容或設定了限制。若是如此，則暫存資料表會轉換為磁碟上暫存資料表，而不是保留在記憶體中。如需詳細資訊，請參閱 MySQL 文件中的 My 中的內部暫時資料表使用SQL。

在 Aurora MySQL 第 3 版中，內部暫存資料表的運作方式與舊版 Aurora MySQL 版本不同。您現在選擇和儲存引擎，而不是在此類暫存資料表的 InnoDB TempTable和我的ISAMMEMORY儲存引擎之間進行選擇。

使用 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 的大小都會預設為 1 GiB。您可以在寫入器和讀取器執行個體上調整此值。
將 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 aurora_tmptable_enable_per_table_limit 版及更新版本中， tmp_table_size也會定義當資料庫參數設定為時，TempTable儲存引擎建立的暫存資料表大小上限ON。預設會停用此行為（OFF），其行為與 Aurora MySQL 3.03 版和更低版本中的行為相同。

當 aurora_tmptable_enable_per_table_limit 為 OFF 時，透過 TempTable 儲存體引擎建立記憶體內部暫存資料表不用考慮 tmp_table_size。

但是，全域 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 參數的值時請小心。設定過高的值會減少資料庫執行個體上可用的記憶體，這可能會導致 out-of-memory條件。監控資料庫執行個體上的平均可用記憶體。然後為 temptable_max_ram 決定適合的值，以便執行個體上仍能保留合理的可用記憶體量。如需詳細資訊，請參閱Amazon Aurora中的可用記憶體問題。

監控本機儲存空間的大小和暫時資料表空間的耗用狀況，也很重要。您可以使用 FreeLocalStorageAmazon 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 My 的暫時儲存限制SQL。

讀取器資料庫執行個體上的使用者建立 (明確) 的暫時資料表

您可以在 CREATE TABLE 陳述式中使用 TEMPORARY 的關鍵字建立明確的暫時資料表。Aurora 資料庫叢集中寫入器資料庫執行個體支援明確暫時資料表。您也可以在讀取器資料庫執行個體上使用明確暫時資料表，但該資料表無法強制使用 InnoDB 儲存引擎。

若要避免在 Aurora MySQL Reader 資料庫執行個體上建立明確的暫存資料表時發生錯誤，請確定您使用下列其中一種或兩種方式執行所有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 My 上使用 TempTable 儲存引擎SQL。

您的瀏覽器已停用或無法使用 Javascript。

您必須啟用 Javascript，才能使用 AWS 文件。請參閱您的瀏覽器說明頁以取得說明。

文件慣用形式

Aurora MySQL 第 3 版與 MySQL 8.0 相容

比較 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`