From: Andres Freund <andres@2ndquadrant.com>
To: Jim Nasby <jim@nasby.net>
Cc: Bruce Momjian <bruce@momjian.us>
Cc: Alvaro Herrera <alvherre@2ndquadrant.com>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Peter Geoghegan <peter@2ndquadrant.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Andrew Dunstan <andrew@dunslane.net>
Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: Re: [PERFORM] encouraging index-only scans
Date: Wed, 18 Sep 2013 01:10:50 +0200
Message-ID: <20130917231050.GD29545@awork2.anarazel.de> (raw)
In-Reply-To: <5238854F.5040708@nasby.net>
References: <20130906052236.GF6067@eldon.alvh.no-ip.org>
<20130906130854.GB600952@alap2.anarazel.de>
<20130906163056.GA13158@momjian.us>
<20130906163647.GA626072@alap2.anarazel.de>
<20130906170159.GB13158@momjian.us>
<20130906191330.GA13193@momjian.us>
<20130906222623.GC626072@alap2.anarazel.de>
<20130907002908.GA12753@momjian.us>
<20130907053449.GE626072@alap2.anarazel.de>
<5238854F.5040708@nasby.net>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
On 2013-09-17 11:37:35 -0500, Jim Nasby wrote:
> On 9/7/13 12:34 AM, Andres Freund wrote:
> >What I was thinking of was to keep track of the oldest xids on pages
> >that cannot be marked all visible. I haven't thought about the
> >statistics part much, but what if we binned the space between
> >[RecentGlobalXmin, ->nextXid) into 10 bins and counted the number of
> >pages falling into each bin. Then after the vacuum finished we could
> >compute how far RecentGlobalXmin would have to progress to make another
> >vacuum worthwile by counting the number of pages from the lowest bin
> >upwards and use the bin's upper limit as the triggering xid.
> >
> >Now, we'd definitely need to amend that scheme by something that handles
> >pages that are newly written to, but it seems something like that
> >wouldn't be too hard to implement and would make autovacuum more useful.
>
> If we're binning by XID though you're still dependent on scanning to
> build that range. Anything that creates dead tuples will also be be
> problematic, because it's going to unset VM bits on you, and you won't
> know if it's due to INSERTS or dead tuples.
I don't think that's all that much of a problem. In the end, it's a good
idea to look at pages shortly after they have been filled/been
touched. Setting hint bits at that point avoid repetitive IO and in many
cases we will already be able to mark them all-visible.
The binning idea was really about sensibly estimating whether a new scan
already makes sense which is currently very hard to judge.
I generally think the current logic for triggering VACUUMs via
autovacuum doesn't really make all that much sense in the days where we
have the visibility map.
> What if we maintained XID stats for ranges of pages in a separate
> fork? Call it the XidStats fork. Presumably the interesting pieces
> would be min(xmin) and max(xmax) for pages that aren't all visible. If
> we did that at a granularity of, say, 1MB worth of pages[1] we're
> talking 8 bytes per MB, or 1 XidStats page per GB of heap. (Worst case
> alignment bumps that up to 2 XidStats pages per GB of heap.)
Yes, I have thought about similar ideas as well, but I came to the
conclusion that it's not worth it. If you want to make the boundaries
precise and the xidstats fork small, you're introducing new contention
points because every DML will need to make sure it's correct.
Also, the amount of code that would require seems to be bigger than
justified by the increase of precision when to vacuum.
Greetings,
Andres Freund
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: andres@2ndquadrant.com, jim@nasby.net, bruce@momjian.us, alvherre@2ndquadrant.com, robertmhaas@gmail.com, peter@2ndquadrant.com, tgl@sss.pgh.pa.us, andrew@dunslane.net, pgsql-hackers@postgresql.org
Subject: Re: [PERFORM] encouraging index-only scans
In-Reply-To: <20130917231050.GD29545@awork2.anarazel.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox