如何對我在 Aurora MySQL-Compatible 資料庫叢集中的慢速 SELECT 陳述式進行疑難排解？

2 分的閱讀內容

我想要對 Aurora MySQL-Compatible 版本資料庫叢集中的慢速 SELECT 陳述式進行疑難排解。

簡短說明

您的 SELECT 陳述式在 Aurora MySQL-Compatible 資料庫叢集中可能會執行緩慢，原因如下：

您過度使用資料庫系統資源。
資料庫已鎖定。
SELECT 陳述式在大型資料表上進行完整資料表掃描。或者，查詢缺少必要的索引。
長時間執行的交易增加了 InnoDB 歷史清單長度 (HLL)。

解決方法

**注意：**如果您在執行 AWS Command Line Interface (AWS CLI) 命令時收到錯誤訊息，請參閱對 AWS CLI 錯誤進行疑難排解。此外，請確定您使用的是最新的 AWS CLI 版本。

使用 CloudWatch Database Insights 檢查 SELECT 陳述式緩慢的原因

啟用 Performance Insights，並使用 Database Insights 偵測造成高資料庫負載的查詢。在資料庫負載圖表中監控 SQL、使用者和等待等維度。同時，監控資料庫遙測索引標籤的慢速 SQL 查詢區段。此外，您可以從 Performance Insights 的 SQL 統計資料分析查詢執行。例如，如果每次呼叫檢視的列數高於正常值，則執行計畫可能效率不佳。

使用指標監控您的資料庫執行個體系統資源

高 CPU、低記憶體或超出資料庫執行個體類別能力的大型工作負載，可能導致 SELECT 陳述式執行緩慢。若要監控資料庫執行個體資源，請使用以下工具：

使用 Amazon Aurora 的 Amazon CloudWatch 指標監控 CPU 使用率。
使用 Enhanced Monitoring (增強型監控) 查看作業系統 (OS) 指標詳細資訊。
使用 OS processes data in the Database telemetry (資料庫遙測標籤中的作業系統程序資料) 索引標籤，檢查資料庫負載是否超過最大 vCPU。

磁碟搜尋可能會導致 SELECT 陳述式執行緩慢。為了最小化磁碟 I/O，資料庫引擎會將從磁碟讀取的區塊快取到記憶體中。當資料庫需要相同資料時，它會從記憶體而非磁碟擷取資料。若要判斷特定查詢是從磁碟還是記憶體提供服務，請使用以下指標：

檢查 ReadIOPS 指標，以查看磁碟 I/O 操作數量。最佳實務是保持此值盡可能低。
檢查 BufferCacheHitRatio 指標，以查看緩衝快取服務的請求百分比。最佳實務是保持此值盡可能高。
檢查 FreeableMemory 指標，以查看資料庫執行個體可用記憶體。最佳實務是保持此值穩定。可用記憶體不足可能導致 BufferCacheHitRatio 低，且 ReadIOPS 高。<br id=hardline_break/> **注意：**如果 BufferCacheHitRatio 指標下降，且 SELECT 陳述式執行緩慢，則引擎會從底層磁碟區處理查詢。

本機儲存的磁碟搜尋也可能導致 SELECT 陳述式執行緩慢。Aurora MySQL-Compatible 對手動和內部暫存資料表使用本機儲存。如需更多資訊，請參閱 Aurora MySQL 版本 3 中的新暫存資料表行為。若要監控並解決本機儲存的磁碟搜尋問題，請檢查 Database Insights 的 Rdstemp OS 指標。最佳實務是讓這些值盡量保持低水準。

網路飽和可能導致 SELECT 陳述式執行緩慢。Aurora 會透過網路對叢集磁碟區執行 I/O 操作，並透過網路將查詢結果傳送到用戶端。若要監控並解決網路飽和，請檢查 NetworkThroughput 和 StorageNetworkThroughput 指標。總網路輸送量必須低於您的資料庫執行個體網路頻寬。

如果因工作負載而導致任何資源使用超出您資料庫執行個體類型的能力，請升級資料庫執行個體類別。

識別鎖死和阻塞鎖定

當兩個或多個交易彼此阻塞而無法繼續時，即會發生鎖死。若要識別資料庫中的鎖死，請在您的參數群組中啟用 innodb_print_all_deadlocks 參數。如需更多資訊，請參閱 MySQL 網站上的 innodb_print_all_deadlocks。然後從 Amazon RDS 主控台、AWS Command Line Interface 或 API 監控 mysql-error.log。

(選用) 若要識別鎖死，請登入 MySQL 管理帳號，然後執行以下命令：

SHOW ENGINE INNODB STATUS\G;

**注意：**在 MySQL Workbench 的預期輸出中，檢查 Latest Detected Deadlock (最新偵測到的鎖死) 區段。

