一文帶你搞懂GaussDB資料庫效能調優

华为云开发者联盟發表於2024-11-18
資料庫

本文分享自華為雲社群《【GaussTech技術專欄】GaussDB效能調優》，作者：GaussDB 資料庫。

資料庫效能調優是一項複雜且系統性的工作，需要綜合考慮多方面的因素。因此，調優人員應對系統軟體架構、軟硬體配置、資料庫配置引數、併發控制、查詢處理和資料庫應用擁有廣泛而深刻的理解。

本文旨在剖析GaussDB效能調優的總體思路，探討系統整體效能問題，以及對鎖阻塞問題進行分析和最佳化。

1. 效能調優思路

GaussDB總體效能調優的思路是：先進行效能瓶頸點分析，找到相應的瓶頸點之後，再針對性地進行最佳化，直到系統效能到達業務可接受的範圍內。

調優思路，如圖1所示：

圖1 GaussDB總體效能調優思路

首先，應該確認應用壓力是否傳遞到資料庫，可以透過分析資料庫節點的資源使用情況，如CPU、I/O、記憶體以及資料庫執行緒池、活躍會話等資訊來輔助判斷。GaussDB資料庫的管控平臺提供了豐富的監控指標體系，便於效能分析人員檢視資料庫的實時或者歷史資源使用情況。

登入管控平臺後，進入監控巡檢選單，選擇監控大盤，即可檢視對應例項的CPU/記憶體使用率，如圖2所示：

圖2 對應例項的CPU/記憶體使用率

點選磁碟/儲存選單，可以檢視磁碟I/O使用率，重點關注磁碟讀寫速率以及時延是否符合預期，如圖3所示：

圖3 磁碟讀寫速率以及時延情況

點選網路選單，可以檢視網路傳輸速率及網路卡是否有丟包、錯包等情況，如圖4所示：

圖4 網路傳輸速率及網路卡傳送速率

選擇連線選單，可以檢視資料庫的連線及會話狀態，如圖5所示：

圖5 連線及會話狀態

圖5中，如果活躍會話的佔比遠低於應用的併發數，說明資料庫中大量會話處於空閒狀態。同時，如果CPU使用率也很低，那麼，就可以判斷壓力沒到達資料庫，此時需要排查應用端是否存在瓶頸。

導致應用側瓶頸的問題比較常見的原因有：

  • 應用伺服器資源瓶頸。比如，應用伺服器的CPU滿載，應用程式記憶體分配不足等；

  • 應用到資料庫網路問題。比如，網路時延高，頻寬滿，存在丟包現象等；

  • 應用自身邏輯處理速度慢；

  • 應用配置不優，比如連線池引數、記憶體相關配置等設定不當。

例如，某個客戶透過 jmeter 做大併發壓測，效能不及業務預期。經過分析，發現是 jmeter 工具分配的最大可用記憶體不足，導致壓力沒有到達資料庫。透過修改如下配置，問題得到了解決。

編輯jmeter.sh檔案：set HEAP=-Xms1g -Xmx4g

確認壓力到達資料庫後，再針對相應的瓶頸點進行分析最佳化。主要從以下兩個方面進行：

1）排查資料庫中是否存在效能不優的業務SQL語句，並對效能不優的SQL進行最佳化。透過如下語句，檢視資料庫中耗時高的TOP SQL語句，並對那些執行效能不符合預期的SQL語句逐一進行分析與調優。

select unique_sql_id,substr(query,1,50) as query ,n_calls,round(total_elapse_time/n_calls/1000,2) avg_time,round(total_elapse_time/1000,2) as total_time from dbe_perf.summary_statement t where  n_calls>10 and avg_time>3  and user_name='root' order by total_time desc;

如圖6所示，n_calls 表示該SQL語句在資料庫中的執行次數，avg_time 為該SQL 語句的平均執行時間，total_time 為該SQL語句的總耗時。對於平均執行時間超過閾值的SQL語句，重點進行分析與最佳化。

