華為集團IT PostgreSQL運用實踐（32頁）.pdf

《華為集團IT PostgreSQL運用實踐（32頁）.pdf》由會員分享，可在線閱讀，更多相關《華為集團IT PostgreSQL運用實踐（32頁）.pdf（32頁珍藏版）》請在三個皮匠報告上搜索。

1、華為集團IT PostgreSQL運用實踐陳華軍目錄n 流程IT去O與上云n 如何用好PostgreSQLn 常見問題與對策n 未來展望要致富、先修路提升客戶業務能力,提升產品能力變革與IT投入xxxx名流程IT員工IPD+、ISC+、CRM+、IFS大平臺支撐精兵作戰年度處理xxx萬合同x.xx億行PO年增長50%以上600+IT應用分布全球的IT基礎設施每天發200萬封郵件，5萬次會議，每天跟外部第三方會議超過400次投入持續變革數據IT辦公收入8500億RMB業務 2B、2C、Cloud BU xxx萬基站 1.4億部手機全球運營 170個國家 20多個運營中心全球協同 19萬員工 6萬

2、伙伴 900個辦公地點全球研發 15個研發中心 36個聯合創新中心流程IT-服務19萬人的企業IT類別華為云-MySQL實例數8000+CPU10W U+內存TB級存儲PB級類別華為云-PG實例數4000+CPU7w U+內存TB級存儲PB級華為云MySQL：華為云PostgreSQL：上述為集團內部遷移到華為云的部分數據2020年開始華為集團內部的數據庫從線下自建庫(Oracle,PG,MySQL)遷移到華為云RDS服務，并大規模采用PG去O，目前去O和上云工作已完成一半RDS：開源數據庫服務RDS for MySQL/PostgreSQLGaussDB(for Mongo)(開放生態)Ga

3、ussDB(for Cassandra)(開放生態)GaussDB(for Influx)(開放生態)非關系型數據庫服務GaussDB(for openGauss)(華為開源生態)GaussDB(for MySQL)(開放生態)關系型數據庫服務GaussDB(for Redis)(開放生態)GaussDB：基于統一的DFV技術架構打造的自研數據庫服務數據倉庫服務 GaussDB(DWS)公有云HCSOHCS流程IT數據庫去O上云進展n 體量大n 業務復雜n 核心系統n 時間緊無退路涉及ERP，制造，財經等關鍵的核心系統，對目標庫的功能，性能和可靠性都有非常的要求流程IT去O的困難與挑戰項目My

4、SQLPostgreSQLSQL 語法SQL特性支持36種，SQL語法支持比較弱SQL特性94種，SQL語法支持最完善主從復制邏輯復制物理流復制、邏輯復制復制安全性5.7開始支持半同步物理同步復制，強一致分區表支持支持物化視圖不支持支持索引類型 主要是btree 索引，不支持函數索引 多種索引類型，支持函數索引并行查詢只支持主鍵并行支持多種并行查詢算法業務場景傾向類互聯網應用（邏輯在業務層，最終一致性）場景企業應用（財經、運營商、CRM、物聯網等數據量較大，一致性要求高）場景n MySQL：華為商城等類互聯網應用n PostgreSQL/openGauss：ERP，制造，財經等企業應用交易庫去

5、O的目標庫選型序號挑戰解決方案1數據容量大，不能直接切換1.數據瘦身：識別關鍵資產，清理/歸檔數據2.在完成瘦身的基礎上，再考慮是否需要分庫分表2耦合嚴重，共schema1.按應用拆分Schema，先完成重構、剝離、拆分2.完成整改后，按微服務為單元一次性切換數據庫3大量使用存儲過程1.整改SQL：使用標準SQL，不使用數據庫特殊FEATURE2.數據庫作為存儲層，Java實現業務邏輯層，不在數據庫中寫復雜邏輯SQL4結合服務化改造，在切換過程中存在新老系統并存1.根據業務需要，再按領域/區域逐個搬遷2.如果下游未切換，則需要保持老系統數據是全量數據庫去O準備工作：數據清理、應用解耦、服務化基

