Message-ID: <54D04BDC.1040206@BlueTreble.com>
Date: Mon, 2 Feb 2015 22:17:32 -0600
From: Jim Nasby <Jim.Nasby@BlueTreble.com>
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.9;
 rv:31.0) Gecko/20100101 Thunderbird/31.4.0
MIME-Version: 1.0
To: Slava Mudry <slava44@gmail.com>
CC: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: why pg_class.relfrozenxid needs to be updated for frozen
 tables (where all rows have xmin=2)?
References: 
 <CAEFxPe3pd5B1ssKvAkUuKULRd+YdYpXTpteZzPtQ88xvrJT5OQ@mail.gmail.com>
	<54CC2FBA.1040506@BlueTreble.com>
	<CAEFxPe3UbWnZdWZO-AA+PyhLvrLsEwL7bJUEsGoDTYW-xBmLjA@mail.gmail.com>
	<54D0260F.40508@BlueTreble.com>	<54D029E0.7000800@BlueTreble.com>
 <CAEFxPe02eW7gzAWmE-C0SGArHAymBFk9iJ9L0__0exPeGcAx-Q@mail.gmail.com>
In-Reply-To: 
 <CAEFxPe02eW7gzAWmE-C0SGArHAymBFk9iJ9L0__0exPeGcAx-Q@mail.gmail.com>
Content-Type: text/plain; charset="utf-8"; format=flowed
Content-Transfer-Encoding: 7bit
X-MS-Exchange-CrossTenant-OriginalArrivalTime: 03 Feb 2015 04:17:38.7570 (UTC)
X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted
X-MS-Exchange-Transport-CrossTenantHeadersStamped: BY1PR11MB0344
X-Pg-Spam-Score: -1.9 (-)
List-Archive: <http://archives.postgresql.org/pgsql-performance>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-performance.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@postgresql.org>
List-Post: <mailto:pgsql-performance@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-performance>
List-Unsubscribe: 
 <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
X-Mailing-List: pgsql-performance
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

On 2/2/15 9:37 PM, Slava Mudry wrote:
>
> On Mon, Feb 2, 2015 at 5:52 PM, Jim Nasby <Jim.Nasby@bluetreble.com
> <mailto:Jim.Nasby@bluetreble.com>> wrote:
>
>     On 2/2/15 7:36 PM, Jim Nasby wrote:
>
>
>             Currently the fact that it needs to go back to old tables
>             and FTS them
>             every 2B transactions (or rely on autovacuum for this) and
>             you can't do
>             anything about it (like permanently freeze the tables) seems
>             like a big
>             scalability issue. Does it not?
>
>
>         Unfortunately it's not terribly easy to fix this. The problem is
>         if we
>         try to play games here, we must have a 100% reliable method for
>         changing
>         relfrozenxid as soon as someone inserts a new tuple in the
>         relation. It
>         might be possible to tie this into the visibility map, but no
>         one has
>         looked at this yet.
>
>         Perhaps you'd be willing to investigate this, or sponsor the work?
>
> I'll see what I can do. Will talk to folks at pgDay in a month.
>
>
>     Oh, there is another possibility that's been discussed: read-only
>     tables. If we had the ability to mark a table read-only, then a
>     VACUUM FREEZE on such a table would be able to set that table's
>     relfrozenxid to FrozenTransactionId and prevent any further attempts
>     at vacuuming. This might be easier than trying to do something
>     automatic.
>
> I think if we could log "last update/delete/insert" timestamp for a
> table - we could use that to freeze tables that are not changed.

A timestamp wouldn't work; you need to have an exact XID.

Even if it did work you still have the same problem: there's a huge, 
hairy race condition between what vacuum is trying to do and any DML.

> I also wonder how pg_database.datfrozenxid is set? Is it equal to the
> oldest pg_class.relfrozenxid for that database?

Correct.

> I ask because I am willing to give a try and update relfrozenxid for the
> tables that are never updated and frozen. Currently we are looking at
> 8-hour downtime to vacuum the whole db in single-user mode. High
> availability is more important that data loss in my case. [I still don't
> want to lose data, but it won't be the end of world if it happens].

Why are you trying to go into single user mode? There's no reason to do 
that.

Forcing relfrozenxid to 2 might work, but you're certainly playing with 
fire.
-- 
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com


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