圖6 SQL語句指標及對應資料展示

針對執行效能不優的SQL語句，透過unique_sql_id可以檢視該SQL語句的執行詳情，幫助分析SQL語句的效能瓶頸點。

select * from dbe_perf.statement where unique_sql_id=3508314654;

如圖7所示，該檢視記錄了SQL語句在資料庫的詳細執行情況，比如，總執行次數（n_calls）和總耗時（total_elapse_time），便於獲取該SQL的總耗時以及平均耗時。

圖7 SQL語句在資料庫中的詳細執行情況檢視

行活動，包括隨機掃描、順序掃描行數、返回的行數、插入/更新/刪除的行數以及buffer命中的頁面數等資訊。此外，還記錄了軟解析（n_soft_parse）、硬解析（n_hard_parse）的次數，比如SQL大量硬解析導致的資料庫CPU飈高，可以透過該指標進行分析定位。

時間模型，包含db_time、cpu_time、execution_time、plan_time、data_io_time、net_send_info、net_recv_info、sort_time以及hash_time等指標，有助於判斷SQL在資料庫中的時間消耗在哪個階段。例如，若某環境磁碟效能不佳，則data_io_time的耗時佔比就會比較高。

如果需要進一步分析SQL本身的效能問題，比如執行計劃是否最優、索引是否最優等效能問題，可以藉助SQL的執行計劃進行分析。

透過如下方式，可檢視SQL的執行計劃：

explain analyze SELECT c_id     FROM bmsql_customer     WHERE c_w_id = 1 AND c_d_id = 1 AND c_last = 'ABLEABLEABLE'     ORDER BY c_first;

結合SQL的執行計劃，分析SQL效能的瓶頸點，再進行效能最佳化，如圖8所示：

圖8 SQL效能最佳化過程

2）從系統層面進行作業系統級和資料庫系統級的調優，充分利用機器的CPU、記憶體、I/O和網路資源，避免資源衝突，從而提升整個系統查詢的吞吐量。

2. 系統級效能問題分析

2.1 CPU使用率高

資料庫的CPU使用率高，通常是由業務SQL語句引起的，我們可以透過如下方式，獲取資料庫中消耗CPU資源高的SQL語句，並對相應的業務SQL語句進行最佳化。

select unique_sql_id,substr(query,1,50) as query ,n_calls,round(total_elapse_time/n_calls/1000,2) avg_time,round(total_elapse_time/1000,2) as total_time,round(cpu_time/1000,2) as cup_time from dbe_perf.statement t where  n_calls>10 and avg_time>3  and user_name='root'  order by cpu_time desc limit 5;

常見的導致CPU資源消耗高的原因有：

  • SQL語句大量使用了全表掃描，這可能是由索引缺失、索引失效、執行計劃不優等因素所導致。

  • SQL語句大量進行硬解析，通常是因為應用邏輯未使用PBE（Prepare Bind Execute）。

  • SQL語句掃描了大量的元組，比如，分割槽表分割槽剪枝失效，掃描了全分割槽，表中存在大量的死元組，導致掃描了大量無用頁面等。

如果CPU使用率高是由非業務SQL語句引起的，可以藉助火焰圖來進行分析定位。透過火焰圖，可以直觀地瞭解程式中哪些函式佔用了大量的 CPU 時間或資源，並且可以追蹤函式呼叫路徑。

GaussDB在核心505版本中內建了火焰圖工具，預設每5分鐘會自動採集一次，儲存在$GAUSSLOG/gs_flamegraph/{datanode}路徑下，詳細資訊可參考GaussDB產品文件《內建perf工具》章節。

例如，某客戶在壓測過程中發現資料庫伺服器的CPU SYS佔用率超過70%，透過抓取壓測期間的火焰圖進行分析，如圖9所示，發現資料庫載入時，區檔案的執行緒佔比超過40%。

