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)

Re: pgsql: Make large sequential scans and VACUUMs work in a limited-size

From
Teodor Sigaev
Date:
> 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