摘要： 標簽 PostgreSQL , 分區表 , bind , spin lock , 性能分析 , sleep 進程 , CPU空轉 , cache 背景 實際上我寫過很多文檔，關于分區表的優化： 《PostgreSQL 商用版本EPAS(阿里云ppas) - 分區表性能優化 (堪比pg_pathman)》 《PostgreSQL 傳統 hash 分區方法和性能》 《PostgreSQL 10 內置分區 vs pg_pathman perf profiling》 實際上native分區表的性能問題主要還是在于分區表過多的時候，執行計劃需要耗時很久。

點此查看原文：https://yq.aliyun.com/articles/405176?spm=a2c4e.11153959.teamhomeleft.44.8WKxt7

實際上native分區表的性能問題主要還是在于分區表過多的時候，執行計劃需要耗時很久。

因此有了

1、PPAS的edb_enable_pruning參數，可以在生成執行計劃前，使用簡單SQL的話，直接過濾到目標分區，從而不需要的分區不需要進入執行計劃的環節。

2、pg_pathman則支持的更全面，除了簡單SQL，復雜的表達式，immutable都可以進行過濾，過濾到目標分區，從而不需要的分區不需要進入執行計劃的環節。

因分區表過多引發的問題通常出現在OLTP系統（主要是OLTP系統的并發高，更容易把這種小問題放大），本來一次請求只需要1毫秒的，但是執行計劃可能需要上百毫秒，也就是說執行耗時變成了小頭，而執行計劃（SPIN LOCK）變成了大頭。

下面這個例子也是OLTP系統相關的，有具體的原因分析。

SQL訪問的分區表過多，并發高時CPU負載高，但是大量的是SLEEP狀態的BIND進程。

某個業務系統，單次SQL請求很快，幾十毫秒，但是并發一高，QPS并沒有線性的增長。

而且大量的進程處于BIND，SLEEP的狀態。

經過診斷，

《PostgreSQL 源碼性能診斷(perf profiling)指南》

《Linux 性能診斷 perf使用指南》

主要的原因是大量的SPIN LOCK，導致CPU空轉。

perf record -ag perf report -g

比如某個進程BIND時的pstack

#pstack 18423 #0 0x00002ad051f3ef67 in semop () from /lib64/libc.so.6 -- 這邊到了內核，上spin lock #1 0x0000000000656117 in PGSemaphoreLock () #2 0x00000000006c274a in LWLockAcquire () #3 0x00000000006bd136 in LockAcquireExtended () #4 0x00000000006b8768 in LockRelationOid () -- 對所有的子表都會調用這個函數，導致spinlock #5 0x000000000050c10a in find_inheritance_children () #6 0x000000000050c212 in find_all_inheritors () -- 找到所有子表 #7 0x0000000000645e4e in expand_inherited_tables () #8 0x000000000063a6e8 in subquery_planner () #9 0x0000000000618c4f in set_rel_size () #10 0x0000000000618e7c in set_rel_size () #11 0x0000000000619587 in make_one_rel () #12 0x0000000000636bd1 in query_planner () #13 0x000000000063862c in grouping_planner () #14 0x000000000063a9c4 in subquery_planner () #15 0x0000000000618c4f in set_rel_size () #16 0x0000000000619587 in make_one_rel () #17 0x0000000000636bd1 in query_planner () #18 0x000000000063862c in grouping_planner () #19 0x000000000063a9c4 in subquery_planner () #20 0x0000000000618c4f in set_rel_size () #21 0x0000000000619587 in make_one_rel () #22 0x0000000000636bd1 in query_planner () #23 0x000000000063862c in grouping_planner () #24 0x000000000063b0d0 in standard_planner () #25 0x00000000006d1597 in pg_plan_queries () #26 0x00000000007ca156 in BuildCachedPlan () #27 0x00000000007ca525 in GetCachedPlan () #28 0x00000000006d1d07 in exec_bind_message () #29 0x00000000006d44de in PostgresMain () #30 0x000000000066bd5f in PostmasterMain () #31 0x00000000005f474c in main ()

