濮阳杆衣贸易有限公司

主頁 > 知識庫 > 修改一行代碼提升 Postgres 性能 100 倍

修改一行代碼提升 Postgres 性能 100 倍

熱門標簽:青白江400企業(yè)電話申請 小裙科技電銷機器人怎樣 呼和浩特外呼系統(tǒng)原理是什么 長沙電銷外呼防封卡是什么 內(nèi)蒙古營銷智能外呼系統(tǒng)哪個好 crm外呼系統(tǒng)聯(lián)系方式 智能外呼系統(tǒng)官網(wǎng) 河南電話外呼系統(tǒng)招商 外呼線路資源屬于電信業(yè)務嗎

在一個(差)的PostgreSQL 查詢中只要一個小小到改動(ANY(ARRAY[...])to ANY(VALUES(...)))就能把查詢時間從20s縮減到0.2s。從最簡單的學習使用 EXPLAIN ANALYZE開始,到學習使用 Postgres community 大量學習時間的投入將有百倍時間到回報。

使用Postgres監(jiān)測慢的Postgres查詢

在這周早些時候,一個用于我們的圖形編輯器上的小表(10GB,1500萬行)的主鍵查詢,在我們的一個(多個)數(shù)據(jù)庫上發(fā)生來大的查詢性能問題。

99.9%到查詢都是非常迅速流暢的,但是在一些使用大量的枚舉值的地方,這些查詢會需要20秒。花費如此多到時間在數(shù)據(jù)庫上,意味著使用者必須在瀏覽器面前等待圖形編輯器的響應。很明顯只因為這0.01%就會造成很不好到影響。

查詢和查詢計劃

下面是這個出問題的查詢

復制代碼 代碼如下:

SELECT c.key,
       c.x_key,
       c.tags,
       x.name
 FROM context c
 JOIN x
   ON c.x_key = x.key
WHERE c.key = ANY (ARRAY[15368196, -- 11,000 other keys --)])
  AND c.x_key = 1
  AND c.tags @> ARRAY[E'blah'];

表X有幾千行數(shù)據(jù),表C有1500萬條數(shù)據(jù)。兩張表的主鍵值“key”都有適當?shù)乃饕?。這是一個非常簡單清晰的主鍵查詢。但有趣的是,當增加主鍵內(nèi)容的數(shù)量,如在主鍵有11,000個值的時候,通過在查詢語句上加上 EXPLAIN (ANALYZE, BUFFERS)我們得到如下的查詢計劃。

復制代碼 代碼如下:

Nested Loop  (cost=6923.33..11770.59 rows=1 width=362) (actual time=17128.188..22109.283 rows=10858 loops=1)
  Buffers: shared hit=83494
  ->  Bitmap Heap Scan on context c  (cost=6923.33..11762.31 rows=1 width=329) (actual time=17128.121..22031.783 rows=10858 loops=1)
        Recheck Cond: ((tags @> '{blah}'::text[]) AND (x_key = 1))
        Filter: (key = ANY ('{15368196,(a lot more keys here)}'::integer[]))
        Buffers: shared hit=50919
        ->  BitmapAnd  (cost=6923.33..6923.33 rows=269 width=0) (actual time=132.910..132.910 rows=0 loops=1)
              Buffers: shared hit=1342
              ->  Bitmap Index Scan on context_tags_idx  (cost=0.00..1149.61 rows=15891 width=0) (actual time=64.614..64.614 rows=264777 loops=1)
                    Index Cond: (tags @> '{blah}'::text[])
                    Buffers: shared hit=401
              ->  Bitmap Index Scan on context_x_id_source_type_id_idx  (cost=0.00..5773.47 rows=268667 width=0) (actual time=54.648..54.648 rows=267659 loops=1)
                    Index Cond: (x_id = 1)
                    Buffers: shared hit=941
  ->  Index Scan using x_pkey on x  (cost=0.00..8.27 rows=1 width=37) (actual time=0.003..0.004 rows=1 loops=10858)
        Index Cond: (x.key = 1)
        Buffers: shared hit=32575
Total runtime: 22117.417 ms

在結(jié)果的最底部你可以看到,這個查詢總共花費22秒。我們可以非常直觀的通過下面的CPU使用率圖觀察到這22秒的花費。大部分的時間花費在 Postgres和 OS 上, 只有很少部分用于I/O .



在最低的層面,這些查詢看起來就像是這些CPU利用率的峰值。CPU圖很少有用,但是在這種條件下它證實了關鍵的一點:數(shù)據(jù)庫并沒有等待磁盤去讀取數(shù)據(jù)。它在做一些排序,哈希以及行比較之類的事情。

第二個有趣的度量,就是距離這些峰值很近的軌跡,它們是由Postgres“取得”的行數(shù)(本例中沒有返回,就看看再忽略掉吧)。



顯然有些動作在規(guī)則的有條不紊的瀏覽過許多行:我們的查詢。

Postgres 的問題所在:位圖掃描
下面是行匹配的查詢計劃

復制代碼 代碼如下:

Buffers: shared hit=83494
-> Bitmap Heap Scan on context c (cost=6923.33..11762.31 rows=1 width=329) (actual time=17128.121..22031.783 rows=10858 loops=1)
Recheck Cond: ((tags @> '{blah}'::text[]) AND (x_key = 1))
Filter: (key = ANY ('{15368196,(a lot more keys here)}'::integer[]))
Buffers: shared hit=50919