6、于成熟的工具和專家經驗，為產品團隊提供數據庫引擎推薦、資源評估和遷移難度分析，評估效率從月至天。實施階段通過平臺和工具輔助代碼遷移和數據遷移。MySQLPostgreSQLGaussDBSQL代碼對象數據對象性能數據SQLPackageViewProcedure表索引分區表CPUIO內存磁盤代碼遷移數據遷移資源推薦專業標準評估高效快速遷移MySQLPGORACLE華為云數據庫遷移財經供應主干交易銷售研發HR遷移評估數據庫遷移：通過平臺服務快速遷移到基于華為云的數據庫生態目錄n 流程IT去O與上云n 用好PostgreSQLn 常見問題與對策n 未來展望要點描述磁盤級高可用自研分布式存儲(EVS

7、)采用3副本方式，單副本損壞，業務零感知。反親和性同一實例的不同節點所在的VM創建在不同的物理機上，主機故障時只影響一個節點。同城高可用充分利用多AZ實現高可用方案，避免單個機房事故導致業務中斷。異地高可用跨region災備節點，滿足容災訴求。自動故障切換主節點故障，自動切備機，切VIP。自動復制降級配置為華為云PG最大可用模式時，備機故障，主庫自動降級為異步復制，備機恢復時自動恢復同步復制。n 一主一備/一主兩備+只讀+異地容災n 同步復制+自動降級n 基于JDBC多主機URL的讀寫分離同城多AZ部署異地容災備只讀只讀主災備部署架構參數說明lock_timeout當某個表上的長事務阻塞DDL

8、時，這個表上的所有訪問都會被阻塞，引發雪崩效應。強烈建議設置合理的lock_timeout，比如1分鐘或5分鐘，及時中斷阻塞。hot_standby_feedback可以在只讀庫上設置為on，避免只讀庫的查詢和回放死元組清理WAL記錄的后臺任務發生沖突。但是必須同時做好只讀庫上長事務，未決2PC事務以及失效和滯后復制槽的監控，并及時處理，否則會導致主庫的表發生膨脹。n 云上RDS的參數默認值都已經過調優，但個別參數仍需要用戶根據場景定制。比如:n 數據庫初始化配置CREATE DATABASE$dbname LC_COLLATE C LC_CTYPE en_US.UTF-8 ENCODING

9、UTF-8 TEMPLATE template0;參數調優分類配置配置建議備注連接參數URLjdbc:postgresql:/$host:5432/$dbname?tcpKeepAlive=true&connectTimeout=5&defaultRowFetchSize=2000n 如遇到數據類型轉換的兼容問題，可嘗試設置stringtype=unspecified。設置stringtype=unspecified可使服務端更積極的做數據類型轉換，兼容性更好。比如支持使用setString()對int字段賦值。n 測試環境調查問題，可臨時設置“loggerLevel=DEBUG&logger

10、File=/tmp/jdbc.log”打開詳細日志連接池連接池組件建議dbcp2，HikariCP。不建議使用druiddruid使用select 1對池中的空閑PG連接進行檢活，容易產生空閑長事務；并且不支持連接生命周期控制最小連接數建議設置為1，最大不超過5dbcp2：minIdle，maxIdleHikariCP：minimumIdle最大連接數n 所有應用連接池最大連接數合計不超過數據庫最大連接數n 能滿足業務需求的前提下，盡量設小dbcp2：maxTotalHikariCP：maximumPoolSize連接生命周期務必設置以便及時釋放會話級緩存和臨時表（臨時表會影響事務凍結），比如

11、1小時dbcp2：maxConnLifetimeMillisHikariCP：maxLifetimen TIPS：根據業務特征設置合理的查詢超時（QueryTimeout）大量數據更新，盡量使用JDBC批更新，合理控制單個批次更新的數據量，比如2000盡量使用JDBC PraparedStatment，對簡單SQL性能可提升1倍，并且可預防SQL注入PG緩存的執行計劃消耗內存過大或不適用于所有的參數值時，在充分評估影響的前提下可考慮關閉服務端預編譯(prepareThreshold=0)客戶端配置（JDBC）n 除了常規的OS資源監控，數據庫年齡，長事務等PG特有的監控也必不可少CPU內存IO