即使沒有鎖死，一個長時間持有鎖的交易也可能造成阻塞鎖定。若要識別持續的阻塞鎖定，請參閱為何我的 Amazon RDS for MySQL 資料庫執行個體的查詢，在沒有其他活動工作階段時阻塞？

檢查您的查詢是否使用索引

當查詢沒有索引或進行完整資料表掃描時，查詢執行速度會很慢。索引可加快 SELECT 陳述式的執行速度。若要檢查查詢是否使用索引，請使用 EXPLAIN 陳述式。如需更多資訊，請參閱 MySQL 網站上的 EXPLAIN 陳述式。

在 EXPLAIN 輸出中，檢查資料表名稱、索引鍵，以及引擎在查詢期間掃描的列數。如果輸出未顯示任何使用中的索引鍵，請在 WHERE 子句的欄位上建立索引。如果資料表具有所需的索引，請檢查統計資料表是否為最新狀態。使用 ANALYZE 子句更新統計資料。如需詳細資訊，請參閱 MySQL 網站上的 INFORMATION_SCHEMA STATISTICS 資料表。

若要識別慢速 SELECT 陳述式，請使用 slow_query_log。若要記錄慢速查詢，請為您的資料庫叢集啟用慢速查詢日誌。

檢查 HLL

InnoDB 使用多版本並行控制 (MVCC)。MVCC 維護同一筆記錄的多個複本，以保持讀取一致性。HLL 是歷史清單中包含修改的復原日誌的總數。當有一個長時間執行的交易寫入或讀取資料時，HLL 會增加，直到交易完成或復原。其他交易會修改長時間執行交易使用的資料表。如果您的工作負載需要多個開放或長時間執行的交易，那麼您的資料庫可能會具有較高的 HLL。另外，如果清除執行緒無法跟上資料庫的變更，則可能會導致 HLL 偏高。HLL 偏高會導致資源使用率增加，並使 SELECT 陳述式的效能變慢且不穩定，以及儲存空間需求增加。在極端情況下，高 HLL 可能導致資料庫中斷。

若要監控 HLL，請使用寫入執行個體的 RollbackSegmentHistoryListLength 指標。或者，執行以下命令：

SHOW ENGINE INNODB STATUS;

輸出範例：

------------ TRANSACTIONS ------------Trx id counter 26368570695  
Purge done for trx's n:o < 26168770192 undo n:o < 0 state: running but idle History list length 1839

對於 Aurora MySQL-Compatible，HLL 位於叢集層級。若要檢查叢集層級的 HLL，請連線到您的寫入執行個體並執行以下陳述式：

SELECT server_id, IF(session_id = 'master_session_id', 'writer', 'reader') AS ROLE, replica_lag_in_msec, oldest_read_view_trx_id, oldest_read_view_lsn from mysql.ro_replica_status;

**注意：**您可以使用先前的陳述式識別讀取節點和寫入節點之間的複本延遲。檢查資料庫用於從儲存空間讀取的最舊日誌序號 (LSN) 以及資料庫執行個體的最舊讀取檢視交易 ID (Trx ID)。確保其中一個執行個體包含舊讀取檢視。

若要連線到包含舊讀取檢視的執行個體，請執行以下陳述式：

SELECT a.trx_id, a.trx_state, a.trx_started,  TIMESTAMPDIFF(SECOND,a.trx_started, now()) as "Seconds Transaction Has Been Open",    
a.trx_rows_modified, b.USER, b.host, b.db, b.command, b.time, b.state    
from information_schema.innodb_trx a, information_schema.processlist b    
where a.trx_mysql_thread_id=b.id    
order by trx_started;

**注意：**使用先前的陳述式識別擁有最舊 trx_id 的工作階段或交易。若要解除清理操作的阻塞，請判斷是否可以結束該工作階段。

若要解決 HLL 偏高的問題，請採取以下動作：

如果 DML 寫入導致 HLL 增加，請回復交易以取消查詢。由於必須回復的更新數量眾多，此過程可能較為漫長。
如果 READ 導致 HLL 增加，請使用 mysql.rds_kill_query 取消查詢。<br id=hardline_break/> **注意：**請聯絡您的資料庫管理員，以確認是否可以取消查詢。

若為防止 HLL 偏高，最佳實務是以較小批次提交資料。此外，不要重新啟動資料庫叢集或執行個體。當 HLL 可以存取緩衝快取中的記憶體資料時，才進行清理。如果重新啟動資料庫，可存活的頁面快取可能會遺失。若可存活的頁面快取遺失，則必須從叢集磁碟區讀取資料頁來清理 HLL。此過程比從記憶體清理慢，並會產生額外 I/O 計費成本。

Aurora MySQL 調校

主題: Database
標籤: Aurora MySQL
語言: 中文 (繁體)