AnalyticDB：通過監控資訊調優叢集效能

CPU平均使用率增高

CPU平均使用率展示的是在某個時間點，多個節點的CPU平均使用率。CPU平均使用率增高，會影響叢集的穩定運行，導致查詢和寫入的速度變慢。如果CPU平均使用率持續增高，表示叢集存在較大的風險，需要儘快最佳化。

CPU平均使用率增高的常見原因如下：

• 查詢

  查詢導致的CPU使用率增高，可能是由於Bad SQL，例如SQL中包含了複雜的計算邏輯、處理大量的資料，或者JOIN沒有JOIN條件，從而產生了笛卡爾積等。您可以通過一鍵診斷功能來定位存在問題的查詢：

  • Bad SQL檢測結果中，高耗時的SQL、資料讀取量大的SQL、Stage個數多的SQL、最耗CPU的SQL，都可能導致叢集的CPU使用率增高，需要根據自診斷結果或者執行計畫進行進一步的分析。

  • 異常Pattern檢測會從SQL模板的角度，對異常提交的Pattern進行檢測，與Bad SQL類似，導致CPU增高的Pattern需要從資料讀取量異常、消耗CPU異常、查詢耗時異常等多個維度進行分析，這些異常Pattern的出現都可能導致CPU增高。

  • 如果是計算節點或儲存節點CPU使用率增高的問題，可以結合一鍵診斷結果中的計算層檢測和儲存層檢測中的異常運算元檢測來分析，異常運算元中的運算元詳細資料和運算元匯總資訊中，都會從CPU消耗角度對異常運算元進行了篩選和過濾。

• 寫入

  寫入過程也會消耗CPU資源（包括INSERT、UPDATE、DELETE、REPLACE、INSERT OVERWRITE、INSERT INTO SELECT），同樣也會導致儲存節點CPU使用率增高。此時需要觀察刪除TPS、寫入TPS、更新TPS和LOAD_TPS等監控指標是否同樣出現了突增。

  常見的寫入導致CPU使用率增高原因如下：

  • 超長主鍵

    當主鍵非常長的時候，會導致查詢的主鍵索引比較大，從而消耗較大的CPU資源。

  • DELETE SQL

    如果單個DELETE WHERE語句命中了較多行資料，計算引擎需要計算出所有行的主鍵，然後再逐個下發給儲存節點進行刪除操作。一個DELETE SQL操作步驟可能會放大很多倍，從而導致CPU使用率增高。

  • UPDATE SQL

    如果單個UPDATE WHERE語句命中了較多行資料，計算引擎需要計算出所有命中行的主鍵，並更新其對應的欄位值，然後再逐個下發給儲存節點進行標記舊行以及追加（Append）新行的操作。一個UPDATE SQL操作步驟可能會放大很多倍，從而導致CPU使用率增高。

  • INSERT OVERWRITE

    批量寫入（Batch load）的過程中需要進行資料解析、按照叢集索引欄位（如果有聚集鍵）進行排序（Sort）、構建主鍵索引和普通索引等操作，上述操作都屬於CPU密集型操作（每個Shard需要一個線程進行上述工作）。目前雖然有批量寫入並發數量限制（例如最多同時存在2個批量寫入SQL），但是每個Shard需要一個線程進行批量寫入相關操作，仍舊可能導致CPU使用率增高。

  • INSERT INTO SELECT

    短時間內大量資料寫入，當後台Build任務堆積時會導致即時資料增多，此時查詢如果涉及即時資料的話，資料庫需要掃描大量即時資料（因為即時資料沒有索引），最終導致CPU使用率增高。

• Build

  Build任務會對資料進行構建索引、建立和清除分區等操作，很可能會導致儲存節點的CPU使用率增高。您可以在控制台對比CPU使用率和Build任務數的監控資料，可以較容易發現兩個指標間的相關性。

  說明

  • 關於Build的介紹請參考BUILD。

  • 如何定位和分析Build導致的資源水位增高的問題，請參見Build任務數增多。

