京公网安备11010502063487号“PostgreSQL 的 shared_buffers 该设多大、怎么看缓存命中率、重启后为什么变慢” 都可以用 pg_buffercache 和 EXPLAIN (BUFFERS) 直接看到。这篇在本机自己起一个 PostgreSQL 16.15,用 120.5 MB 的表验证 read 和 hit 的区别、全表扫描的环形缓冲、命中率的算法,以及 pg_prewarm。
第 1 次:25.21 ms;Buffers: shared hit=3 read=3623;I/O Timings: shared read=6.482 第 2 次:17.98 ms;Buffers: shared hit=3626 表和索引占了多少个缓冲区:t 3078 个,t_pkey 550 个
Buffers 里 read 是向系统要页,hit 是命中共享缓冲区;重启后第一次全是 read。
全表扫描:78.15 ms;Buffers: shared read=15424 扫完之后 t 在 shared_buffers 里有 33 个缓冲区(整张表有 15424 个页) 再扫一次:67.00 ms;Buffers: shared hit=33 read=15391(仍然大部分是 read,因为上一次的页没有留下来)
大表的顺序扫描用环形缓冲,只占几十个缓冲区,不会冲掉热数据。
2 万次随机点查:0.99 s,平均 50 μs;缓冲区里 t 有 11149 个,t_pkey 有 2741 个 heap_blks_read = 11149,heap_blks_hit = 8851,idx_blks_read = 2741,idx_blks_hit = 57260;堆页命中率 44.3%,索引页命中率 95.4% 同样的 2 万次再来一遍:0.85 s,平均 43 μs
pg_statio_user_tables 的 heap_blks_hit / (read + hit);索引页命中率通常远高于堆页。
shared_buffers 第 1 遍耗时 read hit 第 2 遍耗时 read hit
16MB 0.87s 32059 48264 0.97s 31857 48464
64MB 0.91s 17221 63102 0.90s 13502 66819
256MB 0.90s 13964 66359 0.81s 0 80321工作集装不下时第二遍仍然大量 read;在系统缓存很大的机器上耗时差别不大。
不 prewarm,第一次:29.95 ms;Buffers: shared hit=3 read=3623 pg_prewarm(t_pkey) 读了 2745 个页,pg_prewarm(t) 读了 15424 个页,共 0.07 s;缓冲区里 t 有 15424 个,t_pkey 有 2745 个 prewarm 之后第一次:21.46 ms;Buffers: shared hit=3626
重启或切换后用 pg_prewarm 提前把热表和索引读进缓冲区。