Dave Owens <dave@xxxxxxxxxxxxx> 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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