圖9 某客戶壓測期間的火焰圖

經分析，原因是在高併發頻繁建立連線時，資料庫每次建連都需要讀取時區檔案以獲取時區資訊，而應用未使用長連線，導致CPU SYS使用率飆升。

2.2 記憶體不足

記憶體資源，也是影響資料庫效能的關鍵因素之一。在分析記憶體問題之前，我們先了解一下GaussDB的記憶體管理機制。

如圖10所示，GaussDB的記憶體管理採用動態記憶體與靜態記憶體相結合的方式，由引數 max_process_memory 控制資料庫可用的最大記憶體。其中，靜態記憶體區域主要用作資料庫的共享緩衝區，用於快取資料頁面，由shared_buffers引數控制。動態記憶體區域，則由資料庫根據需要進行動態分配，主要包括後設資料的快取、執行計劃的快取、使用者建連以及內部執行緒的記憶體消耗等。

圖10 GaussDB的記憶體管理機制

記憶體導致的效能問題，通常分為以下幾個方面：

1）共享快取區不足，導致SQL的buffer命中率低。為了檢視相應的效能指標，可以藉助GaussDB的管控平臺或者WDR報告。通常情況下，TP資料庫的buffer命中率應該在99%以上。如果資料庫的buffer命中率較低，建議排查資料庫的shared_buffers引數設定是否合理（如圖11所示）。

圖11 資料庫的buffer命中率

2）在GaussDB中，SQL的hash join或者sort運算元存在資料落盤操作，work_mem引數控制可下盤運算元可用的實體記憶體空間。如果work_mem所限定的實體記憶體不夠，運算元運算的資料將被寫入臨時表空間，會帶來5-10倍的效能下降。為了最佳化效能，可以檢視SQL的執行計劃，如果運算元存在落盤的情況（如圖12所示），可適當調整work_mem引數值。

圖12 運算元落盤情況

3）資料庫動態記憶體不足，導致業務執行報錯（ERROR：memory is temporarily unavailable ）或者效能不足。當動態記憶體不足時，可以透過如下SQL語句找出記憶體消耗高的SQL語句，以便排查是否存在不優的SQL 語句。藉助SQL的執行計劃分析，可以檢查是否有不合理的join順序，或者是否存在非必要的排序操作，從而避免消耗大量記憶體。

select unique_sql_id,substr(query,1,50) as query ,n_calls,round(total_elapse_time/n_calls/1000,2) avg_time,round(total_elapse_time/1000,2) as total_time,hash_mem_used,sort_mem_used from dbe_perf.statement t where  n_calls>10 and avg_time>3  and user_name='root' order by (hash_mem_used+sort_mem_used) desc;

如果需要排查由非業務SQL語句導致的異常的記憶體消耗問題，比如記憶體堆積、記憶體洩露等，GaussDB提供了豐富的記憶體相關的監控檢視，可以透過下面的檢視（如圖13所示），檢視資料庫節點的記憶體消耗情況。

圖13 GaussDB記憶體相關的監控檢視

基於上面的查詢結果，如果dynamic_used_shrctx的佔用率高，說明是全域性共享動態記憶體的佔用高。可以透過如下SQL語句，檢視全域性共享動態記憶體上下文的消耗情況。

select contextname, sum(totalsize)/1024/1024 totalsize, sum(freesize)/1024/1024 freesize, count(*) count from gs_shared_memory_detail group by contextname order by totalsize desc limit 10;

如果max_dynamic_memory的佔用率高，但是dynamic_used_shrctx的佔用率低，那麼說明是執行緒或者會話佔用的記憶體多。可以透過如下SQL語句，查詢資料庫執行緒的記憶體上下文消耗情況。

select contextname, sum(totalsize)/1024/1024 totalsize, sum(freesize)/1024/1024 freesize, count(*) sum from gs_thread_memory_context group by contextname order by sum desc limit 10;

