Re: query against pg_locks leads to large memory alloc

pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed

From: Kevin Grittner <kgrittn@ymail.com>
To: Dave Owens <dave@teamunify.com>
Cc: Andres Freund <andres@2ndquadrant.com>
Cc: Merlin Moncure <mmoncure@gmail.com>
Cc: Matheus de Oliveira <matioli.matheus@gmail.com>
Cc: postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: query against pg_locks leads to large memory alloc
Date: Wed, 20 Aug 2014 11:15:02 -0700
Message-ID: <1408558502.11006.YahooMailNeo@web122302.mail.ne1.yahoo.com> (raw)
In-Reply-To: <CA+OQrzg0OwnU4TyMnwi577M7aCrNjBb0ObJoyZW+ndUWjmOEgg@mail.gmail.com>
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>
List-Unsubscribe:  <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>

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).  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.  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?  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.  One 
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.  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.  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


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

view thread (20+ messages)  latest in thread

Message-ID: <1408558502.11006.YahooMailNeo@web122302.mail.ne1.yahoo.com>
Permalink:  ../1408558502.11006.YahooMailNeo@web122302.mail.ne1.yahoo.com/
Also on:    postgresql.org/message-id/1408558502.11006.YahooMailNeo@web122302.mail.ne1.yahoo.com

 ·

reply

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-performance@postgresql.org
  Cc: kgrittn@ymail.com, dave@teamunify.com, andres@2ndquadrant.com, mmoncure@gmail.com, matioli.matheus@gmail.com
  Subject: Re: query against pg_locks leads to large memory alloc
  In-Reply-To: <1408558502.11006.YahooMailNeo@web122302.mail.ne1.yahoo.com>

* 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