From: daveg <daveg@sonic.net>
To: Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>
Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: Re: Re: PD_ALL_VISIBLE flag was incorrectly set happend during repeatable vacuum
Date: Mon, 7 Mar 2011 15:53:40 -0800
Message-ID: <20110307235340.GA21941@sonic.net> (raw)
In-Reply-To: <4D710ABD.5090103@enterprisedb.com>
References: <AANLkTikw==USr3+4Mt+w97P3ymux-Bhn0G7Ad0ZJHbqG@mail.gmail.com>
<4D2D6A56.9060808@enterprisedb.com>
<20110228212832.GH26397@sonic.net>
<1B3CCBDD-4AC6-404A-9E26-DB4CA2554228@endpoint.com>
<AANLkTi=xq4SV6jwA7txvrbEWocyeiU6XiTG=-zVy+7Dq@mail.gmail.com>
<AANLkTi=Ye7AoF7_uVQfPVn-ok2VOp65mi0xU3H7kCke3@mail.gmail.com>
<20110303071208.GA4053@sonic.net>
<4D6F4E5D.6080507@enterprisedb.com>
<20110304090031.GE4053@sonic.net>
<4D710ABD.5090103@enterprisedb.com>
On Fri, Mar 04, 2011 at 05:52:29PM +0200, Heikki Linnakangas wrote:
> Hmm, if these all came from the same database, then it looks OldestXmin
> has moved backwards. That would explain the warnings. First one vacuum
> determines that all the tuples are visible to everyone and sets the
> flag. Then another vacuum runs with an older OldestXmin, and determines
> that there's a tuple on the page with an xmin that is not yet visible to
> everyone, hence it thinks that the flag should not have been set yet.
>
> Looking at the code, I don't see how that situation could arise, though.
> The value calculated by GetOldestXmin() should never move backwards. And
> GetOldestXmin() is called in lazy_vacuum_rel(), after it has acquired a
> lock on the table, which should protect from a race condition where two
> vacuums could run on the table one after another, in a way where the
> later vacuum runs with an OldestXmin calculated before the first vacuum.
>
> Hmm, fiddling with vacuum_defer_cleanup_age on the fly could cause that,
> though. You don't do that, do you?
No.
I've updated the patch to collect db and schema and added Merlins patch as
well and run it for a while. The attached log is all the debug messages
for pg_statistic page 333 from one database. I've also attached the two
most recent page images for that particular page, the last digits in the
filename are the hour and minute of when the page was saved.
What else can I be doing to help figure this out?
Thanks
-dg
--
David Gould daveg@sonic.net 510 536 1443 510 282 0869
If simplicity worked, the world would be overrun with insects.
Attachments:pd_all.c57.pg_catalog.pg_statistic.333.log (text/plain, 2.8K) download
2011-03-05 00:50:03.238 PST 13304 WARNING: debugging: setting PD_ALL_VISIBLE in relation "c57.pg_catalog.pg_statistic" on page 333 (OldestXmin 337186202)
2011-03-05 21:55:00.004 PST 10702 WARNING: debugging: setting PD_ALL_VISIBLE in relation "c57.pg_catalog.pg_statistic" on page 333 (OldestXmin 341342282)
2011-03-05 23:28:06.368 PST 16660 WARNING: debugging: setting PD_ALL_VISIBLE in relation "c57.pg_catalog.pg_statistic" on page 333 (OldestXmin 342000646)
2011-03-06 05:24:03.461 PST 12850 WARNING: debugging: setting PD_ALL_VISIBLE in relation "c57.pg_catalog.pg_statistic" on page 333 (OldestXmin 343420345)
2011-03-06 06:23:40.174 PST 21650 WARNING: debugging: setting PD_ALL_VISIBLE in relation "c57.pg_catalog.pg_statistic" on page 333 (OldestXmin 343773970)
2011-03-06 07:50:49.921 PST 3847 WARNING: debugging: setting PD_ALL_VISIBLE in relation "c57.pg_catalog.pg_statistic" on page 333 (OldestXmin 344373260)
2011-03-06 09:11:35.662 PST 10820 WARNING: debugging: setting PD_ALL_VISIBLE in relation "c57.pg_catalog.pg_statistic" on page 333 (OldestXmin 344660206)
2011-03-07 01:23:57.124 PST 14121 WARNING: debugging: setting PD_ALL_VISIBLE in relation "c57.pg_catalog.pg_statistic" on page 333 (OldestXmin 347873961)
2011-03-07 01:25:01.129 PST 14266 WARNING: PD_ALL_VISIBLE flag was incorrectly set in relation "c57.pg_catalog.pg_statistic" page 333 via OldestXmin (OldestXmin 347186993)
2011-03-07 01:26:01.143 PST 14356 WARNING: debugging: setting PD_ALL_VISIBLE in relation "c57.pg_catalog.pg_statistic" on page 333 (OldestXmin 347879220)
2011-03-07 01:27:01.158 PST 14456 WARNING: PD_ALL_VISIBLE flag was incorrectly set in relation "c57.pg_catalog.pg_statistic" page 333 via OldestXmin (OldestXmin 347186993)
2011-03-07 01:33:05.353 PST 15108 WARNING: debugging: setting PD_ALL_VISIBLE in relation "c57.pg_catalog.pg_statistic" on page 333 (OldestXmin 347705144)
2011-03-07 01:38:09.298 PST 15869 WARNING: debugging: setting PD_ALL_VISIBLE in relation "c57.pg_catalog.pg_statistic" on page 333 (OldestXmin 347705144)
2011-03-07 01:51:17.622 PST 18829 WARNING: debugging: setting PD_ALL_VISIBLE in relation "c57.pg_catalog.pg_statistic" on page 333 (OldestXmin 348042728)
2011-03-07 02:24:41.994 PST 22594 WARNING: debugging: setting PD_ALL_VISIBLE in relation "c57.pg_catalog.pg_statistic" on page 333 (OldestXmin 348116608)
2011-03-07 06:33:47.460 PST 18384 WARNING: debugging: setting PD_ALL_VISIBLE in relation "c57.pg_catalog.pg_statistic" on page 333 (OldestXmin 349004767)
2011-03-07 07:01:51.400 PST 23896 WARNING: PD_ALL_VISIBLE flag was incorrectly set in relation "c57.pg_catalog.pg_statistic" page 333 via OldestXmin (OldestXmin 347705144)
2011-03-07 10:23:31.416 PST 10654 WARNING: debugging: setting PD_ALL_VISIBLE in relation "c57.pg_catalog.pg_statistic" on page 333 (OldestXmin 349660381)
pageimage_c57_pg_catalog_pg_statistic_333.0127 (application/octet-stream, 8.0K) download
pageimage_c57_pg_catalog_pg_statistic_333.0701 (application/octet-stream, 8.0K) download
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-admin@postgresql.org
Cc: daveg@sonic.net, heikki.linnakangas@enterprisedb.com, pgsql-hackers@postgresql.org
Subject: Re: Re: PD_ALL_VISIBLE flag was incorrectly set happend during repeatable vacuum
In-Reply-To: <20110307235340.GA21941@sonic.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