在短暫的“忙于其他事情”(換工作,基因組倍增)之后,我認為是時候再次敲鍵盤以避免在寫作時變得過于“生疏”,同時也避免我的大腦在未來衰老,因為如果你忘記了一些細節,用谷歌搜索你自己過去的文章實際上是很好的,因此,從理論上講,最終甚至可能是凈正“投資”。
我認為做一些“考古學”會很酷,看起來有點超過通常的“verX vs verX+1”比較目標,從v10開始!這肯定很有趣,因為在這段時間里有很多變化——確切地說,大約有2000項“值得發布說明”的東西……令人難以置信!但令人遺憾的是,發布說明和文檔總體上對一些性能提升非常粗略(在黑客郵件中,你可以找到一些東西),甚至可能是出于一個很好的原因-肯定有無數不同的用例、配置調整和硬件組合,因此,在很短的時間內,這些籠統的語句可能看起來非??尚?記住,640KB的RAM應該足夠任何人使用。
那么,如何處理性能數據的模糊性呢?沒錯,正如你可能已經猜到的,是時候卷起袖子,把難以解決的事物放在一起進行一些試驗了!因此,請繼續閱讀我的簡單測試設置的一些細節,或者直接跳到底部了解我的一些發現。
請注意,我不想測試任何接近“大數據”的東西,而是“在內存中”,因此,如果您處理大量數據,并主要訪問磁盤,那么我猜整個練習可能看起來毫無意義。不過,它應該顯示出Postgres如何創建執行計劃、索引和完整掃描、解包元組以及進行一些數字處理的可能變化。
測試設置
有些事情在一年內不會改變—我選擇的武器是一把瑞士軍刀“pgbench”,這把刀可以用來進行一些涉及Postgres的“快速和骯臟/個人使用”風格測試,盡管這一次將一些自定義SQL混合到pot中,作為默認的OLTP風格測試場景有點片面,并且沒有說明Postgres也可以成功地用于更多的分析用例(盡管有一定的限制-默認情況下,它不使用列存儲或過于激進的壓縮)。因此,我添加了一些連接和“海量數據”類型的查詢,這在當時對我來說似乎是“常見”的,但并沒有給出太多,盡管老實說,因為很難得到一些確定的結果,所以請記住這一點。
測試查詢
-- Standard "tcp-b"-like --skip-some-updates
UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2;
SELECT abalance FROM pgbench_accounts WHERE aid = $1;
-- Full scan
SELECT count(*), avg(abalance) FROM pgbench_accounts;
-- Assumes a 4x reduced (aid % 4 = 0) clone of pgbench_accounts
SELECT count(*) FROM pgbench_accounts JOIN pgbench_accounts_reduced USING (aid);
-- Top 5 accounts for each branch - assumes a new index on pgbench_accounts(bid)
SELECT bid, abalance FROM pgbench_branches b JOIN LATERAL (SELECT abalance FROM pgbench_accounts WHERE bid = b.bid ORDER BY abalance DESC LIMIT 5) a ON true;
硬件
5x GCP e2-standard-4虛擬機,配備4個vCPU和16GB RAM,50GB標準永久磁盤(IOPS非常低)
軟件
操作系統:Debian 11(Bullseye)
Postgres:v10到v15,最新的次要版本和v15的Beta 2(在編寫本文時不再是最新版本)。在給定硬件的情況下,還應用了一些基本的“最佳實踐”配置調整(25%RAM shared_buffers,random_page_cost=1.25,max_parallel_workers_per_gather=1)。
工作集大小:每個查詢3個大小-適合共享緩沖區,適合RAM,稍微超過RAM,因此對慢速磁盤的一些輕度訪問應該會減弱Postgres方面可能的算法磁盤訪問改進。
測試運行時間:每個“工作集大小/規?!?小時,查詢協議(普通與擴展),查詢,PG版本組合
并行級別:我選擇了固定的“活動客戶端”計數2,以避免CPU最大化并避免一些上下文切換隨機性。
測量方法:Postgres內置“pg_stats_statements”擴展統計聚合
此外,這一次,與“舊時代”相比,您可能已經注意到,我決定在云虛擬機上運行測試,而不是真正的硬件……這似乎也在慢慢成為數據庫世界的一種常態,或者至少情況正在發生變化。
同樣,在選定的數據集大小上-是的,它幾乎都是“在內存中”,數據集很小,因此您可能會認為這與現實生活中的用例有點格格不入-但請記住,云存儲延遲/隨機性要比本地磁盤高得多,因此很難對軟件進行實際基準測試。此外,理論上(如果你有足夠的資金),你可以很容易地“點擊”自己。例如,現在AWS上有多TB的RAM,即使是大數據,也會給你一種非常類似的“大部分在內存中”的感覺。
高級別成果
在按下“回車”鍵后,我花了8天時間才終于能夠興奮地搓手……但令我恐懼的是,最初的喜悅很快變成了幾乎絕望-因為顯然有大量數據(900行)需要分析,比我預想的要多得多-組合數學有時肯定會令人驚訝。所以在一個小時的數據挖掘和比較各個行之后,我終于可以制定出我真正想要的,并可以使用窗口函數編寫一些分析查詢(注:如果你還沒有掌握它們,一定要去學習它們——它們是SQL最好的部分之一,說真的)。因此,不用多說,下面是一些比較數字。
請注意,最后,為了簡單起見,我實際上決定忽略“中間”的Postgres版本,現在只看v10和v15,因為它似乎太多了,無法很好地總結它……希望在未來解決它,因為這里有完整的數據,所以如果有時間,可以隨意查看下面的數字。

