最佳實踐：解讀GaussDB(DWS) 統計信息自動收集方案

来源:https://www.cnblogs.com/huaweiyun/p/18347040
-Advertisement-
Play Games

摘要：現在商用優化器大多都是基於統計信息進行查詢代價評估，因此統計信息是否實時且準確對查詢影響很大，特別是分散式資料庫場景。本文詳細介紹GaussDB(DWS)如何實現了一種輕量、實時、準確的統計信息自動收集方案。 本文分享自華為雲社區《【最佳實踐】GaussDB(DWS) 統計信息自動收集方案》， ...


摘要：現在商用優化器大多都是基於統計信息進行查詢代價評估，因此統計信息是否實時且準確對查詢影響很大，特別是分散式資料庫場景。本文詳細介紹GaussDB(DWS)如何實現了一種輕量、實時、準確的統計信息自動收集方案。

本文分享自華為雲社區《【最佳實踐】GaussDB(DWS) 統計信息自動收集方案》，作者： leapdb。

一、統計信息收集痛點

  1. 何時做analyze，多做空耗系統資源，少做統計信息不及時。
  2. 多個數據源併發加工一張表，手動analyze不能併發。
  3. 數據修改後立即查詢，統計信息實時性要求高。
  4. 需要關心每張表的數據變化和治理，消耗大量人力。

二、基本功能介紹

三、自動收集方案

GaussDB(DWS) 支持統計信息自動收集功能，主要解決統計信息收集不及時和不准確的問題。

手動採樣：用戶在作業中，手動發起的顯示analyze。

輪詢採樣：autovacuum後臺線程，輪詢發起的analyze。

動態採樣：查詢時，優化器觸發的runtime analyze。

前臺動態採樣：負責統計信息實時準確，信息放記憶體（有淘汰機制），一級鎖（像查詢一樣輕量）。

autoanalyze=on;
autoanalyze_mode='light';

後臺輪詢採樣：負責統計信息的持久化，寫系統表（四級鎖），不要求特別及時。

autovacuum_mode=mix或analyze;
--- 以前只有“後臺輪詢採樣”，都由後臺autovacuum線程式控制製做vacuum或analyze。
--- 後來開發“前臺動態採樣”，叫autoanalyze。
--- 請註意二者的區別。

二者都需要開啟。

替代場景

統計信息基於收集時表數據生成，數據變化較多後可能失效。自動觸發也是基於閾值（50+表大小*10%）。

總結：

  1. 小表變化<10%且數據特征變化明顯，需要“調低閾值自動收集”。
  2. 調整過採樣大小且實時性要求高的場景，需要“主動收集統計信息”。
  3. 外表和冷熱表因訪問性能問題，不支持自動，需要“主動收集統計信息”。

四、如何保證及時觸發

【觸發條件】“無統計信息” or “表的修改量超過一定閾值（預設“50 + 表大小 * 10%”）”

【觸發場景】含stream計劃的SQL都可觸發動態採樣，包括select和帶條件的delete, update。

【修改計數】

1. 哪些修改行為會被記錄？

DML: Insert, Update, Delete, Copy, Merge，會累加修改計數。

DDL: truncate table，truncate/exchange/drop partition, alter column type, alter distribute，由於CN無法獲取DN修改計數，所以直接記錄一個超大修改計數。

2. 跨CN查詢場景，如何確保修改計數全局一致？

非同步廣播：autovacuum後臺線程輪詢檢查時，向所有CN廣播全局修改計數。修改計數達2/3時廣播一次，此後每增10%再廣播一次。

實時廣播：單SQL修改超過tuple_change_sync_threshold(預設1W)條時，直接實時廣播修改計數到其它CN。

總結：“修改計數記錄”和“修改計數廣播”，覆蓋都比較全面，能夠保證查詢及時觸發動態採樣。

五、最佳實踐

GaussDB(DWS) analyze使用指南8.1.3及以下版本

GaussDB(DWS) analyze使用指南8.2.0及以上版本

1.事務塊中手動analyze堵塞其它業務

【業務場景】

BEGIN;
ANALYZE t_ucuser;
INSERT INTO t_user_name(project_id, account_id, name_id, uid, etl_time)
with t1 AS (
select project_id, account_id, name_id
from t_user_name
WHERE uid is null or uid = ''
)
select a.project_id,a.account_id,a.name_id, b.user_name AS uid, CURRENT_TIMESTAMP AS etl_time
from t1 a join t_ucuser b ON a.project_id = b.project_id AND a.account_id = b.account_id
ON CONFLICT(project_id,account_id,name_id) DO UPDATE
SET project_id=excluded.project_id, account_id=excluded.account_id, name_id=excluded.name_id, uid=excluded.uid, etl_time=excluded.etl_time;
END;

【問題根因】

a. 某數據湖用戶，多個數據源按照不同的分區進行數據導入加工。

b. 事務塊中有手動analyze，且事務塊中後面的查詢長時間執行不完。

c. 因analyze對錶加四級鎖長時間不能釋放，導致其它相關表上的業務等鎖超時報錯。

【解決方案】開啟light動態採樣，去掉事務塊中的手動analyze。

2. 多數據源併發加工同一張表的不同分區

【業務場景】

為了保證用戶查詢表總有數據，需要把加工過程放到一個事務裡面。堵塞其它人的動態採樣。

begin;
alter table tab_partition truncate partition P2023_03;
insert into tab_partition select * from t1;
end;

【問題根因】alter table truncate parition對分區加8級鎖，事務過程中長時間持鎖。

【解決方案】使用exchange partition

CREATE TABLE IF NOT EXISTS tab_tmp1(like tab_partition INCLUDING DROPCOLUMNS INCLUDING DISTRIBUTION INCLUDING STORAGE INCLUDING RELOPTIONS);
INSERT INTO tab_tmp1 SELECT * FROM t1;
ALTER TABLE tab_partition exchange partition (P2023_03) WITH TABLE tab_tmp1;

3.多表併發反序analyze導致統計信息收集失敗

【業務場景】

a. 某銀行客戶，多個表進行批處理數據加工，開啟了normal類型動態採樣。

b. 查詢A先對t1表觸發動態採樣，再對t2表觸發動態採樣。

c. 查詢B先對t2表觸發動態採樣，再對t1表觸發動態採樣。

d. 觸發動態採樣的順序不一致，互相申請四級鎖導致申鎖超時，統計信息未收集。

【問題根因】多人同時按不同順序analyze多表導致死鎖。

【解決方案】開啟light動態採樣，僅加一級鎖不再有四級鎖衝突。

4.剛導入的數據不在統計信息中導致查詢計劃差

【業務場景】

a. 某財經用戶，按照月度視為會計期，月初時導入少量數據，然後馬上查詢。

b. 觸發了動態採樣，但採集不到最新會計期的少量數據。

【問題根因】新插入數據占比小，及時觸發了動態採樣但採集不到，導致估算偏差大。

【解決方案】

a. 開啟統計信息推算enable_extrapolation_stats功能，根據上一個會計期的統計信息推算當前會計期數據特征。

b. 不提高採樣大小，利用歷史信息增強統計信息準確性。

5.隨機函數質量差導致數據特征統計不准

【業務場景】

a. 某銀行客戶，按月度條件進行關聯查詢

b. 多次analyze，最多數據月份在MCV中占比從13%~30%大幅波動

c. 詳細輸出樣本點位置和採樣隨機數發現，隨機數（小數點後6位）生成重覆度高導致採樣扎堆兒嚴重。

【問題根因】採樣隨機數不夠隨機，樣本採集不均勻導致MCV數據特征統計偏差。

【解決方案】

a. 每次傳入隨機種子再生成隨機數，提高隨機性和併發能力。控制參數random_function_version。

b. 不提高採樣大小，提升隨機數質量增強統計信息準確性。

6.樣本分佈不均勻導致數據特征統計不准

【業務場景】

a. tpc-h的lineitem表l_orderkey列，數據每4~8條批量重覆。即同一個訂單購買多個商品。

b. 傳統採樣演算法由於採樣不均勻，採集到的重覆數據稍多，導致採集的distinct值偏低。

【問題根因】數據特征分佈不均勻，採樣無法抓準數據特征，distinct值高的場景統計出的distinct值偏低。

【解決方案】

a. 使用自研的優化蓄水池採樣演算法，控制參數analyze_sample_mode=2，讓採樣更加均勻，以提升統計信息準確性。

b. 如果上述方法沒有達到預期效果，可以手動修改distinct值。

select APPROX_COUNT_DISTINCT(l_orderkey) from lineitem; --近似計算distinct值
alter table lineitem alter l_orderkey set (n_distinct=10000); --手動設置distinct值，然後再analyze即可。

 

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


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 最全MySQL面試20題和答案(三)
    視圖 1. 為什麼要使用視圖？什麼是視圖？ 為了提高複雜 SQL 語句的復用性和表操作的安全性，MySQL 資料庫管理系統提供了視圖特性。所謂視圖，本質上是一種虛擬表，在物理上是不存在的，其內容與真實的表相似，包含一系列帶有名稱的列和行數據。但是，視圖並不在資料庫中以儲存的數據值形式存在。行和列數據 ...
  • SQL Server資料庫Owner導致事務複製log reader job無法啟動的解決辦法
    問題場景 SQL Server事務複製在正常創建發佈和訂閱之後，log reader Job 啟動異常，出現“The process could not execute ‘sp_replcmds’ on xxx”等異常日誌導致代理服務無法正常啟動。 異常現象 參考下圖，異常日誌如下 Error me ...
  • pg_dump與pg_restore的邏輯邏輯備份還原
    整理了一下pg_dump邏輯備份還原，pg啥時候推出一個庫級別的物理備份還原就好，邏輯備份能行但操作大庫效率太低，就像MySQL/MSSQL一樣，跨實例做庫級別還原的需求太多了 pg_dump備份 pg_dump備份 -F format 參數，備份文件的格式。format可以是下列之一： p pla ...
  • Percona Toolkit 神器全攻略（系統類）
    Percona Toolkit 神器全攻略（系統類） Percona Toolkit 神器全攻略系列共八篇，前文回顧： 前文回顧 Percona Toolkit 神器全攻略 Percona Toolkit 神器全攻略（實用類） Percona Toolkit 神器全攻略（配置類） Percona T ...
  • 最全MySQL面試20題和答案(二)
    索引 百萬級別或以上的數據如何刪除？ 關於索引：由於索引需要額外的維護成本，因為索引文件是單獨存在的文件，所以當我們對數據的增加、修改、刪除都會產生額外的對索引文件的操，這些操作需要消耗額外的IO，會降低增/改/刪的執行效率。所以，在我們刪除資料庫百萬級別數據的時候，查詢MySQL官方手冊得知刪除數 ...
  • ClickHouse生產環境部署
    背景ClickHouse是一個面向分析型的開源列式資料庫管理系統,它主要應用於以下幾個場景: 數據倉庫和商業智能分析:ClickHouse擅長處理大規模的數據,可以用於構建企業級的數據倉庫,支持複雜的OLAP查詢,可用實時數倉，適合各種商業分析和報表應用。 實時分析和監控:ClickHouse以毫秒 ...
  • 2.3.6版本發佈！Apache SeaTunnel Zeta引擎迎來新架構！
    Apache SeaTunnel 2.3.6 版本於近日正式發佈，社區期待的 SeaTunnel Zeta Master/Worker 新架構、事件通知機制、支持動態編譯的transform等新功能和新能力在這次版本中都有了全面的更新，並添加了首個向量資料庫 Milvus。此外，本版本還進行了一些基 ...
  • B站基於Apache DolphinScheduler的一站式大數據集群管理平臺（BMR）初窺
    一、背景 大數據服務是數據平臺建設的基座，隨著B站業務的快速發展，其大數據的規模和複雜度也突飛猛進，技術的追求也同樣不會有止境。 B站一站式大數據集群管理平臺（BMR），在千呼萬喚中孕育而生。本文簡單介紹BMR的由來、面臨的主要矛盾以及如何在變化中求得生存與發展。 下圖是截至2024年6月初，統計到 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類