CPU最大使用率傾斜

CPU最大使用率展示的是在某個時間點，多個節點中CPU使用率最高節點的值。當CPU最大使用率和CPU平均使用率長時間相差較大時，說明叢集內部存在任務處理不均勻的情況（有些節點計算量較大，有些節點計算量較小，最終導致CPU使用率傾斜）。CPU使用率傾斜嚴重（例如1倍以上），會較大地影響叢集啟動並執行穩定性，並且會導致資源浪費，因為分布式的查詢子任務受到了CPU最大使用率的限制，而無法進一步的提升效能，只能升配解決，但是其他節點的CPU使用率並不高。

導致CPU使用率傾斜可能存在如下原因：

• 源表傾斜

  通常是建表時選擇的分布鍵不均勻，導致不同Shard上的資料量存在較大差異。

  如下圖所示，某個大表分布不均，儲存節點0上的Shard_0和Shard_1中資料量較大，而在儲存節點1上的Shard_2和Shard_3中資料量較小，那麼當您查詢這個大表時，較大機率會出現儲存節點0需要處理的資料多，儲存節點1上需要處理的資料少的情況，這樣就會導致儲存節點0的CPU使用率長期高於儲存節點1的CPU使用率，最終導致CPU使用率傾斜。

  關於源表傾斜的診斷，請參見儲存空間診斷。一鍵診斷也會檢測佔用磁碟空間較大的傾斜表，分析資源傾斜。

• 中間資料扭曲

  中間資料扭曲不同於源表傾斜。在中間資料扭曲的情境下，源表資料可能在各個Shard上是分布均勻的，但是Shard中包含的某個欄位的值又是分布不均的。

  當您根據分布不均的欄位來做分組彙總查詢或者作為JOIN的條件，AnalyticDB for MySQL內部會根據這些欄位對資料進行一次不同節點間的重分布，重新分配後，因為相關的欄位值的資料會被分發到同一個節點上，就出現了欄位分布不均的現象。

  如下圖所示，某張表是根據a欄位進行分布，因為a欄位本身比較均勻，所以資料均勻地分布在不同的儲存節點上，當您使用了b欄位進行分組（group by b），那麼儲存節點1會將b欄位值為b1的資料行分發到計算節點1，為確保計算節點1具有所有b欄位值為b1的行，儲存節點2會把b欄位值為b1的資料分發到計算節點1，值為b2的資料分發到計算節點2。因此，計算節點1具有的資料行遠大於計算節點2，就產生了資料扭曲，對於後續的計算任務，計算節點1就需要消耗較多的叢集資源。

  針對中間資料扭曲導致的CPU使用率不均勻，您可以通過分析查詢的Stage層級診斷結果進行問題定位。

CPU平均使用率不高，但CPU最大使用率增高

查詢資料量大

原因：單條SQL查詢返回的資料量非常大，會影響接入節點的CPU使用率。

解決方案：您可以增加更精確的查詢條件縮小查詢範圍，減少返回的資料量；或者使用分頁查詢，避免一次性載入過多內容。如果需要匯出大量資料，也可以使用外表匯出。

最佳化器佔用CPU高

原因：叢集QPS較高、SQL較複雜時，會導致最佳化器佔用大量CPU。

解決方案：您可以先開啟PlanCache功能。開啟後觀察CPU使用率是否有明顯降低。若沒有降低，請提交工單聯絡支援人員協助處理。

存在長欄位寫入

原因：當某個表存在長欄位寫入時，接入節點需要消耗較多的資源處理長欄位，會導致CPU使用率增高。

解決方案：您可以執行以下語句查詢是否存在長欄位寫入的表。如果存在，建議您最佳化該表的商務邏輯，限制其欄位長度或拆分長欄位。

SELECT *
FROM 
    (SELECT schema_name,
        table_name,
        column_name,
         cast(json_extract(stats,
        '$.avgSize') AS bigint) AS avg_size
    FROM INFORMATION_SCHEMA.COLUMN_STATISTICS ) tmp