兩個異常
pgbench_accounts_reduced上的慢速加入
在查看查詢結果時,我注意到的第一件事當然是連接查詢的速度下降了123%——我想這一定是我的測試的原因!但在手動重新檢查之后-事實上,Postgres v15測試版選擇了比v10更糟糕的計劃!當然,這些事情時有發生,通??梢越鉀Q-但從3個自定義查詢中,我選擇了已經命中一個…
但不要太擔心-基于我10年來每天與Postgres合作的經驗,我可以證明這是一件非常罕見的事情,而且它是一個測試版。
順便說一下,從技術方面-在運行“解釋-分析”之后,我看到JIT已經啟動,所以我禁用了它,并認為這一定是它…但仍然得到了一個更慢的計劃,選擇了哈希連接而不是合并連接!雖然它的成本估算比v10合并計劃低(順便說一下,這通常是一種罕見的現象,也是一種“回退”連接類型),但從技術上講,Postgres做了它必須做的事情。只有在禁用哈希連接之后,我才能看到v15確實快了大約20%,這實際上非常好!
分析查詢的標準差惡化
嗯,stddev中有相當大的200%以上的變化……因此,為了以防萬一,再次在兩個版本上運行查詢:
select count(*), avg(abalance) from pgbench_accounts
Hhmm兩個版本都有完全相同的“并行序列掃描”計劃,因為這是一個非常簡單的查詢-而且奇怪的是,v15總是更快…但不知何故更加跳躍。我沒有想到什么明顯的東西-也許是“云”/“噪音鄰居”效應?
SELECT count(*) FROM pgbench_accounts JOIN pgbench_accounts_reduced USING (aid)
這里更容易理解,因為在前面的“慢速連接”段落中解釋了相同的查詢-JIT不必要地啟動+選擇了稍微慢一點的計劃。
SELECT bid, abalance FROM pgbench_branches b JOIN LATERAL (SELECT abalance FROM pgbench_accounts WHERE bid = b.bid ORDER BY abalance DESC LIMIT $1) a ON
同樣,JIT似乎啟動得太早了……但一旦在v15上禁用,它就正?;?,在運行時實際領先v10 40%!
試圖得出結論
好了,好了,現在來看看最難的部分-那么我從這個練習中學到了什么,如果有的話?一些想法:
-
請記住,我測試了一個V15beta2版本(遺憾的是,beta3在我開始測試后不久就發布了)
-
在從測試結果中排除具有上述詳細規劃異常的查詢后,所有查詢和規模的加速率僅為4%。不多對令人驚訝的不-事實上,這非常符合邏輯,因為PostgreSQL在我使用它的整個十年中一直堅如磐石,并且(大部分)優化得很好!此外,它是一個非常靜態的、主要是只讀的、幾乎是“內存中”的測試設置,其中許多最近的主要優化(索引重復數據消除、自下而上的索引清理等)無法顯現。
-
Postgres planner并不是最復雜的,它仍然可能會誤判事物,因此,與以往一樣,重要的是了解你的方法,解釋計劃、計劃常數和某些結構(如橫向結構),這些結構將Postgres推向你喜歡的計劃。
-
查詢協議(普通SQL與準備好的語句)對于更小/更快的查詢仍然發揮著巨大的作用-僅通過使用準備好的聲明,兩個非分析性簡單索引訪問查詢就獲得了20%和50%的提升!當然,這里沒有什么新鮮事,只是重復一下:)
-
對于這種相對較短的運行測試來說,云虛擬機似乎不太值得信賴-不同5臺主機上所有查詢/規模的統計變異系數(~7%)實際上高于測得的Postgres 4%的加速率(不包括離群查詢)…因此,下一次我仍然會再次啟動一些真正的硬件。
-
總而言之,一個非常不錯的性能從良好的舊v10,這實際上將在幾個月內下線!因此,那些仍然在安全網絡中運行的人(沒有安全更新,記?。。┒也幌胱屚C時間升級,至少不必太擔心性能部分。
-
請記住,測試性能很難,而且有很多事情我沒有測試-分區、嚴重并發/多用戶訪問、由于膨脹導致的長期性能下降。因此,我想象出來的自定義SQL查詢現在對我來說似乎相當片面(更多的是在分析方面),所以可能把它當作“某物”。
順便說一下,如果您想嘗試類似的東西,可以在這里找到我使用的腳本。此外,您只需單獨創建一個可從測試虛擬機訪問的Postgres“結果數據庫”,即可將結果推送到單個表中,以便通過SQL進行分析。此外,如果您在我的邏輯中看到一些明顯的錯誤或有其他想法,在評論部分獲得ping將是非常好的。
這次就這樣!希望在v15最終版發布時,能帶著類似的作品回來-同時好好照顧你的數據庫和其他寵物。
PS:特別感謝我的雇主Cognite對我的博客很友好,讓我可以在一周內燒掉這些CPU!順便說一句,我們還招聘了一些不同引擎上的數據庫工程師職位,所以如果你不介意我作為同事,為什么不去看看呢。
原文標題:5 years in PostgreSQL major versions performance - anything surprising?
原文作者:Kaarel Moppel
原文鏈接:https://kmoppel.github.io/2022-09-05-5-years-in-postgresql-major-versions-performance-anything-surprising/