查詢結果如下圖所示，可以看出，當前資料庫中記憶體佔用最高的為後設資料的快取(LocalSysCacheShareMemory)。結合圖14中的查詢結果，排查是否存在不合理的記憶體佔用情況。

圖14 資料庫執行緒的記憶體上下文消耗情況

2.3 IO瓶頸

透過 iostat 命令，可以檢視資料庫節點 I/O 的繁忙度和吞吐量，分析是否存在由於 I/O 導致的效能瓶頸。如圖15所示：

圖15 資料庫節點 I/O 的繁忙度和吞吐量

重點關注磁碟的讀寫吞吐量和讀寫時延。通常情況下，SSD盤的讀寫時延在2ms以下，單盤頻寬在300MB以上。如果磁碟效能存在異常，優先排查硬體是否存在故障，如磁碟存在壞盤、慢盤、RAID卡故障或磁碟讀寫策略不正確等。如果磁碟硬體效能正常，而I/O 壓力大，可以適當調整資料庫I/O 相關的引數，以降低資料的I/O 消耗，從而最佳化資料庫的整體效能。I/O 相關的關鍵引數連結如下：

後端寫程序：https://support.huaweicloud.com/distributed-devg-v2-gaussdb/gaussdb-12-1124.html

非同步I/O：https://support.huaweicloud.com/distributed-devg-v2-gaussdb/gaussdb-12-1125.html

2.4 網路異常

在傳統集中式資料庫環境下，應用伺服器與資料庫伺服器通常部署在同一個機房內，從而確保應用與資料庫間的網路開銷較小。然而，在雲+分散式資料庫環境下，應用伺服器到資料庫伺服器的網路鏈路較長，網路耗時對交易效能至關重要。在此情境下，我們不僅需要關注應用與資料庫之間的網路狀況（通常應該小於0.2ms），還需考慮資料庫內部節點之間的網路情況，也會對效能產生較大的影響。

GaussDB要求AZ內網路時延小於0.2ms，AZ間的網路時延小於2ms，region間網路時延小於100ms。可以透過linux的ping命令，排查兩個伺服器之間的網路時延及丟包等情況，如圖16所示：

圖16 ping命令，排查兩個伺服器之間的網路時延及丟包等情況

透過 sar -n DEV 1 命令，檢視網路的傳輸情況。

如圖17所示，“rxkB/s”為每秒接收的千位元組數，“txkB/s”為每秒傳送的千位元組數，主要關注每個網路卡的傳輸量是否達到傳輸上限。

圖17 sar -n DEV 1 命令，網路傳輸情況

3. 鎖阻塞問題分析

資料庫鎖機制是一種用於管理併發訪問的技術。它透過對資料庫中的資料進行鎖定，來確保在多個使用者併發訪問資料庫時，資料的一致性和完整性。

在併發訪問的場景下，經常會遇到因為鎖衝突導致的效能問題。下面我們看一下在GaussDB中應該如何定位和分析鎖衝突的問題。

如果應用正在執行，可以透過下面的SQL語句，檢視當前資料庫中正在執行的會話是否存在鎖阻塞。

集中式場景：

SELECT a.pid as w_pid,a.query as w_query,a.state,d.query as locking_query,d.state as l_state,d.pid as l_pid,d.sessionid as l_sessionid
FROM pg_stat_activity AS a
JOIN pg_thread_wait_status b ON b.query_id = a.query_id
JOIN pg_thread_wait_status c 
ON c.sessionid = b.block_sessionid and c.node_name=b.node_name
JOIN pg_stat_activity d
on d.sessionid=c.sessionid
;

分散式場景：

