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>
Message-ID: <1408558502.11006.YahooMailNeo@web122302.mail.ne1.yahoo.com>
Date: Wed, 20 Aug 2014 11:15:02 -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: 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: 
 <CA+OQrzg0OwnU4TyMnwi577M7aCrNjBb0ObJoyZW+ndUWjmOEgg@mail.gmail.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

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?

> SIReadLocks continue to grow.=A0 It seems, in general, that our
> application code over uses Serializable... we have produced a patch
> that demotes some heavy-hitting queries down to Read Committed, and we
> will see if this makes an impact on the number of SIReadLocks.

Do all of those modify data?=A0 If not, you may get nearly the same
benefit from declaring them READ ONLY instead, and that would get
better protection against seeing transient invalid states.=A0 One=20
example of that is here:

http://wiki.postgresql.org/wiki/SSI#Deposit_Report

> Is it interesting that only 101557 out of 7 million SIReadLocks have a
> pid associated with them?

I would need to double-check that I'm not forgetting another case,
but the two cases I can think of where the pid is NULL are if the
transaction is PREPARED (for two phase commit) or if committed
transactions are summarized (so they can be combined) to try to
limit RAM usage.=A0 We might clear the pid if the connection is
closed, but (without having checked yet) I don't think we did that.
Since you don't use prepared transactions, they are probably from
the summarization.=A0 But you would not normally accumulate much
there unless you have a long-running transaction which is not
flagged as READ ONLY.

--
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