Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-hackers-owner+M255896=pgsql-hackers-arkari@postgresql.org>)
 id 1XMMU4-0001zm-St
 for pgsql-hackers@arkaria.postgresql.org; Tue, 26 Aug 2014 19:32:49 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
 by malur.postgresql.org with smtp (Exim 4.80) (envelope-from
 <pgsql-hackers-owner+M255896=pgsql-hackers-arkari@postgresql.org>)
 id 1XMMU4-0004ia-D8
 for pgsql-hackers@arkaria.postgresql.org; Tue, 26 Aug 2014 19:32:48 +0000
Received: from makus.postgresql.org ([2001:4800:1501:1::229])
 by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256)
 (Exim 4.80) (envelope-from <pgsql@j-davis.com>) id 1XMMU2-0004iR-Mm
 for pgsql-hackers@postgresql.org; Tue, 26 Aug 2014 19:32:46 +0000
Received: from mail-pd0-f171.google.com ([209.85.192.171])
 by makus.postgresql.org with esmtps (TLS1.0:RSA_AES_256_CBC_SHA1:256)
 (Exim 4.80) (envelope-from <pgsql@j-davis.com>) id 1XMMTy-0005kM-U8
 for pgsql-hackers@postgresql.org; Tue, 26 Aug 2014 19:32:44 +0000
Received: by mail-pd0-f171.google.com with SMTP id z10so23082764pdj.30
 for <pgsql-hackers@postgresql.org>; Tue, 26 Aug 2014 12:32:41 -0700 (PDT)
X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
 d=1e100.net; s=20130820;
 h=x-gm-message-state:message-id:subject:from:to:cc:date:in-reply-to
 :references:content-type:mime-version:content-transfer-encoding;
 bh=QUyynSQlEalLCUCxHiFP280yXdbGZhozL7kexkHbKP4=;
 b=h6zaxMsQRv/yXgGpgkCnqgkARNlurutXU1rU3I9U3UFUoFizoQRD9cyJKJwgCw4jy2
 1AupR2XiD5dJqSr7wLF7qfnBwEL09O4vxDS/G4zbZZpVYCVpyVOt5z+Ek8ZY/ujedQj2
 reb+Jup36OgQBoa75dI3laA2jmTGQKDw5wvsNK31/V8nzll5o0Mulv9OQ55Mwu7C2mGh
 U11k5DmGg/7QlSe7DxaqCTj5BsrYEvp62H0oialojJjyRexwFqI4hxszMRSMZSCkeS2I
 65MdbCHpcXpQlV42XXFB8II91zq1U6tGkzmv8dP35Xm/J0PJEBV5myeLP1exZ9G/pxPF
 Fhqw==
X-Gm-Message-State: 
 ALoCoQny2CWIWQrx8XPXWVZdiNrRpOmxAAPf8ktzjKNqxujoqblSKbww8TmTMK5p9q20v1Bcxyoa
X-Received: by 10.70.30.132 with SMTP id s4mr39381348pdh.96.1409081561635;
 Tue, 26 Aug 2014 12:32:41 -0700 (PDT)
Received: from [192.168.0.105] (c-69-181-249-16.hsd1.ca.comcast.net.
 [69.181.249.16])
 by mx.google.com with ESMTPSA id c6sm4038943pbu.59.2014.08.26.12.32.40
 for <multiple recipients>
 (version=SSLv3 cipher=RC4-SHA bits=128/128);
 Tue, 26 Aug 2014 12:32:40 -0700 (PDT)
Message-ID: <1409081570.2335.391.camel@jeff-desktop>
Subject: Re: Proposal for CSN based snapshots
From: Jeff Davis <pgsql@j-davis.com>
To: Greg Stark <stark@mit.edu>
Cc: Heikki Linnakangas <hlinnakangas@vmware.com>, Robert Haas
 <robertmhaas@gmail.com>, Andres Freund <andres@2ndquadrant.com>, Alvaro
 Herrera <alvherre@2ndquadrant.com>, Bruce Momjian <bruce@momjian.us>,
 Rajeev rastogi <rajeev.rastogi@huawei.com>, Ants Aasma <ants@cybertec.at>,
 PostgreSQL-development <pgsql-hackers@postgresql.org>, Markus Wanner
 <markus@bluegap.ch>
