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

PDFRSS

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 TempTable和 MyISAM 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 叢集磁碟區上儲存任何資料。

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

• 在讀取器執行個體上，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 中的可用記憶體問題。

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

最佳化 Aurora MySQL 資料庫執行個體上的 temptable_max_mmap 參數

Aurora MySQL 中的 temptable_max_mmap 參數控制記憶體映射檔案在溢位至磁碟上 InnoDB 暫存資料表 （寫入器資料庫執行個體上） 或導致錯誤 （讀取器資料庫執行個體上） 之前，可以使用的本機磁碟空間上限。正確設定此資料庫執行個體參數有助於最佳化資料庫執行個體的效能。

必要條件

1. 確定已啟用效能結構描述。您可以執行下列 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 My 的暫時儲存限制SQL。

例如，如果您使用的是 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 版中臨時資料表儲存方面和效能影響的詳細資訊，請參閱部落格文章 Use the TempTable storage engine on Amazon RDS for MySQL and Amazon Aurora MySQL (在 Amazon RDS for MySQL 和 Amazon Aurora MySQL 上使用 TempTable 儲存引擎)。