postgresql - 错误：postgres 中没有可用的未固定缓冲区

Question

执行以下查询时在生产环境中遇到错误

ERROR:  no unpinned buffers available
STATEMENT:  SELECT product_id,  msisdn,EXPIRY_DATE, STATUS,PAY_SRC,PRODUCT_COST,START_DATE ,BEN_MSISDN, SRCCHANNEL,offer_id ,product_purchase_type  FROM RS_ADHOC_PRODUCTS  WHERE   msisdn =$1  AND segment_id =$2 AND PRODUCT_ID = $3 AND status != 4
ERROR:  no unpinned buffers available


ERROR:  no unpinned buffers available at character 13
QUERY:  INSERT INTO adhoc_segment_4 VALUES (NEW.*)

ERROR:  no unpinned buffers available at character 67
STATEMENT:  SELECT TRIGGER_NAME, TRIGGER_GROUP, NEXT_FIRE_TIME, PRIORITY FROM QRTZ_TRIGGERS WHERE SCHED_NAME = 'ClusteredScheduler' AND TRIGGER_STATE = $1 AND NEXT_FIRE_TIME <= $2 AND (MISFIRE_INSTR = -1 OR (MISFIRE_INSTR != -1 AND NEXT_FIRE_TIME >= $3)) ORDER BY NEXT_FIRE_TIME ASC, PRIORITY DESC

有没有人遇到过同样的问题

标签： postgresqlpostgresql-9.2

Answer 1

这是产品内存的解决方案规范

MemTotal:       26312192 Kb (26GB)
MemFree:          869080 kB
MemAvailable:   17128440 kB
Buffers:          143184 kB
Cached:         17165500 kB
SwapCached:        11848 kB
Active:         10612320 kB
Inactive:       13494804 kB
Active(anon):    6793376 kB
Inactive(anon):  1452068 kB
Active(file):    3818944 kB
Inactive(file): 12042736 kB
Unevictable:           0 kB
Mlocked:               0 kB
SwapTotal:       8388604 kB
SwapFree:        8288996 kB
Dirty:              9216 kB
Writeback:             0 kB
AnonPages:       6789452 kB
Mapped:           225808 kB
Shmem:           1446824 kB
Slab:             863492 kB
SReclaimable:     651096 kB
SUnreclaim:       212396 kB
KernelStack:       21248 kB
PageTables:       181896 kB
NFS_Unstable:          0 kB
Bounce:                0 kB
WritebackTmp:          0 kB
CommitLimit:    21544700 kB
Committed_AS:   11161456 kB
VmallocTotal:   34359738367 kB
VmallocUsed:       83540 kB
VmallocChunk:   34359643136 kB
HardwareCorrupted:     0 kB
AnonHugePages:   2160640 kB
HugePages_Total:       0
HugePages_Free:        0
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB
DirectMap4k:      194560 kB
DirectMap2M:     5048320 kB
DirectMap1G:    22020096 kB

根据以上设置 postgres 推荐设置

在我现在的环境中

共享缓冲区值只有 8 MB，如下所示

show shared_buffers;
shared_buffers 
----------------
8MB
(1 row)

因此，将此值更改为推荐值，6GB这是根据 postgres 站点的文档计算得出的。

共享缓冲区

其中共享缓冲区的合理值是系统的 25%。

快乐阅读！！