Kevin Grittner <kgrittn@ymail.com> wrote:
> 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?
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