由于業務使用了prepared statement，所以過程會變成bind 過程

1、prepare statement

2、bind parameters

3、代入參數、(設置了constraint_exclusion時)判斷哪些分區需要被過濾

4、execute prepared statement

在find_all_inheritors過程中，涉及的分區表過多，最后每個分區都要取LOCK（后面加載了系統的spin lock），所以我們會看到CPU很高，同時大量的BIND，進程處于SLEEP狀態，也就是CPU空轉，CPU時間片被獨占的狀態。

spinlock (自旋鎖)?
自旋鎖是專為防止多處理器并發而引入的一種鎖，它在內核中大量應用于中斷處理等部分(對于單處理器來說，防止中斷處理中的并發可簡單采用關閉中斷的方式，不需要自旋鎖)。

自旋鎖最多只能被一個內核任務持有，如果一個內核任務試圖請求一個已被爭用(已經被持有)的自旋鎖，那么這個任務就會一直進行忙循環——旋轉——等待鎖重新可用。

要是鎖未被爭用，請求它的內核任務便能立刻得到它并且繼續進行。自旋鎖可以在任何時刻防止多于一個的內核任務同時進入臨界區，因此這種鎖可有效地避免多處理器上并發運行的內核任務競爭共享資源。

事實上，自旋鎖的初衷就是：

在短期間內進行輕量級的鎖定。一個進程去獲取被爭用的自旋鎖時，請求它的線程在等待鎖重新可用的期間進行自旋(特別浪費處理器時間)，所以自旋鎖不應該被持有時間過長（等待時CPU被獨占）。如果需要長時間鎖定的話, 最好使用信號量（睡眠，CPU資源可出讓）?
。

簡單的說，自旋鎖在內核中主要用來防止多處理器中并發訪問臨界區，防止內核搶占造成的競爭。另外自旋鎖不允許任務睡眠(持有自旋鎖的任務睡眠會造成自死鎖——因為睡眠有可能造成持有鎖的內核任務被重新調度，而再次申請自己已持有的鎖)，它能夠在中斷上下文使用。

死鎖：假設有一個或多個內核任務和一個或多個資源，每個內核都在等待其中的一個資源，但所有的資源都已經被占用了。這便會發生所有內核任務都在相互等待，但它們永遠不會釋放已經占有的資源，于是任何內核任務都無法獲得所需要的資源，無法繼續運行，這便?
意味著死鎖發生了。自死瑣是說自己占有了某個資源，然后自己又申請自己已占有的資源，顯然不可能再獲得該資源，因此就自縛手腳了。

spinlock特性：

防止多處理器并發訪問臨界區，

1、非睡眠（該進程/LWP(Light Weight Process)始終處于Running的狀態）

2、忙等 （cpu一直檢測鎖是否已經被其他cpu釋放）

3、短期（低開銷）加鎖

4、適合中斷上下文鎖定

5、多cpu的機器才有意義（需要等待其他cpu釋放鎖）

以下截取自

http://blog.sina.com.cn/s/blog_458d6ed5010110hv.html

Spinlock的目的是用來同步SMP中會被多個CPU同時存取的變量。在Linux中，普通的spinlock由于不帶額外的語義，是用起來反而要非 常小心。 在Linux kernel中執行的代碼大體分normal和interrupt context兩種。tasklet/softirq可以歸為normal因為他們可以進入等待

Spinlock的目的是用來同步SMP中會被多個CPU同時存取的變量。在Linux中，普通的spinlock由于不帶額外的語義，是用起來反而要非常小心。

在Linux kernel中執行的代碼大體分normal和interrupt context兩種。tasklet/softirq可以歸為normal因為他們可以進入等待；nested interrupt是interrupt context的一種特殊情況，當然也是interrupt context。Normal級別可以被interrupt搶斷，interrupt會被另一個interrupt搶斷，但不會被normal中斷。各個 interrupt之間沒有優先級關系，只要有可能，每個interrupt都會被其他interrupt中斷。

