如何對 Amazon RDS for MySQL 資料庫中的可釋放記憶體不足進行疑難排解?
我正在執行適用於 MySQL 執行個體的 Amazon Relational Database Service (Amazon RDS)。我發現我的可用記憶體不足,我的資料庫記憶體不足,或記憶體不足導致應用程式出現延遲問題。如何識別記憶體使用率的來源,以及如何對可釋放記憶體不足進行疑難排解?
簡短描述
在 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 次要版本。較舊的次要版本更容易包含與記憶體洩漏相關的錯誤。
相關資訊
相關內容
- 已提問 9 個月前lg...
- 已提問 10 個月前lg...
- 已提問 3 個月前lg...
- AWS 官方已更新 9 個月前
- AWS 官方已更新 2 年前
- AWS 官方已更新 3 年前
- AWS 官方已更新 9 個月前