SELECT a.pid as w_pid,a.query as w_query,a.state as w_state, a.datname, a.usename,d.query as lock_query,d.state as l_state,d.pid as l_pid,d.sessionid as l_sessionid
FROM pgxc_stat_activity AS a
JOIN pgxc_thread_wait_status b ON b.query_id = a.query_id
JOIN pgxc_thread_wait_status c ON c.sessionid = b.block_sessionid and c.node_name=b.node_name
JOIN pgxc_stat_activity d
on substring(d.global_sessionid,0,instr(d.global_sessionid,'#')) ilike substring(c.global_sessionid,0,instr(c.global_sessionid,'#'))
;

查詢結果如圖18所示，可以獲取當前庫中存在鎖阻塞的SQL語句，同時獲取到阻塞它的會話ID、執行緒ID以及對應的查詢。

圖18 鎖阻塞查詢結果展示

要找到並結束阻塞當前查詢的會話，可以使用以下語句。

SELECT PG_TERMINATE_BACKEND(pid);

如果是歷史的鎖阻塞導致的效能問題，可以透過下面語句查詢指定時間段內的資料庫等待事件。如果發現有大量的acquire lock（包括transaction ID、relation、tuple）事件，表示該時間段內資料庫存在鎖阻塞問題。

select wait_status,event,count(*) from gs_asp where sample_time>='20241016 18:45:00' and sample_time <='20241016 19:00:00' group by 1,2 order by 3 desc;

ASP（Active Session Profile，活躍會話概要資訊），透過取樣例項中活躍會話的狀態資訊，以低成本的方式復現過去一段時間內的系統活動，主要包含會話基本資訊、會話事務、執行的語句，等待事件，會話狀態（如active、idle等）、當前正阻塞在哪個事件上、正在等待哪個鎖或被哪個會話阻塞。

如圖19所示，該時間段資料庫佔比最高的兩個等待事件，一個是等待dn_6004_6005_6006分片返回執行結果，這需要進一步排查該分片上效能瓶頸的原因；另外一個等待事件是acquire lock（relation），表示存在大量的表級鎖等待。

圖19 特定事件內資料庫佔比最高的兩個等待事件

結合資料庫的歸一化檢視，可以獲取資料庫中存在鎖等待的SQL語句，如圖20所示：

圖20 獲取資料庫中存在鎖等待的SQL語句

透過該語句的Unique_query_id，獲得查詢阻塞該語句的query_id。

execute direct on datanodes $$select t1.unique_query_id,t1.thread_id,t1.sessionid,t1.wait_status,t1.event,t1.state,t2.query_id as lock_query_id from gs_asp t1,gs_asp t2 where t1.block_sessionid=t2.sessionid and  t1.unique_query_id=168353725$$;

如圖21所示，lock_query_id 為阻塞該SQL語句的query_id。

圖21 獲取阻塞鎖等待SQL語句的query_id

利用上一步查詢出來的query_id，並結合gs_asp檢視，可以透過如下語句獲取該SQL語句的詳情。查詢結果如圖22所示，可以看到，阻塞該語句的也是同一張表的update語句，這表明是由於併發更新同一行資料所導致的鎖衝突。

圖22 鎖等待的SQL語句查詢結果

通常情況下，解決併發更新鎖衝突問題的解決思路需要從業務角度出發，審視存在併發更新同一行的情況是否符合業務場景。如果業務中不存在這樣的場景，那應該從業務邏輯或者業務資料上進行最佳化，以避免併發更新同一行的情況發生。

4. 總結

資料庫效能調優涉及硬體、作業系統、資料庫、應用等多個層面，因此，在效能調優過程中，需要綜合考慮各方面因素的影響。本文介紹了在GaussDB中分析效能問題時常見的手段和思路，幫助大家熟悉GaussDB資料庫效能診斷常用的工具及使用方法。

華為開發者空間，匯聚鴻蒙、昇騰、鯤鵬、GaussDB、尤拉等各項根技術的開發資源及工具，致力於為每位開發者提供一臺雲主機、一套開發工具及雲上儲存空間，讓開發者基於華為根生態創新。點選連結，免費領取您的專屬雲主機。

點選關注，第一時間瞭解華為雲新鮮技術~

相關文章