Date: Mon, 9 Dec 2013 16:00:32 -0300
From: Alvaro Herrera <alvherre@2ndquadrant.com>
To: Andres Freund <andres@2ndquadrant.com>
Cc: Serge Negodyuck <petr@petrovich.kiev.ua>, pgsql-bugs@postgresql.org
Subject: Re: BUG #8673: Could not open file
 "pg_multixact/members/xxxx" on slave during hot_standby
Message-ID: <20131209190031.GK6777@eldon.alvh.no-ip.org>
References: <E1Vq1BO-00064N-Qn@wrigleys.postgresql.org>
 <20131209142525.GA1123@awork2.anarazel.de>
 <CABKyZDFWn+ty_m88sq2j5UThowb2Qwd0125BNx_19va2Fn57dQ@mail.gmail.com>
 <20131209150345.GB1123@awork2.anarazel.de>
 <CABKyZDFRtDzcY1=sEznC2ivmcM5p1yPQvC8qLL8XLviNF2kp7A@mail.gmail.com>
 <20131209182701.GD9519@awork2.anarazel.de>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <20131209182701.GD9519@awork2.anarazel.de>
User-Agent: Mutt/1.5.21 (2010-09-15)
Precedence: bulk
Sender: pgsql-bugs-owner@postgresql.org

Andres Freund wrote:
> Hi,
> 
> On 2013-12-09 17:49:34 +0200, Serge Negodyuck wrote:
> > On master there are files from 0000 to 14078
> > 
> > On slave there were absent files from A1xx to FFFF
> > They were  the oldest ones. (October, November)
> 
> Some analysis later, I am pretty sure that the origin is a longstanding
> problem and not connected to 9.3.[01] vs 9.3.2.
> 
> The above referenced 14078 file is exactly the last page before a
> members wraparound:
> (gdb) p/x (1L<<32)/(MULTIXACT_MEMBERS_PER_PAGE * SLRU_PAGES_PER_SEGMENT)
> $10 = 0x14078

As a note, the SlruScanDirectory code has a flaw because it only looks
at four-digit files; the reason only files up to 0xFFFF are missing and
not the following ones is because those got ignored.  This needs a fix
as well.

> So, what happened is that enough multixacts where created, that the
> members slru wrapped around. It's not unreasonable for the members slru
> to wrap around faster then the offsets one - after all we create at
> least two entries into members for every offset entry. Also in 9.3+
> there fit more xids on a offset than a members page.
> When truncating, we first read the offset, to know where we currently
> are in members, and then truncate both from their respective
> point. Since we've wrapped around in members we very well might remove
> content we actually need.

Yeah, on 9.3 each member Xid occupies five bytes in
pg_multixact/members, whereas each offset only occupies four bytes in
pg_multixact/offsets.  It's rare that a multixact only contains one
member; typically they will have at least two (so for each multixact we
would have 4 bytes in offsets and a minimum of 10 bytes in members).
So wrapping around is easy for members, even with the protections we
have in place for offsets.

> I've recently remarked that I find it dangerous that we only do
> anti-wraparound stuff for pg_multixact/offsets, not for /members. So,
> here we have the proof that that's bad.

It's hard to see how to add this post-facto, though.  I mean, I am
thinking we would need some additional pg_control info etc.  We'd better
figure out a way to add such controls without having to add that.

> This is an issue in <9.3 as well. It might, in some sense, even be worse
> there, because we never vacuum old multis away. But on the other hand,
> the growths of multis is slower there and we look into old multis less
> frequently.
> 
> The only reason that you saw the issue on the standby first is that the
> truncation code is called more frequently there. Afaics it will happen,
> sometime in the future, on the master as well.
> 
> I think problems should be preventable if you issue a systemwide VACUUM
> FREEZE, but please let others chime in before you execute it.

I wouldn't freeze anything just yet, at least until the patch to fix
multixact freezing is in.

-- 
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services


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