12、網絡SWAP存儲空間生死(連通性）連接數活動連接數數據庫年齡長事務存活時間未決2PC事務存活時間失效的復制槽為復制槽保留的WAL大小復制延遲OSDB監控告警會話信息：pg_stat_activitySQL及執行計劃：pg_stat_statementsauto_explain鎖信息：pg_locks基于豐富的商業數據庫運維經驗，構建PG數據庫性能診斷工具，支撐應用開發團隊及時發現應用中的慢SQL、鎖爭用，提升應用健壯性和用戶體驗。實時會話歷史會話對象鎖阻塞鎖TOP SQL慢 SQLSQL 查詢SQL診斷KILL會話鎖分析會話分析SQL分析建模采集加工數據庫性能診斷工具面向企業數據庫開發者，提供

13、覆蓋MySQL、PG、Gauss等10+數據庫類型的查詢、導出、變更等操作統一管理WEB IDE；融入了行列鑒權控制、防數據庫高危操作、數據庫設計規范、SQL規范、數據閃回、變更穩定性控制等企業安全穩定特性。數據庫開發者服務工具備份存儲池（OBS）歸檔WALPostgreSQL業務數據庫（RDS）WAL解析環境（ECS）每5分鐘定時增量備份(即打包上傳歸檔WAL)歸檔WALPostgreSQL數據字典解析結果1.下載歸檔WAL2.導入數據字典select from pg_classselect from pg_attribute3.解析WALn 對業務庫0侵入n 大部分場景分鐘級恢復WAL解析

14、插件PostgreSQL閃回實現目錄n 流程IT去O與上云n 用好PostgreSQLn 常見問題與對策n 未來展望n大部分時候PG的優化器工作得很好，但某些特定場景下，容易產生不合理的執行計劃。原因對策統計信息采集不及時n 手動執行analyzen 調整庫級或表級參數autovacuum_analyze_scale_factor大表采樣統計信息不準確n default_statistics_target參數調大n ALTER TABLE.ALTER COLUMN.SET STATISTICSn ALTER TABLE.ALTER COLUMN.SET(n_distinct=.)多字段條件組合

15、的選擇率估算不準確n CREATE STATISTICS.緩存的執行計劃不適用所有代入參數n 代碼中不使用預編譯語句n 數據庫中設置plan_cache_mode為force_custom_plann JDBC連接字符串中設置參數prepareThreshold=0優化器算法缺陷n pg_hint_plann SQL改寫問題1.不合理的執行計劃create table tb1(id int primary key,c1 int);create index on tb1(c1);insert into tb1 select id,id/10000 from generate_series(1,1

16、0000000)id;postgres=#explain analyze select*from tb1 where c1=999 order by id limit 10;QUERY PLAN-Limit(cost=0.43.332.29 rows=10 width=8)(actual time=1571.315.1571.319 rows=10 loops=1)-Index Scan using tb1_pkey on tb1 (cost=0.43.328935.03 rows=9912 width=8)(actual time=1571.314.1571.316 rows=10 loop

17、s=1)Filter:(c1=999)Rows Removed by Filter:9989999 Planning Time:0.112 ms Execution Time:1571.337 ms(6 rows)n表定義&數據分布nSQL&執行計劃上面Index Scan估算的行數和cost都比較準確，但評估LIMIT子句時，優化器假設數據分布是均勻的，只需掃描主鍵索引的10/9912即可找到10條匹配的記錄，最終的估算代價也被LIMIT降到10/9921。但實際上滿足條件的記錄都集中在索引的尾部。例1：LIMIT子句代價估算偏差select*from tb1 where c1=999 or

18、der by id+0 limit 10;WITH t AS MATERIALIZED(select*from tb1 where c1=999)select*from t order by id limit 10nSQL改寫方法1：破壞索引排序nSQL改寫方法2：物化子查詢改寫后，本例中的SQL執行時間從1571ms減少到4msSQL改寫優化create table tb1(c1 int,c2 int,d int);create table tb2(c1 int,c2 int);create table tb3(d int);create index on tb3(d);insert int

19、o tb1 select id,id,id/100 from generate_series(1,100000)id;insert into tb2 select id,id from generate_series(1,100000)id;insert into tb3 select id from generate_series(1,1000)id;select count(*)from tb1,tb2,tb3 where tb1.c1=tb2.c1 and tb1.c2=tb2.c2 and tb1.d=tb3.dn表定義&數據分布n查詢SQL：例2：多個Join條件組合Aggregat