我們先考慮單CPU的情況。在這樣情況下，不管在什么執行級別，我們只要簡單地把CPU的中斷關掉就可以達到獨占處理的目的。從這個角度來說，spinlock的實現簡單地令人乍舌：cli/sti。只要這樣，我們就關閉了preemption帶來的復雜之門。

單CPU的情況很簡單，多CPU就不那么簡單了。單純地關掉當前CPU的中斷并不會給我們帶來好運。當我們的代碼存取一個shared variable時，另一顆CPU隨時會把數據改得面目全非。我們需要有手段通知它(或它們，你知道我的意思)——spinlock正為此設。這個例子是 我們的第一次嘗試：

extern spinlock_t lock; // ... spin_lock(&lock); // do something spin_unlock(&lock);

他能正常工作嗎？答案是有可能。在某些情況下，這段代碼可以正常工作，但想一想會不會發生這樣的事：

// in normal run level extern spinlock_t lock; // ... spin_lock(&lock); // do something // interrupted by IRQ ... // in IRQ extern spinlock_t lock; spin_lock(&lock);

喔，我們在normal級別下獲得了一個spinlock，正當我們想做什么的時候，我們被interrupt打斷了，CPU轉而執行interrupt level的代碼，它也想獲得這個lock，于是“死鎖”發生了！解決方法很簡單，看看我們第二次嘗試：

extern spinlock_t lock; // ... cli; // disable interrupt on current CPU spin_lock(&lock); // do something spin_unlock(&lock); sti; // enable interrupt on current CPU

在獲得spinlock之前，我們先把當前CPU的中斷禁止掉，然后獲得一個lock;在釋放lock之后再把中斷打開。這樣，我們就防止了死鎖。事實上，Linux提供了一個更為快捷的方式來實現這個功能:

extern spinlock_t lock; // ... spin_lock_irq(&lock); // do something spin_unlock_irq(&lock);

如果沒有nested interrupt，所有這一切都很好。加上nested interrupt，我們再來看看這個例子:

// code 1 extern spinlock_t lock; // ... spin_lock_irq(&lock); // do something spin_unlock_irq(&lock); // code 2 extern spinlock_t lock; // ... spin_lock_irq(&lock); // do something spin_unlock_irq(&lock);

Code 1和code 2都運行在interrupt context下，由于中斷可以嵌套執行，我們很容易就可以想到這樣的運行次序：

Code 1 extern spinlock_t lock; // ... spin_lock_irq(&lock); Code 2 extern spinlock_t lock; // ... spin_lock_irq(&lock); // do something spin_unlock_irq(&lock); Code 1 // do something spin_unlock_irq(&lock);

問題是在第一個spin_unlock_irq后這個CPU的中斷已經被打開，“死鎖”的問題又會回到我們身邊！

解決方法是我們在每次關閉中斷前紀錄當前中斷的狀態，然后恢復它而不是直接把中斷打開。

unsigned long flags; local_irq_save(flags); spin_lock(&lock); // do something spin_unlock(&lock); local_irq_restore(flags);

Linux同樣提供了更為簡便的方式:

unsigned long flags; spin_lock_irqsave(&lock, flags); // do something spin_unlock_irqrestore(&lock, flags);

小結

優化方法：

1、假設我們的QUERY進程要查詢多個分區（指很多個分區），那么建議把分區的粒度降低，盡量讓QUERY減少真正被訪問的分區數，從而減少LWLockAcquire次數。

2、如果我們的分區很多，但是通過QUERY的WHERE條件過濾后實際被訪問的分區不多，那么分區表的選擇就非常重要。（目前盡量不要使用NATIVE分區）。盡量使用PPAS的edb_enable_pruning。對于PostgreSQL社區版本用戶，在社區優化這部分代碼前，請盡量使用pg_pathman分區功能。

掃描二維碼獲取更多消息：

創作挑戰賽新人創作獎勵來咯，堅持創作打卡瓜分現金大獎

總結

以上是生活随笔為你收集整理的PostgreSQL 查询涉及分区表过多导致的性能问题 - 性能诊断与优化(大量BIND, spin lock, SLEEP进程)的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。