Date: Tue, 26 Aug 2014 12:32:50 -0700
In-Reply-To: 
 <CAM-w4HMJReV6DLZC6v+-TR3_S0eBkxJtiuMrTkb92sdOssJezA@mail.gmail.com>
References: <51B6C97B.1070000@bluegap.ch>
 <BF2827DCCE55594C8D7A8F7FFD3AB7713DDBC758@SZXEML508-MBX.china.huawei.com>
 <5370D323.1070606@vmware.com> <20140512144112.GB9619@awork2.anarazel.de>
 <5370E267.2020904@vmware.com> <20140515183436.GD25053@momjian.us>
 <CA+TgmoYiqga4KWz_1=O8D6ivmTAJfY=-fxO6Nk+D-o3M0M60=Q@mail.gmail.com>
 <20140515200632.GB16014@awork2.anarazel.de>
 <20140515213714.GB7857@eldon.alvh.no-ip.org> <53889CCB.7030005@vmware.com>
 <20140530152721.GC30516@awork2.anarazel.de> <5388D02D.6040305@vmware.com>
 <CA+TgmoZ4cP_1Pavwn+VX74_ksDhHf-whO5FFR1j+NdZqONu8MA@mail.gmail.com>
 <CAM-w4HMG-YeTfWc3r_excej3Kd04q4zDDXR0hxShVbij211ABA@mail.gmail.com>
 <539AD153.9000004@vmware.com> <1409043789.2335.330.camel@jeff-desktop>
 <53FC6545.7000501@vmware.com>
 <CAM-w4HMJReV6DLZC6v+-TR3_S0eBkxJtiuMrTkb92sdOssJezA@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
X-Mailer: Evolution 3.10.4-0ubuntu1 
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit
X-Pg-Spam-Score: -2.6 (--)
List-Archive: <http://archives.postgresql.org/pgsql-hackers>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-hackers.postgresql.org>
List-Owner: <mailto:pgsql-hackers-owner@postgresql.org>
List-Post: <mailto:pgsql-hackers@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-hackers>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
X-Mailing-List: pgsql-hackers
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

On Tue, 2014-08-26 at 19:25 +0100, Greg Stark wrote:
> I don't immediately see how to make that practical. One thought would
> be to have a list of xids in the page header with their corresponding
> csn -- which starts to sound a lot like Oralce's "Interested
> Transaction List". But I don't see how to make that work for the
> hundreds of possible xids on the page.

I feel like that's moving in the wrong direction. That's still causing a
lot of modifications to a data page when the data is not changing, and
that's bad for a lot of cases that I'm interested in (checksums are one
example).

We are mixing two kinds of data: user data and visibility information.
Each is changed under different circumstances and has different
characteristics, and I'm beginning to think they shouldn't be mixed at
all.

What if we just devised a structure specially designed to hold
visibility information, put all of the visibility information there, and
data pages would only change where there is a real, user-initiated
I/U/D. Vacuum could still clear out dead tuples from the data area, but
it would do the rest of its work on the visibility structure. It could
even be a clever structure that could compress away large static areas
until they become active again.

Maybe this wouldn't work for all tables, but could be an option for big
tables with low update rates.

> The worst case for visibility resolution is you have a narrow table
> that has random access DDL happening all the time, each update is a
> short transaction and there are a very high rate of such transactions
> spread out uniformly over a very large table. That means any given
> page has over 200 rows with random xids spread over a very large range
> of xids.

That's not necessarily a bad case, unless the CLOG/CSNLOG lookup is a
significant fraction of the effort to update a tuple.

That would be a bad case if you introduce scans into the equation as
well, but that's not a problem if the all-visible bit is set.

> Currently the invariant hint bits give us is that each xid needs to be
> looked up in the clog only a more or less fixed number of times, in
> that scenario only once since the table is very large and the
> transactions short lived.

A backend-local cache might accomplish that, as well (would still need
to do a lookup, but no locks or contention). There would be some
challenges around invalidation (for xid wraparound) and pre-warming the
cache (so establishing a lot of connections doesn't cause a lot of CLOG
access).

Regards,
	Jeff Davis




-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers