如何對 Amazon RDS for MySQL 資料庫中的可釋放記憶體不足進行疑難排解？

簡短描述

在 Amazon RDS for MySQL 中，您可以監控四種記憶體狀態：

• 作用中： 資料庫程序或執行緒正在主動使用的記憶體。
• 緩衝區： 緩衝區是記憶體中用於保存資料區塊的暫時空間。
• 可用記憶體： 可供使用的記憶體。
• 快取： 快取是一種將資料暫時儲存在記憶體中的技術，可以快速擷取資料。

依預設，當您建立 Amazon RDS for MySQL 執行個體時，系統會配置緩衝區和快取以改進資料庫操作。Amazon RDS for MySQL 也有一個內部記憶體元件 (such as key_buffers_size or query_cache_size)，可建立內部暫時資料表以執行特定操作。

當您使用 Amazon RDS for MySQL 時，確保了解 MySQL 如何使用和配置記憶體。識別使用記憶體的元件之後，您可以在執行個體和資料庫層級尋找瓶頸。然後，監控這些特定的指標，並設定工作階段以獲得最佳效能。

解決方法

MySQL 如何使用記憶體

在 Amazon RDS for MySQL 中，執行個體上 80% 至 90% 的可用記憶體會以預設參數配置。此配置對於效能來說是最佳的，但如果您設定了使用較多記憶體的參數，請修改其他參數以使用較少的記憶體來補償。

您可以計算 RDS for MySQL 資料庫執行個體的大約記憶體使用量，如下所示：

Maximum MySQL Memory Usage = innodb_buffer_pool_size + key_buffer_size + ((read_buffer_size + read_rnd_buffer_size + sort_buffer_size + join_buffer_size) X max_connections)

緩衝集區

全域緩衝區和快取包括 Innodb_buffer_pool_size、Innodb_log_buffer_size、key_buffer_size 和 query_cache_size 之類的元件。innodb_buffer_pool_size 參數是 RAM 的記憶體區域，其中 innodb 快取資料庫資料表和索引相關的資料。緩衝集區越大，需要轉移回磁碟的 I/O 操作越少。依預設，innodb_buffer_pool_size 使用配置給 Amazon RDS 資料庫執行個體的最多 75% 的可用記憶體：

innodb_buffer_pool_size = {DBInstanceClassMemory*3/4}

請確保先檢閱此參數，以識別記憶體用量的來源。然後，考慮透過修改自訂參數群組中的參數值來減少 innodb_buffer_pool_size 的值。

例如，可以將預設 DBInstanceClassMemory*3/4 減少為 *5/8 或 *1/2。請確保執行個體的 BufferCacheHitRatio 值不會太低。如果 BufferCacheHitRatio 值很低，則可能需要增加執行個體大小以取得更多 RAM。如需詳細資訊，請參閱設定 Amazon RDS for MySQL 參數的最佳實務，第 1 部分： 與效能相關的參數。

MySQL 執行緒

還會為連接至 MySQL 資料庫執行個體的每個 MySQL 執行緒配置記憶體。下列執行緒需要配置記憶體：

• thread_stack
• net_buffer_length
• read_buffer_size
• sort_buffer_size
• join_buffer_size
• max_heap_table_size
• tmp_table_size

此外，MySQL 建立內部暫時資料表來執行一些操作。這些資料表最初是作為以記憶體為基礎的資料表建立的。在資料表達到由 tmp_table_size 或 max_heap_table_size (以最低值為準) 指定的大小時，則此資料表將轉換為以磁碟為基礎的資料表。在多個工作階段建立內部暫時資料表時，您可能會看到記憶體使用率增加。為了減少記憶體使用率，請避免在查詢中使用暫時資料表。

注意： 當您增加限制 tmp_table_size 和 max_heap_table_size 時，較大的暫時資料表能夠存在於記憶體中。若要確認是否已建立隱含暫時資料表，請使用 created_tmp_tables 變數。如需有關此變數的詳細資訊，請參閱 MySQL 網站上的 created_tmp_tables。

JOIN 和 SORT 操作

如果在 JOIN 或 SORT 操作期間配置多個相同類型的緩衝區 (例如 join_buffer_size 或 sort_buffer_size)，記憶體用量將會增加。例如，MySQL 配置一個 JOIN 緩衝區來執行兩個資料表之間的 JOIN。如果查詢涉及多資料表 JOIN 且所有查詢都需要 JOIN 緩衝區，則 MySQL 會比資料表總數少配置一個 JOIN 緩衝區。如果查詢沒有最佳化，則設定工作階段變數的值過高可能會導致問題。您可以將最小記憶體配置給工作階段層級的變數，例如 join_buffer_size 和 sort_buffer_size。如需詳細資訊，請參閱使用資料庫參數群組。

如果您對 MYISAM 資料表執行大量插入，則會使用 bulk_insert_buffer_size 個位元組的記憶體。如需詳細資訊，請參閱使用 MySQL 儲存引擎的最佳實務。

效能結構描述

如果您已為 Amazon RDS for MySQL 上的 Performance Insights 啟用效能結構描述，效能結構描述可能會耗用記憶體。啟用效能結構描述時，MySQL 會在執行個體啟動時和伺服器操作期間配置內部緩衝區。如需有關效能結構描述如何使用記憶體的詳細資訊，請參閱效能結構描述記憶體配置模型的 MySQL 文件。

