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>
 <1408399307.93048.YahooMailNeo@web122304.mail.ne1.yahoo.com>
 <12486.1408404273@sss.pgh.pa.us>
 <1408448332.31825.YahooMailNeo@web122302.mail.ne1.yahoo.com>
 <CA+OQrzh1fOgZ7WQDm+hwNh-32ONk5xvf7L5cbiz=9ycb0PdzcA@mail.gmail.com>
 <1408466441.59082.YahooMailNeo@web122304.mail.ne1.yahoo.com>
 <CA+OQrzgMRG3y5htc6SPyfT2wzo1xBJ=+xa84ok=bWMr3fHU3Rg@mail.gmail.com>
Message-ID: <1408470926.74229.YahooMailNeo@web122303.mail.ne1.yahoo.com>
Date: Tue, 19 Aug 2014 10:55:26 -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: Tom Lane <tgl@sss.pgh.pa.us>, Merlin Moncure <mmoncure@gmail.com>,
 Matheus de Oliveira <matioli.matheus@gmail.com>,
 postgres performance list <pgsql-performance@postgresql.org>
In-Reply-To: 
 <CA+OQrzgMRG3y5htc6SPyfT2wzo1xBJ=+xa84ok=bWMr3fHU3Rg@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 do see a handful of backends that like to stay IDLE in
> transaction for minutes at a time.=A0 We are refactoring the
> application responsible for these long IDLE times, which will
> hopefully reduce the duration of their connections.

That may help some.=A0 Other things to consider:

=A0- If you can use a connection pooler in transaction mode to reduce
the number of active connections you may be able to improve
performance all around, and dodge this problem in the process.
Very few systems can make efficient use of hundreds of concurrent
connections, but for various reasons fixing that with a connection
pooler is sometimes difficult.

=A0- If you have transactions (or SELECT statements that you run
outside of explicit transactions) which you know will not be
modifying any data, flagging them as READ ONLY will help contain
the number of predicate locks and will help overall performance.
(If the SELECT statements are not in explicit transactions, you may
have to put them in one to allow the READ ONLY property to be set,
or set default_transaction_read_only in the session to accomplish
this.)

=A0- Due to the heuristics used for thresholds for combining
fine-grained locks into coarser ones, you might be able to work
around this by boosting max_connections above the number you are
going to use.=A0 Normally when you increase
max_pred_locks_per_transaction it increases the number of page
locks it will allow in a table or index before it combines them
into a relation lock; increasing max_connections doesn't affect the
granularity promotion threshold, but it increases the total number
of predicate locks allowed, so if you boost that and reduce
max_pred_locks_per_transaction in proportion, you may be able to
dodge the problem.=A0 It's an ugly workaround, but it might get you
into better shape.=A0 If that does work, it's good evidence that we
should tweak those heuristics.

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