ORDER BY  avg_size DESC limit 20;

磁碟IO吞吐增高

磁碟IO吞吐表示底層儲存介質的吞吐消耗，單位MB/s。磁碟IO吞吐的最高值請參見ESSD雲端硬碟。由於該上限值是理想狀態下的測試值，與叢集實際的負載情況不完全相符，一般實際負載可能能夠達到該標稱值的80%左右。

磁碟IOPS增高

磁碟IOPS表示底層儲存介質每秒的IO次數，磁碟IOPS的最高值請參見ESSD雲端硬碟。由於該上限值是理想狀態下的測試值，與叢集實際的負載情況不完全相符，一般實際負載可能能夠達到該標稱值的80%左右。

計算記憶體使用量率增高

AnalyticDB在進行大量資料計算時，會消耗較多的記憶體資源。消耗記憶體較高的SQL一般包含Aggregation運算元、TopN運算元、Window運算元以及Join運算元：

• Aggregation運算元

  Aggregation運算元消耗記憶體較高主要是因為AnalyticDB for MySQL在進行資料的分組時，會把分組資訊暫存在記憶體中，如果分組欄位唯一值較多，那麼就會在分布式彙總的Final階段消耗較多的記憶體資源（Partial階段因為不需要進行全域的彙總，各個節點完成部分資料的局部彙總就可以將資料發送到下遊節點，所以Partial階段消耗的記憶體不大）。

• TopN運算元

  AnalyticDB for MySQL在進行TopN計算時（例如SQL中有ORDER BY id LIMIT m,n），當m較大時，AnalyticDB for MySQL中的TopN運算元會緩衝較多資料在記憶體中，以完成最終的全域排序，這個過程會消耗較多的記憶體資源。

• Window運算元

  Window運算元用於計算視窗函數。為了達到語義的最終結果，和Aggregation運算元類似，同樣需要暫存較多的資料到記憶體中。

• Join運算元

  AnalyticDB for MySQL支援標準的Join查詢操作，系統內部實現Join過程一般使用Hash演算法和Index演算法，更多介紹，請參見運算元。Hash演算法會將小表（Build表）緩衝到記憶體中，並在記憶體中為Build表構建一個Hash表，加速Join的過程。以下原因可能導致Hash表佔用較多記憶體：

  • Build表本身較大：

    AnalyticDB for MySQL會根據統計資訊評估Join操作兩邊的表的大小，以較小的表作為Build表，但不排除Build表仍然較大。

  • 統計資訊到期或者統計資訊評估不準

    當Join操作的兩表不是源表，而是經過了多次的彙總、過濾、其他Join等計算後，基於源表的統計資訊很難準確統計兩表的大小。或者當統計資訊到期，沒有及時更新，也會錯誤選擇了較大的表作為Build表，並為其構建Hash表。更多介紹，請參見統計資訊。

  • Left Join

    由於語義的原因，Left Join的右表一定要用來構建Hash表，以達到正確的語義結果。如果Left Join的右表較大，就會導致本次Join操作佔用較大記憶體。

更多這些運算元的介紹，請參見運算元。

當包含這些運算元的SQL並發較高，或者單運算元佔用較高的記憶體，那麼計算記憶體使用量率指標就會增高，影響叢集的穩定性，並且導致查詢報錯，常見報錯如下：

• Query exceeded reserved memory limit：某個查詢在單個節點上使用記憶體超過限制。

• Query exceeded system memory pool limit：單個欄位過長或參與計算的列過多。

• Out of Memory Pool size pre cal. avaliable：實體記憶體池耗盡。

• The cluster is out of memory, and your query was killed：叢集記憶體不足時，會終止當前正在啟動並執行最大的一個查詢。

如果要降低計算記憶體使用量率，您需要對包含這些運算元類型的SQL進行調優，具體操作請參見運算元層級診斷結果。

Build任務數增多

