Thread: pgsql: Make large sequential scans and VACUUMs work in a limited-size
pgsql: Make large sequential scans and VACUUMs work in a limited-size
From
tgl@postgresql.org (Tom Lane)
Date:
Log Message:
-----------
Make large sequential scans and VACUUMs work in a limited-size "ring" of
buffers, rather than blowing out the whole shared-buffer arena. Aside from
avoiding cache spoliation, this fixes the problem that VACUUM formerly tended
to cause a WAL flush for every page it modified, because we had it hacked to
use only a single buffer. Those flushes will now occur only once per
ring-ful. The exact ring size, and the threshold for seqscans to switch into
the ring usage pattern, remain under debate; but the infrastructure seems
done. The key bit of infrastructure is a new optional BufferAccessStrategy
object that can be passed to ReadBuffer operations; this replaces the former
StrategyHintVacuum API.
This patch also changes the buffer usage-count methodology a bit: we now
advance usage_count when first pinning a buffer, rather than when last
unpinning it. To preserve the behavior that a buffer's lifetime starts to
decrease when it's released, the clock sweep code is modified to not decrement
usage_count of pinned buffers.
Work not done in this commit: teach GiST and GIN indexes to use the vacuum
BufferAccessStrategy for vacuum-driven fetches.
Original patch by Simon, reworked by Heikki and again by Tom.
Modified Files:
--------------
pgsql/src/backend/access/hash:
hash.c (r1.94 -> r1.95)
(http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/access/hash/hash.c.diff?r1=1.94&r2=1.95)
hashovfl.c (r1.57 -> r1.58)
(http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/access/hash/hashovfl.c.diff?r1=1.57&r2=1.58)
hashpage.c (r1.67 -> r1.68)
(http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/access/hash/hashpage.c.diff?r1=1.67&r2=1.68)
pgsql/src/backend/access/heap:
heapam.c (r1.233 -> r1.234)
(http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/access/heap/heapam.c.diff?r1=1.233&r2=1.234)
pgsql/src/backend/access/nbtree:
nbtree.c (r1.154 -> r1.155)
(http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/access/nbtree/nbtree.c.diff?r1=1.154&r2=1.155)
pgsql/src/backend/access/transam:
xlog.c (r1.269 -> r1.270)
(http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/access/transam/xlog.c.diff?r1=1.269&r2=1.270)
pgsql/src/backend/catalog:
index.c (r1.283 -> r1.284)
(http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/catalog/index.c.diff?r1=1.283&r2=1.284)
pgsql/src/backend/commands:
analyze.c (r1.107 -> r1.108)
(http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/commands/analyze.c.diff?r1=1.107&r2=1.108)
vacuum.c (r1.351 -> r1.352)
(http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/commands/vacuum.c.diff?r1=1.351&r2=1.352)
vacuumlazy.c (r1.89 -> r1.90)
(http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/commands/vacuumlazy.c.diff?r1=1.89&r2=1.90)
pgsql/src/backend/postmaster:
autovacuum.c (r1.46 -> r1.47)
(http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/postmaster/autovacuum.c.diff?r1=1.46&r2=1.47)
pgsql/src/backend/storage/buffer:
README (r1.11 -> r1.12)
(http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/storage/buffer/README.diff?r1=1.11&r2=1.12)
bufmgr.c (r1.219 -> r1.220)
(http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/storage/buffer/bufmgr.c.diff?r1=1.219&r2=1.220)
freelist.c (r1.58 -> r1.59)
(http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/storage/buffer/freelist.c.diff?r1=1.58&r2=1.59)
localbuf.c (r1.76 -> r1.77)
(http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/storage/buffer/localbuf.c.diff?r1=1.76&r2=1.77)
pgsql/src/backend/tcop:
utility.c (r1.279 -> r1.280)
(http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/tcop/utility.c.diff?r1=1.279&r2=1.280)
pgsql/src/include/access:
genam.h (r1.66 -> r1.67)
(http://developer.postgresql.org/cvsweb.cgi/pgsql/src/include/access/genam.h.diff?r1=1.66&r2=1.67)
hash.h (r1.80 -> r1.81)
(http://developer.postgresql.org/cvsweb.cgi/pgsql/src/include/access/hash.h.diff?r1=1.80&r2=1.81)
relscan.h (r1.53 -> r1.54)
(http://developer.postgresql.org/cvsweb.cgi/pgsql/src/include/access/relscan.h.diff?r1=1.53&r2=1.54)
xlog.h (r1.77 -> r1.78)
(http://developer.postgresql.org/cvsweb.cgi/pgsql/src/include/access/xlog.h.diff?r1=1.77&r2=1.78)
pgsql/src/include/commands:
vacuum.h (r1.71 -> r1.72)
(http://developer.postgresql.org/cvsweb.cgi/pgsql/src/include/commands/vacuum.h.diff?r1=1.71&r2=1.72)
pgsql/src/include/storage:
buf.h (r1.21 -> r1.22)
(http://developer.postgresql.org/cvsweb.cgi/pgsql/src/include/storage/buf.h.diff?r1=1.21&r2=1.22)
buf_internals.h (r1.89 -> r1.90)
(http://developer.postgresql.org/cvsweb.cgi/pgsql/src/include/storage/buf_internals.h.diff?r1=1.89&r2=1.90)
bufmgr.h (r1.103 -> r1.104)
(http://developer.postgresql.org/cvsweb.cgi/pgsql/src/include/storage/bufmgr.h.diff?r1=1.103&r2=1.104)
> Work not done in this commit: teach GiST and GIN indexes to use the vacuum
> BufferAccessStrategy for vacuum-driven fetches.
Attached patch does it. Patch is rather simple - it just replaces ReadBuffer to
ReadBufferWithStrategy in all vacuum-involved places.
If there are no objections, I'll commit the patch.
--
Teodor Sigaev E-mail: teodor@sigaev.ru
WWW: http://www.sigaev.ru/
Attachment
Teodor Sigaev <teodor@sigaev.ru> writes:
>> Work not done in this commit: teach GiST and GIN indexes to use the vacuum
>> BufferAccessStrategy for vacuum-driven fetches.
> Attached patch does it. Patch is rather simple - it just replaces ReadBuffer to
> ReadBufferWithStrategy in all vacuum-involved places.
Thanks --- I figured you could fix that much faster than I could ;-)
regards, tom lane