綜合指南：postgresql shared buffers

本文主要針對(duì)下面問(wèn)題詳述PG的共享內(nèi)存：PG中需要給共享內(nèi)存分配多少內(nèi)存？為什么？

非常奇怪，為什么我的RDS PG需要使用系統(tǒng)RAM的25％，而Aurora的PG卻需要分配75％？

理解PG中的共享內(nèi)存及操作系統(tǒng)的緩存

首先提出個(gè)問(wèn)題：PG中的bgwriter進(jìn)程是干什么的？

如果回答是將臟頁(yè)刷到磁盤(pán)的，那這就錯(cuò)了。他僅僅將臟頁(yè)刷寫(xiě)到操作系統(tǒng)的緩存，然后由操作系統(tǒng)調(diào)用sync將操作系統(tǒng)緩存刷寫(xiě)到磁盤(pán)。有點(diǎn)迷惑？那么接著我們說(shuō)道說(shuō)道。

由于PG輕量的特性，他高度依賴(lài)操作系統(tǒng)緩存，通過(guò)操作系統(tǒng)感知文件系統(tǒng)、磁盤(pán)布局以及讀寫(xiě)數(shù)據(jù)文件。下圖幫助了解數(shù)據(jù)如何在磁盤(pán)和共享緩存之間流動(dòng)。

因此當(dāng)發(fā)起“select ＊from emp”時(shí)，數(shù)據(jù)會(huì)加載到操作系統(tǒng)緩存然后才到shared buffer。同樣當(dāng)將臟頁(yè)向磁盤(pán)刷寫(xiě)時(shí)，也是先到操作系統(tǒng)緩存，然后由操作系統(tǒng)調(diào)用fsync（）將操作系統(tǒng)緩存中數(shù)據(jù)持久化到磁盤(pán)。這樣PG實(shí)際上由兩份數(shù)據(jù)，看起來(lái)有些浪費(fèi)空間，但是操作系統(tǒng)緩存是一個(gè)簡(jiǎn)單的LRU而不是數(shù)據(jù)庫(kù)優(yōu)化的clock sweep algorithm。一旦在shared＿buffers中命中，那么讀就不會(huì)下沉到操作系統(tǒng)緩存。如果shared buffer和操作系統(tǒng)緩存有相同頁(yè)，操作系統(tǒng)緩存中的頁(yè)很快會(huì)被驅(qū)逐替換。

我能影響操作系統(tǒng)的fsync將臟頁(yè)刷回磁盤(pán)嗎？

當(dāng)然，通過(guò)postgresql．conf中參數(shù)bgwriter＿flush＿after，該參數(shù)整型，默認(rèn)512KB。當(dāng)后臺(tái)寫(xiě)進(jìn)程寫(xiě)了這么多數(shù)據(jù)時(shí)，會(huì)強(qiáng)制OS發(fā)起sync將cache中數(shù)據(jù)刷到底層存儲(chǔ)。這樣會(huì)限制內(nèi)核頁(yè)緩存中的臟數(shù)據(jù)數(shù)量，從而減小checkpoint時(shí)間或者后臺(tái)大批量寫(xiě)回?cái)?shù)據(jù)的時(shí)間。

不僅僅時(shí)bgwriter，即使checkpoint進(jìn)程和用戶(hù)進(jìn)程也從shared buffer刷寫(xiě)臟頁(yè)到OS cache。可以通過(guò)checkpoint＿flush＿after影響checkpoint進(jìn)程的fsync，通過(guò)backend＿flush＿after影響后臺(tái)進(jìn)程的fsync。

如果給OS cache很小值會(huì)怎么樣？

正如上文所述，一旦頁(yè)被標(biāo)記為臟，他就會(huì)刷寫(xiě)到操作系統(tǒng)緩存。操作系統(tǒng)可以更加自由地根據(jù)傳入的流量進(jìn)行IO調(diào)度。如果OS cache太小，則無(wú)法重新對(duì)write進(jìn)行排序從而優(yōu)化IO。這對(duì)于寫(xiě)操作頻繁的工作負(fù)載尤為重要，所以操作系統(tǒng)緩存大學(xué)也很重要。

如果給shared buffer很小值會(huì)怎么樣？

數(shù)據(jù)庫(kù)操作都在shared buffer，所以最好為shared buffer分配足夠空間。

建議值多大？

PG推薦系統(tǒng)內(nèi)存的25％給shared buffer，當(dāng)然可以根據(jù)環(huán)境進(jìn)行調(diào)整。

如果查看shared buffer中內(nèi)容？

PG的buffer cache擴(kuò)展可以幫助實(shí)時(shí)查看shared buffer中內(nèi)容。從shared＿buffers中采集信息保存到pg＿buffercache表中：

create extension pg＿buffercache；

安裝好后，執(zhí)行下面查詢(xún)查看內(nèi)容：

SELECT c．relname

， pg＿size＿pretty（count（＊） ＊ 8192） as buffered

， round（100．0 ＊ count（＊） ／ （ SELECT setting FROM pg＿settings WHERE name＝＇shared＿buffers＇）：：integer，1） AS buffers＿percent

， round（100．0 ＊ count（＊） ＊ 8192 ／ pg＿relation＿size（c．oid），1） AS percent＿of＿relation

FROM pg＿class c

INNER JOIN pg＿buffercache b ON b．relfilenode ＝ c．relfilenode

INNER JOIN pg＿database d ON （b．reldatabase ＝ d．oid AND d．datname ＝ current＿database（））