Build主要完成對寫入的資料進行構建索引、清理到期資料、執行非同步DDL任務等，將資料從寫最佳化轉變為讀取最佳化。Build任務在某些情況下會消耗較高的儲存節點CPU資源以及磁碟IO資源，可能會影響到其他動作，導致出現叢集穩定性問題。關於Build指標的說明如下。

叢集的Build任務數增多並影響到讀寫節點CPU使用率，可以從以下幾個方面進行定位和分析：

• 分區表的單分區較大。單分區較大時，這類大分區被寫入、更新或者刪除機率較大，更容易觸發分區被Build。您可以通過儲存空間診斷來定位這些類型的表，進行表結構的最佳化。

• 過大的非分區表。當非分區表較大時，也更容易被寫入、更新或者刪除，也更容易觸發全表Build。

• 大量的讀寫請求導致儲存節點CPU持續較高，進而導致Build執行過慢。

節點掉線個數增多

當AnalyticDB for MySQL叢集內部的節點無法提供服務時，會存在節點掉線的情況，節點掉線對於叢集的穩定運行有較大的危害，會導致查詢和寫入變慢，也會導致查詢報錯。節點掉線時，您可以分析CPU使用率是否持續增高、IO相關指標是否持續打滿等。

P95曲線

AnalyticDB for MySQL對CPU使用率、接入節點CPU使用率、計算記憶體使用量率、磁碟IO吞吐、磁碟IOPS、磁碟IO使用率、磁碟IO等待時間等監控指標增加了P95監控曲線。P95指標即95%的指標小於等於該值。以計算節點CPU使用率為例，假設叢集有100個計算節點，在某個時間點，將所有節點的CPU使用率按照升序排列，第95個計算節點的CPU使用率即計算節點P95 CPU使用率。

最大值、平均值與P95有以下區別：

• 最大值單純指出資料的上限，在存在異常值或者極端值的情況下，監控指標最大值會被這些個別值所影響，不能很好地代表資料集的一般水平或典型情況。

• 平均值旨在描述資料的集中趨勢，但在資料集存在異常值或傾斜分布時，也不再能準確反映資料的一般狀態。

• P95則是關注的較高部分資料的表現，忽略了最極端的資料點，適合評估大部分情況下的效能或水平。

查詢回應時間增長

查詢回應時間指標表示查詢從提交、排隊到真正執行結束的時間，關於AnalyticDB for MySQL中耗時的說明，請參見監控FAQ。

當叢集的查詢回應時間突然增高，可能存在如下原因：

• Bad SQL

  Bad SQL會佔用較多的叢集資源，影響其他正常SQL的執行。

• 異常Pattern

  有時某個SQL消耗的資源並不大，但是這類SQL出現的次數較多；或者某些SQL資源消耗較大，導致整個叢集處理任務的效能出現瓶頸，最終影響其他查詢，使整體查詢回應時間增高。

• 寫入量增大

  寫入量增大會佔用較多的CPU資源、磁碟IO資源，觸發更多的Build任務，最終會導致整體的查詢回應時間增加。

您可以在控制台的監控資訊頁面，選取查詢回應時間增高的時段，發起一鍵診斷，根據各項診斷結果分析具體原因。

查詢等待時間增長

當查詢提交到叢集的接入層節點後，叢集會根據接入層隊列的大小設定，檢查查詢是否需要排隊，防止同一時間段執行的SQL太多，導致叢集壓力增大，從而影響整體穩定性。更多介紹，請參見並發控制。

當查詢的等待時間突然出現增高的情況，通常是因為叢集內部執行效率降低導致，例如出現Bad SQL或者異常Pattern，導致佔用了大量的叢集資源。您可以通過一鍵診斷，查看多維度Bad SQL檢測結果和異常Pattern檢測結果。寫入資料量增大，導致佔用了較多的儲存節點CPU資源和IO資源也會導致查詢等待時間增高。

查詢失敗率增多

查詢失敗率僅統計失敗查詢的佔比，不會統計查詢失敗原因。導致查詢失敗可能有多種原因，常見原因和處理方法如下：

