Re: query against pg_locks leads to large memory alloc

Dave Owens <dave(at)teamunify(dot)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