References: 
 <CA+OQrzhdtpC-9enDpY_PTue3_eh9xxmP-z51fJHFqezvhJ-9Nw@mail.gmail.com>
 <CAJghg4+y5NcvjfJO+Ynad-5NcuHHOUOgFpUEP=fLA3WTgGAoCw@mail.gmail.com>
 <CAHyXU0w+haLQ2UdU_XjyiEPnC_Xp24QM4QjNYXFXQhtkvdSsgA@mail.gmail.com>
 <CA+OQrzhBziFavzH==iqrJegR5Us6q7EvbopkQAWJV9f-UYVthQ@mail.gmail.com>
 <20140819171710.GA15735@awork2.anarazel.de>
 <CA+OQrzjSJTaUMs5y5Ar=_aipSa115d7qNSZy==w8MiDEdG27Uw@mail.gmail.com>
 <CA+OQrzgZPudu4Rdo6+qFPS2Y-bfXmHCfVuJtfFE-=0NXmZb9mw@mail.gmail.com>
 <1408471302.64928.YahooMailNeo@web122302.mail.ne1.yahoo.com>
 <CA+OQrzi1S7k6tU8fept8D4=iD7LgOnv3kRAmp2RnmrDrtNJpJQ@mail.gmail.com>
 <1408473810.64930.YahooMailNeo@web122304.mail.ne1.yahoo.com>
 <CA+OQrzg0OwnU4TyMnwi577M7aCrNjBb0ObJoyZW+ndUWjmOEgg@mail.gmail.com>
 <1408558502.11006.YahooMailNeo@web122302.mail.ne1.yahoo.com>
Message-ID: <1408569862.61116.YahooMailNeo@web122303.mail.ne1.yahoo.com>
Date: Wed, 20 Aug 2014 14:24:22 -0700
From: Kevin Grittner <kgrittn@ymail.com>
Reply-To: Kevin Grittner <kgrittn@ymail.com>
Subject: Re: query against pg_locks leads to large memory alloc
To: Kevin Grittner <kgrittn@ymail.com>, Dave Owens <dave@teamunify.com>
Cc: Andres Freund <andres@2ndquadrant.com>,
 Merlin Moncure <mmoncure@gmail.com>,
 Matheus de Oliveira <matioli.matheus@gmail.com>,
 postgres performance list <pgsql-performance@postgresql.org>
In-Reply-To: <1408558502.11006.YahooMailNeo@web122302.mail.ne1.yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Kevin Grittner <kgrittn@ymail.com> wrote:
> Dave Owens <dave@teamunify.com> wrote:
>
>> I now have 8 hours worth of snapshots from pg_stat_activity and
>> pg_locks (16 snapshots from each table/view).=A0 I have turned off
>> collection at this point, but I am still able to query pg_locks
>
> Could you take the earliest one after activity started, and the
> latest one before you stopped collecting them, compress them, and
> email them to me off-list, please?

Dave did this, off-list.=A0 There is one transaction which has been
running for over 20 minutes, which seems to be the cause of the
accumulation.=A0 I note that this query does not hold any of the
locks it would need to take before modifying data, and it has not
been assigned a transactionid -- both signs that it has (so far)
not modified any data.=A0 If it is not going to modify any, it would
not have caused this accumulation of locks if it was flagged as
READ ONLY.=A0 This is very important to do if you are using
serializable transactions in PostgreSQL.

To quantify that, I show the number of SIReadLocks in total:

test=3D# select count(*) from locks_snap_16 where mode =3D 'SIReadLock';
=A0 count
---------
=A03910257
(1 row)

... and the number of those which are only around because there is
an open overlapping transaction, not flagged as read only:

test=3D# select count(*) from locks_snap_16 l
test-#=A0=A0 where mode =3D 'SIReadLock'
test-#=A0=A0=A0=A0 and not exists (select * from locks_snap_16 a
test(#=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 wh=
ere a.locktype =3D 'virtualxid'
test(#=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0 and a.virtualxid =3D l.virtualtransaction);
=A0 count
---------
=A03565155
(1 row)

I can't stress enough how important it is that the advice near the
bottom of this section of the documentation is heeded:

http://www.postgresql.org/docs/9.2/interactive/transaction-iso.html#XACT-SE=
RIALIZABLE

Those bullet-points are listed roughly in order of importance;
there is a reason this one is listed first:

=A0- Declare transactions as READ ONLY when possible.

In some shops using SERIALIZABLE transactions, I have seen them set
default_transaction_read_only =3D on, and explicitly set it off for
transactions which will (or might) modify data.

If you have a long-running report that might itself grab a lot of
predicate locks (a/k/a SIReadLocks), you can avoid that by
declaring the transaction as READ ONLY DEFERRABLE.=A0 If you do that,
the transaction will wait to begin execution until it can acquire a
snapshot guaranteed not to show any anomalies (like the example
referenced in an earlier post can show).=A0 It then runs without
acquiring any predicate locks, just like a REPEATABLE READ
transaction.=A0 In fairly busy benchmarks, we never saw it take more
than six seconds to acquire such a snapshot, although the wait time
is not bounded.=A0 Again, getting such a snapshot will be possible
sooner if you declare transactions as READ ONLY when possible.=A0 :-)

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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