20、e (cost=7021.51.7021.52 rows=1 width=8)(actual time=18448.825.18448.828 rows=1 loops=1)-Nested Loop (cost=3334.00.7021.51 rows=1 width=0)(actual time=38.280.18436.881 rows=99901 loops=1)Join Filter:(tb1.d=tb3.d)Rows Removed by Join Filter:99900099 -Hash Join (cost=3334.00.6994.01 rows=1 width=4)(act

21、ual time=29.513.155.524 rows=100000 loops=1)Hash Cond:(tb1.c1=tb2.c1)AND(tb1.c2=tb2.c2)-Seq Scan on tb1 (cost=0.00.1541.00 rows=100000 width=12)(actual time=0.006.10.360 rows=100000 loops=1)-Hash (cost=1443.00.1443.00 rows=100000 width=8)(actual time=29.408.29.409 rows=100000 loops=1)Buckets:131072

22、Batches:2 Memory Usage:2982kB -Seq Scan on tb2 (cost=0.00.1443.00 rows=100000 width=8)(actual time=0.005.7.975 rows=100000 loops=1)-Seq Scan on tb3 (cost=0.00.15.00 rows=1000 width=4)(actual time=0.003.0.077 rows=1000 loops=100000)Planning Time:0.166 ms Execution Time:18448.880 ms(13 rows)n執行計劃：上面對J

23、OIN結果集行數的估算出現巨大偏差，估算1行，實際10w行，并導致對tb3執行了10w次全表掃描。出現偏差的原因是：PG優化器假設2個Join條件對結果的過濾效果是互相獨立的。Join結果集行數=2表的笛卡爾積*Join條件1的選擇率*Join條件2的選擇率 Aggregate (cost=7284.62.7284.64 rows=1 width=8)(actual time=158.687.158.690 rows=1 loops=1)-Hash Join (cost=3361.50.7283.38 rows=500 width=0)(actual time=43.258.151.051 r

24、ows=99901 loops=1)Hash Cond:(tb1.d=tb3.d)-Hash Join (cost=3334.00.7249.00 rows=500 width=4)(actual time=42.365.127.613 rows=100000 loops=1)Hash Cond:(tb1.c1=tb2.c1)AND(tb1.c2+0)=(tb2.c2+0)-Seq Scan on tb1 (cost=0.00.1541.00 rows=100000 width=12)(actual time=0.022.12.525 rows=100000 loops=1)-Hash (co

25、st=1443.00.1443.00 rows=100000 width=8)(actual time=41.936.41.937 rows=100000 loops=1)Buckets:131072 Batches:2 Memory Usage:2982kB -Seq Scan on tb2 (cost=0.00.1443.00 rows=100000 width=8)(actual time=0.046.11.682 rows=100000 loops=1)-Hash (cost=15.00.15.00 rows=1000 width=4)(actual time=0.538.0.538

26、rows=1000 loops=1)Buckets:1024 Batches:1 Memory Usage:44kB -Seq Scan on tb3 (cost=0.00.15.00 rows=1000 width=4)(actual time=0.047.0.248 rows=1000 loops=1)Planning Time:0.306 ms Execution Time:159.037 ms修改后的SQL如下。SQL改寫后使用hash join，執行時間從18秒縮短到159ms把Join條件改成某些邏輯上的等價形式，影響行估算，進而改變執行計劃。比如tb1.c2=tb2.c2=tb1

27、.c2+0=tb2.c2+0nSQL改寫優化：本例中tb1.c2=tb2.c2基于統計信息估算出的選擇率是1/100000；tb1.c2+0=tb2.c2+0無法利用統計信息，使用固定的選擇率1/200PG代價估算算法可參考：http:/www.interdb.jp/pg/pgsql03.htmlnPostgreSQL每個會話對應一個進程，且每個后端進程緩存表定義和執行計劃，并發連接多時容易占用大量內存甚至觸發OOM。Temp_bufferswork_memmaintenance_work_memBackend memoryCacheMemoryCatCacheRelCacheCachedPl

28、an問題2.OOMnPostgreSQL每個會話對應一個進程，且每個后端進程緩存表定義和執行計劃，并發連接多時容易占用大量內存甚至觸發OOM。內存占用原因對策連接數過多n 調小應用端連接池大小n 設置最大連接生命周期n 拆分負載（讀寫分離，分庫分表等）n 部署pgbouncer連接池系統表緩存太大(通常pg_statistic和pg_attribute占用內存最大)n 避免不太分區字段的分區表訪問SQLn 減少分區數n 連接隔離（不同業務單元使用不同連接池）n 通過ALTER TABLE SET STATISTICS對不需要通過頻繁值和柱狀圖評估選擇性的字段，減少收集的統計信息n 數據庫端通過