除了效能結構描述資料表外，您還可以使用 MySQL sys 結構描述。例如，您可以使用 performance_schema 事件來顯示為效能結構描述使用的內部緩衝區配置的記憶體數量。或者，您可以執行如下查詢來查看配置的記憶體數量：

SELECT * FROM performance_schema.memory_summary_global_by_event_name WHERE EVENT_NAME LIKE 'memory/performance_schema/%';

記憶體檢測列在 setup_instruments 資料表中，遵循 "memory/code_area/instrument_name" 格式。若要啟用記憶體檢測，請更新 setup_instruments 資料表中相關檢測的 ENABLED 欄：

UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE 'memory/%';

監控執行個體的記憶體使用量

Amazon CloudWatch 指標

在可用記憶體不足時，監控 DatabaseConnections、CPUUtilization、ReadIOPS 和 WriteIOPS 的 Amazon CloudWatch 指標。

對於 DatabaseConnections，請務必注意，對資料庫進行的每個連線都需要配置一定數量的記憶體。因此，資料庫連線激增可能會導致可釋放的記憶體下降。在 Amazon RDS 中，max_connections 的軟性限制方式如下：

{DBInstanceClassMemory/12582880}

檢查 Amazon CloudWatch 中的 DatabaseConnections 指標，以監控您是否超過此軟性限制。

此外，除了 FreeableMemory 之外，還可以監控 SwapUsage 的 CloudWatch 指標來檢查記憶體壓力。如果您發現使用了大量交換，且 FreeableMemory 不足，則您的執行個體可能會處於高記憶體壓力下。高記憶體壓力會影響資料庫效能。最佳實務是將記憶體壓力程度保持在 95％ 以下。如需詳細資訊，請參閱為什麼 Amazon RDS 執行個體在我具有足夠的記憶體時使用交換記憶體？

增強型監控

若要監控資料庫執行個體的資源使用率，請啟用增強型監控。然後，設定一或五秒的精細程度 (預設值為 60 秒)。使用增強型監控，您可以即時監控可釋放記憶體和作用中記憶體。

您也可以列出資料庫執行個體的執行緒，以監控耗用最大 CPU 和記憶體的執行緒：

mysql> select THREAD_ID, PROCESSLIST_ID, THREAD_OS_ID from performance_schema.threads;

然後，將 thread_OS_ID 對應至 thread_ID：

select p.* from information_schema.processlist p, performance_schema.threads t
where p.id=t.processlist_id and t.thread_os_id=<Thread ID from EM processlist>;

對可釋放記憶體不足進行疑難排解

如果您遇到可釋放記憶體不足的問題，請考慮下列疑難排解秘訣：

• 確保您具有足夠的資源配置給您的資料庫來執行查詢。使用 Amazon RDS 時，配置的資源量取決於執行個體類型。此外，特定查詢 (例如預存程序) 在執行時可能會佔用無限量的記憶體。
• 透過將大型查詢分解為較小的查詢，避免任何長時間執行的交易。
• 若要檢視資料庫中所有作用中的連線和查詢，請使用 SHOW FULL PROCESSLIST 命令。如果您觀察到具有 JOIN 或 SORTS 操作的長時間執行查詢，則必須具有足夠的 RAM 供最佳化工具計算計畫。此外，如果您識別需要暫時資料表的查詢，則必須具有額外的記憶體才能配置給資料表。
• 若要檢視長時間執行的交易、記憶體使用率統計資料和鎖定，請使用 SHOW ENGINE INNODB STATUS 命令。檢閱輸出並檢查 BUFFER POOL AND MEMORY 項目。BUFFER POOL AND MEMORY 項目提供有關 InnoDB 記憶體配置的資訊，例如「已配置的總記憶體」、「內部雜湊表」和「緩衝集區大小」。InnoDB 狀態還有助於提供有關閂鎖，鎖定和鎖死的其他資訊。
• 如果您的工作負載經常遇到鎖死，請在自訂參數群組中修改 innodb_lock_wait_timeout 參數。InnoDB 依賴於 innodb_lock_wait_timeout 設定在發生鎖死時復原交易。
• 若要將資料庫效能最佳化，請務必已正確調校您的查詢。否則，您可能會遇到效能問題和等待時間延長問題。
• 使用 Amazon RDS Performance Insights 協助您監控資料庫執行個體並識別任何有問題的查詢。
• 監控 Amazon CloudWatch 指標 (例如 CPU 使用率、IOPS、記憶體和交換用量)，這樣執行個體就不會限流。
• 在 FreeableMemory 指標上設定 CloudWatch 警示，以便在可用記憶體達到 95% 時收到通知。最佳實務是保持至少 5% 的執行個體記憶體可用。
• 定期將執行個體升級為較新的 MySQL 次要版本。較舊的次要版本更容易包含與記憶體洩漏相關的錯誤。

---

相關資訊

監控 Amazon RDS 概觀

為什麼 Amazon RDS 資料庫執行個體在我具有足夠的記憶體時使用交換記憶體？