PostgreSQL 10 GIN索引 鎖優化

postgresql , gin , 反向索引 , 全文檢索 , 性能優化

postgresql gin索引接口常被用于多值列的檢索，例如全文檢索類型、數組類型。

有興趣了解更多索引接口的原理和使用場景，可以參考下文。

<a href="https://github.com/digoal/blog/blob/master/201706/20170627_01.md">《postgresql 9種索引的原理和應用場景》</a>

今天要說道一下postgresql gin索引的代碼優化。

在說gin代碼優化前，我們先來看一個場景，以及在老版本下的性能表現。

建立一張測試表，三個字段，其中一個全文檢索字段，另一個pk，還有一個時間。

全文檢索字段使用随機字元串生成，建立索引。

測試sql

更新crt_time時間字段，但是不更新全文檢索字段。

注意，雖然我們沒有更新全文檢索字段，但是依舊會導緻gin索引的變更，因為token-&gt;ctid，由于pg多版本的原因這裡的ctid會變化，如果ctid變成了其他page的行，那麼索引也需要變化。

即使是更新後的記錄在同一個page（hot更新），vacuumm時将老的記錄删掉也需要變更索引entry。

總之這個為了突出業務上可能忽視的問題。以為不更新索引字段，索引就不需要變化。

ps：pg 10或将來會支援二級索引，就不會存在以上問題。那麼使用者隻需要考慮索引字段value被更新的情況。

1、4并發

2、16并發

3、64并發

我們發現，并發越高，性能抖動非常嚴重，但是資料庫中并未發現waiting。

跟蹤程序pstack，如下，出現了lock和sleep。

pg gin索引有一個fastupdate的選項，實際上是因為一條記錄涉及多個token，為了防止索引頻繁更新，pg設計的一種快速dml方法。就是先将資料寫入pending list，然後由vacuum, analyze或當list滿時觸發将pengding list合并到gin tree的動作。

首先看一下pending list區域的大小由什麼控制。

postgresql 9.4的pending list大小由work_mem參數控制。

<a href="https://www.postgresql.org/docs/9.4/static/gin-implementation.html#gin-fast-update">https://www.postgresql.org/docs/9.4/static/gin-implementation.html#gin-fast-update</a>

src/backend/access/gin/ginfast.c

postgresql 10的gin pending list大小由表級參數，或者全局參數gin_pending_list_limit控制。

<a href="https://www.postgresql.org/docs/10/static/gin-implementation.html">https://www.postgresql.org/docs/10/static/gin-implementation.html</a>

src/include/access/gin_private.h

性能抖動和pending list大小有沒有關系呢？

預設work_mem, gin_pending_list_limit都是4mb。

1、work_mem = 64kb

4并發

64并發

2、work_mem = 128kb

3、work_mem = 32mb

觀察到一個現象：

1、pending list(work_mem)越大，性能抖動越嚴重，tps=0越持久。

2、work_mem越小，性能抖動越少，但是峰值性能會有一定的下降。

3、并發越低，性能越穩定。

4、work_mem較小時，即使并發較高，tps=0的幾率也非常小。

postgresql 9.4的優化建議：

1、work_mem設定為64kb，降低更新并發（例如使用連接配接池控制并發）。

2、将建立了gin索引的字段剝離到獨立的表，通過pk将兩者進行關聯。

例如

postgresql 10送出了一個patch，解決了gin vacuum時需要對整個posting tree的所有頁面長時間持鎖的問題。

<a href="https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=218f51584d5a9fcdf702bcc7f54b5b65e255c187">https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=218f51584d5a9fcdf702bcc7f54b5b65e255c187</a>

1、pending_list_limit = 64kb

2、pending_list_limit = 128kb

3、pending_list_limit = 4mb

4、pending_list_limit = 128mb

postgresql 10 性能非常的平穩，即使是高并發，高pending list的情況下，沒有出現tps=0的情況。

同時在pg 10下，pstack沒有觀測到idx_test_info索引被更新的情況，這也是一個大的改進，可以找一下git.postgresql.org對應哪個patch。

對于需要頻繁更新的表，如果這個表的某些字段建立了gin索引，為了減少gin索引的更新開銷，優化如下。

1、設定表的fillfactor（如=50），盡量使用讓資料庫使用hot更新。減少行遷移，進而減少索引entry的更新。

2、設定較小work_mem，例如設定為64kb。

3、使用連接配接池，控制并發。

4、将tsvector字段拆分出來，使用pk進行關聯。完全杜絕沒必要的更新。

其中1,2是最好實施的，不影響業務，效果立竿見影(但是在巨大壓力、巨大并發下依舊偶爾會有一兩秒的tps=0)。

pg 10在巨大壓力、巨大并發(同時伴随checkpoint, vacuum的虐待)下，tps表現都非常平穩，抖動不超過5%。

1、使用二級索引

<a href="https://github.com/digoal/blog/blob/master/201703/20170312_21.md">《postgresql 10.0 preview 性能增強 - 間接索引(secondary index)》</a>