Skip site navigation (1) Skip section navigation (2)

Re: Improvement of procArray.xmin for VACUUM

From: Bruce Momjian <bruce(at)momjian(dot)us>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Heikki Linnakangas <heikki(at)enterprisedb(dot)com>, PostgreSQL-patches <pgsql-patches(at)postgresql(dot)org>
Subject: Re: Improvement of procArray.xmin for VACUUM
Date: 2007-03-24 19:42:35
Message-ID: 200703241942.l2OJgZC18482@momjian.us (view raw or flat)
Thread:
Lists: pgsql-patches
Tom Lane wrote:
> Heikki Linnakangas <heikki(at)enterprisedb(dot)com> writes:
> > It seems to me a lot cleaner to do the reference counting like Tom 
> > suggested. Increase the refcount on CopySnapshot, and decrease it on 
> > FreeSnapshot. Assuming that all callers of CopySnapshot free the 
> > snapshot with FreeSnapshot when they're done with it.
> 
> I don't believe we bother at the moment; which is one of the reasons
> it'd be a nontrivial patch.  I do think it might be worth doing though.
> In the simple case where you're just issuing successive non-cursor
> commands within a READ COMMITTED transaction, a refcounted
> implementation would be able to recognize that there are *no* live
> snapshots between commands and therefore reset MyProc->xmin to 0
> whenever the backend is idle.

Attached is my current version of the patch.  It doesn't work now that I
tried to do reference count for Snapshots, but will stop now that Tom is
considering redesigning the snapshot mechanism.

> OTOH, do we have any evidence that this is worth bothering with at all?
> I fear that the cases of long-running transactions that are problems
> in the real world wouldn't be helped much --- for instance, pg_dump
> wouldn't change behavior because it uses a serializable transaction.
> Also, at some point a long-running transaction becomes a bottleneck
> simply because its XID is itself the oldest thing visible in the
> ProcArray and is determining everyone's xmin.  How much daylight is
> there really between "your xmin is old" and "your xid is old"?

Well, interesting you mention that, because I have a second idea on how
to improve things.  We start with MyProc->xmin equal to our own xid, and
then look for earlier transactions.  It should be possible to skip
considering our own xid for MyProc->xmin.  This would obviously help
VACUUM during long-running transactions.  While our transaction is
running, our xid isn't committed, so VACUUM isn't going to touch any of
our rows, and if other transactions complete before our
multi-transaction _statement_ starts, we can't see deleted rows from
them transaction, so why keep the deleted rows around?  Consider this
case:

Session #:
        1                               2               3
                                        BEGIN;
                                        SELECT 1;
        CREATE TABLE test(x int);
        INSERT INTO test VALUES (1);
        DELETE FROM test;
                                        SELECT 1;
                                                        VACUUM VERBOSE test;
                                                        (row can be reused)
                                        COMMIT;
                                                        VACUUM VERBOSE test;
                                                        (normal row reuse)

As I understand it, in READ COMMITTED mode, we have to skip
transactions in progress when our _statement_ starts, but anything
committed before that we see and we don't see dead rows created by them.

-- 
  Bruce Momjian  <bruce(at)momjian(dot)us>          http://momjian.us
  EnterpriseDB                               http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

Attachment: /pgpatches/xid
Description: text/x-diff (18.1 KB)

In response to

pgsql-patches by date

Next:From: Bruce MomjianDate: 2007-03-24 21:50:03
Subject: Re: [BUGS] BUG #3095: LDAP authentication parsing incorrectly
Previous:From: Gregory StarkDate: 2007-03-24 16:14:53
Subject: Re: Improvement of procArray.xmin for VACUUM

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group