• SQL存在問題導致查詢失敗

  • 語法錯誤

    SQL不符合AnalyticDB for MySQL官方對SQL文法的定義，通常在SQL文法解析階段報錯。例如，SQL不完整、格式錯誤、關鍵字缺失、標點符號缺失等。

  • 語義錯誤

    SQL符合AnalyticDB for MySQL官方對SQL文法的定義，但在語義檢查時，探索資料庫對象錯誤，在語義分析階段報錯。例如，表名錯誤、列不存在、GROUP BY欄位缺失、函數參數類型錯誤等。

• 叢集內部問題導致查詢失敗

  • 查詢逾時

    AnalyticDB for MySQL有預設的查詢逾時時間。您也可以根據實際業務需求來配置查詢逾時時間，如果查詢執行時間超過了該時間，將會查詢失敗。

    說明

    • 預設查詢逾時時間的介紹，請參見使用限制。

    • 修改查詢逾時時間的方法，請參見Config和Hint配置參數。

  • 叢集壓力較大

    當叢集出現較大壓力時，內部節點通訊逾時或者內部進程無法服務都會導致查詢失敗。

• Readonly

  當系統發現Raft Log出現問題時，會直接將進程狀態置為Readonly，此時寫入操作會出現報錯。

• Timeout

  當系統無法即時消費Raft Log Queue（例如有超長主鍵導致寫入慢等），此時會出現反壓，最終導致寫入速度變慢，出現Timeout報錯。

表讀取結果資料量

AnalyticDB for MySQL的資料會儲存在不同的儲存節點上。表讀取結果資料量展示某個時間點，多個儲存節點上所有SQL查詢從儲存層返回到計算層的資料量。

如下圖所示，在某個時間點（Time_1），假設共有6條SQL語句（query1、query2、query3、query4、query5、query6）分別從6張表（user、report、customer、test、region、partition）中讀取資料。在該時間點，表讀取結果資料量為20.1 GB（計算方法為：1.6+2+3+0.7+4.8+8=20.1 GB）；表平均讀取結果資料量為6.7 GB（計算方法為：所有儲存節點讀取的資料量/儲存節點個數，即(1.6+2+3+0.7+4.8+8)/3=6.7 GB）；表最大讀取結果資料量為12.8 GB（計算方法為：4.8+8=12.8 GB）。

表讀取結果資料量、表最大讀取結果資料量和表平均讀取結果資料量能在一定程度上反映SQL查詢對叢集的壓力。

• 當表平均讀取結果資料量突然增加時，說明儲存層中有較多的資料被發送到計算層進行處理，消耗更多的CPU資源和記憶體資源。同時，從儲存層讀取的資料量增加，也會佔用更多的磁碟IO資源。

• 當表最大讀取結果資料量和表平均讀取結果資料量相差較大時，說明從多個儲存節點讀取的資料量存在差異，節點處理的資料量不同會導致某些節點過早地達到資源瓶頸，進而影響叢集的整體效能。這種情況通常源於表設計的不合理，例如某些表選擇了不均勻的分布欄位，導致資料在多個儲存節點上的分布不均。

寫入、刪除、更新的回應時間增長

寫入回應時間表示INSERT INTO VALUES操作處理每行資料的回應時間；刪除回應時間表示DELETE操作處理每行資料的回應時間；更新回應時間表示UPDATE操作處理每行資料的回應時間。回應時間一般會受到以下因素的影響：

• 儲存節點CPU使用率增高

  儲存節點CPU使用率增高可能受到其他指標增高的影響，例如叢集出現了Bad SQL，或者寫入（刪除/更新）的TPS增加等。

• 儲存節點寫相關IO指標增高

  儲存節點寫相關IO指標包括磁碟IO輸送量和磁碟IOPS等，這些指標增高的可能原因如下：

  • Build任務數增加所致。

  • 執行了備份、擴容等操作。

  因為以上操作都需要對磁碟進行寫入操作，所以可能會影響相關的回應時間。