From: Jim Nasby <jim@nasby.net>
To: Andres Freund <andres@2ndquadrant.com>
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 15:28:53 -0500
Message-ID: <523A0D05.70504@nasby.net> (raw)
In-Reply-To: <20130917231050.GD29545@awork2.anarazel.de>
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>
<20130917231050.GD29545@awork2.anarazel.de>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
On 9/17/13 6:10 PM, Andres Freund wrote:
>> 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.
Actually, that's not true... the XidStats only need to be "relatively" precise. IE: within a few hundred or thousand XIDs.
So for example, you'd only need to attempt an update if the XID already stored was more than a few hundred/thousand/whatever XIDs away from your XID. If it's any closer don't even bother to update.
That still leaves potential for thundering herd on the fork buffer lock if you've got a ton of DML on one table across a bunch of backends, but there might be other ways around that. For example, if you know you can update the XID with a CPU-atomic instruction, you don't need to lock the page.
> Also, the amount of code that would require seems to be bigger than
> justified by the increase of precision when to vacuum.
That's very possibly true. I haven't had a chance to see how much VM bits help reduce vacuum overhead yet, so I don't have anything to add on this front. Perhaps others might.
--
Jim C. Nasby, Data Architect jim@nasby.net
512.569.9461 (cell) http://jim.nasby.net
--
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: jim@nasby.net, andres@2ndquadrant.com, 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: <523A0D05.70504@nasby.net>
* 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