WHERE pg＿relation＿size（c．oid） ＞ 0

GROUP BY c．oid， c．relname

ORDER BY 3 DESC

LIMIT 10；

輸出：

postgres＝＃ SELECT c．relname  postgres－＃   ，  pg＿size＿pretty（count（＊） ＊ 8192） as buffered  postgres－＃   ， round（100．0 ＊  count（＊） ／ （ SELECT setting FROM pg＿settings WHERE  name＝＇shared＿buffers＇）：：integer，1） AS buffers＿percent  postgres－＃   ， round（100．0 ＊  count（＊） ＊ 8192 ／ pg＿relation＿size（c．oid），1） AS percent＿of＿relation  postgres－＃  FROM pg＿class c  postgres－＃  INNER JOIN  pg＿buffercache b ON b．relfilenode ＝ c．relfilenode  postgres－＃  INNER JOIN  pg＿database d ON （b．reldatabase ＝ d．oid AND d．datname ＝ current＿database（））  postgres－＃  WHERE  pg＿relation＿size（c．oid） ＞ 0  postgres－＃  GROUP BY c．oid，  c．relname  postgres－＃  ORDER BY 3 DESC  postgres－＃  LIMIT 10；            relname          ｜  buffered  ｜  buffers＿percent ｜ percent＿of＿relation  －－－－－－－－－－－－－－－－－－－－－－－－－－－＋－－－－－－－－－－－－＋－－－－－－－－－－－－－－－－－＋－－－－－－－－－－－－－－－－－－－－－   pg＿operator                ｜ 80 kB      ｜              0．1 ｜                71．4   pg＿depend＿reference＿index  ｜ 96 kB      ｜              0．1 ｜                27．9   pg＿am                      ｜ 8192 bytes ｜              0．0 ｜                100．0   pg＿amproc                  ｜ 24 kB      ｜              0．0 ｜                100．0   pg＿cast                    ｜ 8192 bytes ｜              0．0 ｜                50．0   pg＿depend                  ｜ 64 kB      ｜              0．0 ｜                14．0   pg＿index                  ｜  32 kB      ｜              0．0 ｜                100．0   pg＿description            ｜  40 kB      ｜              0．0 ｜                14．3   pg＿language                ｜ 8192 bytes ｜              0．0 ｜                100．0   pg＿amop                    ｜ 40 kB      ｜              0．0 ｜                83．3  （10 rows）
如何感知數(shù)據(jù)到達(dá)操作系統(tǒng)緩存層？

需要安裝包pgfincore：

As root user：      export PATH＝／usr／local／pgsql／bin：＄PATH ／／Set  the path to point pg＿config．  tar －xvf pgfincore－v1．1．1．tar．gz   cd pgfincore－1．1．1   make clean   make   make install      Now connect to PG and run below command  postgres＝＃ CREATE EXTENSION pgfincore；

執(zhí)行下面命令：

select c．relname，pg＿size＿pretty（count（＊） ＊ 8192） as pg＿buffered，
 round（100．0 ＊ count（＊） ／
（select setting
from pg＿settings
where name＝＇shared＿buffers＇）：：integer，1）
as pgbuffer＿percent，
round（100．0＊count（＊）＊8192 ／ pg＿table＿size（c．oid），1） as percent＿of＿relation，
（ select round（ sum（pages＿mem） ＊ 4 ／1024，0 ）
from pgfincore（c．relname：：text） ）
as os＿cache＿M(jìn)B ，
round（100 ＊ （
select sum（pages＿mem）＊4096
from pgfincore（c．relname：：text） ）／ pg＿table＿size（c．oid），1）
as os＿cache＿percent＿of＿relation，
pg＿size＿pretty（pg＿table＿size（c．oid）） as rel＿size
from pg＿class c
inner join pg＿buffercache b on b．relfilenode＝c．relfilenode
inner join pg＿database d on （b．reldatabase＝d．oid and d．datname＝current＿database（）
and c．relnamespace＝（select oid from pg＿namespace where nspname＝＇public＇））
group by c．oid，c．relname
order by 3 desc limit 30；
輸出：
relname ｜pg＿buffered｜pgbuffer＿per｜per＿of＿relation｜os＿cache＿mb｜os＿cache＿per＿of＿relation｜rel＿size
－－－－－－－－－＋－－－－－－－－－－－＋－－－－－－－－－－－－＋－－－－－－－－－－－－－－－＋－－－－－－－－－－－＋－－－－－－－－－－－－－－－－－－－－－－－－＋－－－－－－－－
emp ｜ 4091 MB ｜ 99．9 ｜ 49．3 ｜ 7643 ｜ 92．1 ｜ 8301 MB

pg＿buffered表示PG buffer cache中有多少數(shù)據(jù)，pgbuffer＿percent表示pg＿buffered／total＿buffer＿size＊100。os＿cache＿mb表示OS cache中緩存多少。我們的表emp有8301MB數(shù)據(jù)，92％數(shù)據(jù)在OS cache，49．3％在shared buffers，大約50％的數(shù)據(jù)是冗余的。

為什么Aurora PG推薦75％的內(nèi)存給shared buffer？

Aurora不使用文件系統(tǒng)緩存，因此可以提升shared＿buffers大小以提升性能。最佳實(shí)踐值為75％。Work＿mem、maintenance＿work＿mem和其他本地內(nèi)存不是shared buffer的一部分。如果應(yīng)用請(qǐng)求大量客戶(hù)端連接，或需要大量work＿mem時(shí)，需要將這個(gè)值調(diào)小。