Postgres 使用位圖掃描表C. 當主鍵的數(shù)據(jù)量小的時候,它能有效的使用索引在內(nèi)存里建立位圖。如果位圖太大,最優(yōu)查詢計劃就改變查詢方式了。在我們這個查詢中,因為主鍵包含的數(shù)據(jù)量很大,所以查詢就使用最優(yōu)(系統(tǒng)自己判斷的)的方式去檢索查詢候選行,并且立即查詢所有和主鍵匹配的數(shù)據(jù)。就是這些¨放入內(nèi)存¨和¨立即查詢¨花費太多的時間(查詢計劃中的Recheck Cond)。

幸好只有30%的數(shù)據(jù)被導入到內(nèi)存中,所以還不至于像從硬盤里讀取那么壞。但它仍然對性能有非常明顯的影響。記住,查詢是非常簡單的。這是一個主鍵查詢所以沒有很多明了的方式來確定它有沒有戲劇性的重新架構數(shù)據(jù)庫或應用程序。PGSQL-Performance mailing list給予了我們很大的幫助.

解決方案

這是我們喜歡開源和喜歡幫助用戶的另外一個原因。Tom Lane是開源代碼作者中最盛產(chǎn)的程序員之一,他建議我們做如下嘗試:

復制代碼 代碼如下:

SELECT c.key,
       c.x_key,
       c.tags,
       x.name
 FROM context c
 JOIN x
   ON c.x_key = x.key
WHERE c.key = ANY (VALUES (15368196), -- 11,000 other keys --)
  AND c.x_key = 1
  AND c.tags @> ARRAY[E'blah'];

把ARRAY改成VALUES,你能指出他們的不同點嗎?

我們使用ARRAY[...]列舉出所有的關鍵字以用來查詢,但是這卻欺騙了查詢優(yōu)化器。然而Values(...)卻能夠讓優(yōu)化器充分使用關鍵字索引。僅僅是一行代碼的改變,并且沒有產(chǎn)生任何語義的改變。

下面是新查詢語句的寫法,差別就在于第三和第十四行。

復制代碼 代碼如下:

Nested Loop  (cost=168.22..2116.29 rows=148 width=362) (actual time=22.134..256.531 rows=10858 loops=1)
  Buffers: shared hit=44967
  ->  Index Scan using x_pkey on x  (cost=0.00..8.27 rows=1 width=37) (actual time=0.071..0.073 rows=1 loops=1)
        Index Cond: (id = 1)
        Buffers: shared hit=4
  ->  Nested Loop  (cost=168.22..2106.54 rows=148 width=329) (actual time=22.060..242.406 rows=10858 loops=1)
        Buffers: shared hit=44963
        ->  HashAggregate  (cost=168.22..170.22 rows=200 width=4) (actual time=21.529..32.820 rows=11215 loops=1)
              ->  Values Scan on "*VALUES*"  (cost=0.00..140.19 rows=11215 width=4) (actual time=0.005..9.527 rows=11215 loops=1)
        ->  Index Scan using context_pkey on context c  (cost=0.00..9.67 rows=1 width=329) (actual time=0.015..0.016 rows=1 loops=11215)
              Index Cond: (c.key = "*VALUES*".column1)
              Filter: ((c.tags @> '{blah}'::text[]) AND (c.x_id = 1))
              Buffers: shared hit=44963
Total runtime: 263.639 ms

查詢時間從22000ms下降到200ms,僅僅一行代碼的改變效率就提高了100倍。 

在生產(chǎn)中使用的新查詢
即將發(fā)布的一段代碼:




它使數(shù)據(jù)庫看起來更美觀輕松.






第三方工具
postgres慢查詢不存在了。但是有誰樂意被0.1%不幸的少數(shù)折磨。要立即驗證修改查詢的影響,就需要Datadog來幫助我們判斷修改是否是正確的。

如果你想要找出對Postgres查詢改變的影響,可能需要幾分鐘來注冊一個免費的Datadog賬號。

英文原文:100x faster Postgres performance by changing 1 line

標簽:舟山 呼倫貝爾 黃石 楚雄 菏澤 安順 白山 池州

巨人網(wǎng)絡通訊聲明:本文標題《修改一行代碼提升 Postgres 性能 100 倍》,本文關鍵詞  修改,一行,代碼,提升,Postgres,;如發(fā)現(xiàn)本文內(nèi)容存在版權問題,煩請?zhí)峁┫嚓P信息告之我們,我們將及時溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡,涉及言論、版權與本站無關。
  • 相關文章
  • 下面列出與本文章《修改一行代碼提升 Postgres 性能 100 倍》相關的同類信息!
  • 本頁收集關于修改一行代碼提升 Postgres 性能 100 倍的相關信息資訊供網(wǎng)民參考!
  • 推薦文章
    寻甸| 海丰县| 丰原市| 隆林| 涟水县| 库伦旗| 西安市| 成武县| 黄龙县| 荆门市| 大姚县| 海伦市| 兴业县| 泗阳县| 广昌县| 焦作市| 汝阳县| 云南省| 辽阳县| 都安| 延津县| 乐亭县| 西乌珠穆沁旗| 通许县| 无锡市| 东阿县| 顺平县| 城步| 梅州市| 博爱县| 太和县| 五大连池市| 广宗县| 临清市| 涿州市| 衡阳县| 饶平县| 正镶白旗| 墨脱县| 临城县| 长丰县|