29、LRU算法限制緩存大小(華為云內核定制功能)執行計劃緩存太大n 避免過多，過長的無法復用的預編譯語句,比如in($1,$2 參數多且個數不確定)n 客戶端限制每個連接緩存的預編譯語句數量(preparedStatementCacheQueries)n 關閉預編譯語句(prepareThreshold=0)n 連接隔離（不同業務單元使用不同連接池）n 數據庫端通過LRU算法限制緩存大小(華為云內核定制功能)頁表項占用內存太大n 對大內存環境開啟Huge Pagen子事務對應的功能是SAVEPOINT，即使不顯式使用SAVEPOINT，存儲過程中的BEGIN EXCEPTION塊會隱式的引入子事務

30、。FOR i IN 1.1000 LOOP BEGIN INSERT INTO t VALUES(i);EXCEPTION -每次循環產生一個子事務 WHEN OTHERS THEN NULL;END;END LOOP;n子事務的危害 事務ID快速增長，增加事務ID回卷風險 使用邏輯訂閱時，可能產生大量臨時小文件 單個會話中子事務數超過64后，進入suboverflow狀態，高并發場景下可能導致數據庫整體性能大幅下降問題3.子事務的潛在風險開啟邏輯訂閱時，walsender進程會在內存中保存每個活動事務的變更記錄，但是當一個事務修改的記錄數超過4096時，序列化變更記錄到臨時文件，同時順帶序列

31、化該事務的所有子事務的變更記錄。CREATE TABLE t(id int primary key);CREATE TABLE t2(id int primary key);CREATE OR REPLACE FUNCTION insert_data()RETURNS VOIDLANGUAGE plpgsqlAS$DECLARE i int;BEGIN FOR i in 1.10000000 LOOP BEGIN INSERT INTO t(c1)VALUES(i);EXCEPTION WHEN UNIQUE_VIOLATION THEN NULL;END;END LOOP;END$;SELE

32、CT pg_create_logical_replication_slot(test_slot,test_decoding);BEGIN;SELECT insert_data();-walsender中緩存了1000w個子事務的變更記錄INSERT INTO t2 SELECT generate_series(1,5000);-子事務的變更記錄落盤產生1000w個臨時文件1000w個子事務的變更記錄落盤導致walsender進程長時間CPU打滿，甚至可能撐爆磁盤，耗盡Inode。數據準備：pg_recvlogical-start-S test_slot-d postgres-f/tmp/tes

33、t_logical故障模擬：每個子事務產生一個小臨時文件，每個文件只有100多字節，但會占用4KB存儲空間。案例1.子事務+邏輯訂閱導致walsender卡死現象：數據庫性能下降，且出現大量subtrans和SubtransControlLock鎖(PG13以后為SubtransBuffer和SubtransSLRU鎖)案例2：子事務+高并發DML導致整庫hang參考：nhttps:/www.cybertec- 讀取元組的版本標識n 獲得subtrans輕量級鎖n 根據版本標識，獲得其父事務IDn 如果涉及的xmin的子事務SLRU未在共享內存buffer中n 獲得SubtransContro

34、lLock鎖n 從subtrans文件中讀取相應的頁n 釋放SubtransControlLock鎖n 在共享內存buffer中n 讀取相應的父事務ID值，讀取父事務ID值，n 釋放subtrans鎖原因：子事務溢出時的元組可見性判斷建議：謹慎使用子事務，特別是在大事務，長事務中1.每個會話最多可以緩存64個子事務XID，超出64的部分將溢出到子事務文件(pg_subxact)2.只要有一個會話發生子事務溢出(pgxact-overflowed)，此期間采集的事務快照（suboverflowed）在判斷元組可見性時，可能需要讀取子事務文件。3.大量會話高并發頻繁訪問子事務文件，產生鎖爭用，嚴重時導致整庫性能急劇下降注：讀寫分離場景下，主庫上子事務即使不發生溢出，備庫上的查詢也可能需要讀取子事務文件。目錄n 流程IT去O與上云n 用好PostgreSQLn 常見問題與對策n 未來展望雖然PostgreSQL還不完美，但只要正確使用，完全可以應用到企業的關鍵系統。隨著PostgreSQL用戶的不斷增加，功能和生態也將越來越完善。參考：https:/