內部 (隱含) 暫時資料表的儲存引擎限制記憶體內部暫存資料表的大小緩解 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 版本不同。現在您可以在 TempTable 與 InnoDB 儲存引擎之間進行選擇，而不是為這類暫時資料表，在 InnoDB 與 MyISAM 儲存引擎之間進行選擇。

使用 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 叢集磁碟區上存放任何資料。

與內部暫時資料表相關的組態參數會以不同方式套用至叢集中的寫入器和讀取器執行個體：

在讀取器執行個體上，MySQL Aurora 始終使用 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 參數定義 MEMORY 儲存體引擎在 Aurora MySQL 3 版中建立暫存資料表的大小上限。

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 時，透過 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 設定為關閉。查詢因讀取器執行個體上發生錯誤而結束。


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 中的可用記憶體問題。

監控本機儲存空間的大小和暫時資料表空間的耗用狀況，也很重要。如需監控執行個體上本機儲存空間的詳細資訊，請參閱 AWS 知識中心文章中的 Aurora MySQL 相容的本機儲存空格中儲存些什麼，以及如何疑難排解本機儲存空間的問題？。

注意

當 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。

您必須啟用 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`