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

Lists: pgsql-committerspgsql-hackers
From: tgl(at)postgresql(dot)org (Tom Lane)
To: pgsql-committers(at)postgresql(dot)org
Subject: pgsql: Make large sequential scans and VACUUMs work in a limited-size
Date: 2007-05-30 20:12:03
Message-ID: 20070530201203.E66D09FC224@postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-committers pgsql-hackers

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)


From: Alvaro Herrera <alvherre(at)commandprompt(dot)com>
To: Hackers <pgsql-hackers(at)postgresql(dot)org>
Cc: Heikki Linnakangas <hlinnaka(at)iki(dot)fi>
Subject: Re: [COMMITTERS] pgsql: Make large sequential scans and VACUUMs work in a limited-size
Date: 2007-05-30 21:09:55
Message-ID: 20070530210955.GM7128@alvh.no-ip.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-committers pgsql-hackers

Tom Lane wrote:
> 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.

I think now is time to re-test the patch for advancing OldestXmin during
vacuum?

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.


From: Heikki Linnakangas <heikki(at)enterprisedb(dot)com>
To: Alvaro Herrera <alvherre(at)commandprompt(dot)com>
Cc: Hackers <pgsql-hackers(at)postgresql(dot)org>, Heikki Linnakangas <hlinnaka(at)iki(dot)fi>
Subject: Re: [COMMITTERS] pgsql: Make large sequential scans and VACUUMs work in a limited-size
Date: 2007-05-31 09:11:30
Message-ID: 465E9142.1050101@enterprisedb.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-committers pgsql-hackers

Alvaro Herrera wrote:
> Tom Lane wrote:
>> 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.
>
> I think now is time to re-test the patch for advancing OldestXmin during
> vacuum?

Thanks for the reminder, I'll schedule those tests.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com


From: Teodor Sigaev <teodor(at)sigaev(dot)ru>
To: Tom Lane <tgl(at)postgresql(dot)org>
Cc: pgsql-committers(at)postgresql(dot)org
Subject: Re: pgsql: Make large sequential scans and VACUUMs work in a limited-size
Date: 2007-05-31 13:42:04
Message-ID: 465ED0AC.6090300@sigaev.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-committers pgsql-hackers

> 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(at)sigaev(dot)ru
WWW: http://www.sigaev.ru/

Attachment Content-Type Size
gingist_strategy.patch.gz application/x-tar 1.5 KB

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Teodor Sigaev <teodor(at)sigaev(dot)ru>
Cc: pgsql-committers(at)postgresql(dot)org
Subject: Re: pgsql: Make large sequential scans and VACUUMs work in a limited-size
Date: 2007-05-31 13:47:38
Message-ID: 28887.1180619258@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-committers pgsql-hackers

Teodor Sigaev <teodor(at)sigaev(dot)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