Kevin Grittner <kgrittn@xxxxxxxxx> wrote: > 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? Dave did this, off-list. There is one transaction which has been running for over 20 minutes, which seems to be the cause of the accumulation. I note that this query does not hold any of the locks it would need to take before modifying data, and it has not been assigned a transactionid -- both signs that it has (so far) not modified any data. If it is not going to modify any, it would not have caused this accumulation of locks if it was flagged as READ ONLY. This is very important to do if you are using serializable transactions in PostgreSQL. To quantify that, I show the number of SIReadLocks in total: test=# select count(*) from locks_snap_16 where mode = 'SIReadLock'; count --------- 3910257 (1 row) ... and the number of those which are only around because there is an open overlapping transaction, not flagged as read only: test=# select count(*) from locks_snap_16 l test-# where mode = 'SIReadLock' test-# and not exists (select * from locks_snap_16 a test(# where a.locktype = 'virtualxid' test(# and a.virtualxid = l.virtualtransaction); count --------- 3565155 (1 row) I can't stress enough how important it is that the advice near the bottom of this section of the documentation is heeded: http://www.postgresql.org/docs/9.2/interactive/transaction-iso.html#XACT-SERIALIZABLE Those bullet-points are listed roughly in order of importance; there is a reason this one is listed first: - Declare transactions as READ ONLY when possible. In some shops using SERIALIZABLE transactions, I have seen them set default_transaction_read_only = on, and explicitly set it off for transactions which will (or might) modify data. If you have a long-running report that might itself grab a lot of predicate locks (a/k/a SIReadLocks), you can avoid that by declaring the transaction as READ ONLY DEFERRABLE. If you do that, the transaction will wait to begin execution until it can acquire a snapshot guaranteed not to show any anomalies (like the example referenced in an earlier post can show). It then runs without acquiring any predicate locks, just like a REPEATABLE READ transaction. In fairly busy benchmarks, we never saw it take more than six seconds to acquire such a snapshot, although the wait time is not bounded. Again, getting such a snapshot will be possible sooner if you declare transactions as READ ONLY when possible. :-) -- 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
