Re: How to monitor locks (max_pred_locks_per_transaction)?

Andrey Lizenko <lizenko79(at)gmail(dot)com> wrote:

>> 2014-12-28 14:33:23 GMT 553582643 24969 SELECT 53200 63/8298433 54a00a84.6189 1 %ERROR: out of shared memory
>> 2014-12-28 14:33:23 GMT 553582643 24969 SELECT 53200 63/8298433 54a00a84.6189 2 %HINT: You might need to increase max_pred_locks_per_transaction.
>
> Is there any way to predict such OOM situation (to adjust
> max_pred_locks_per_transaction before some transaction fails)?
> As far as we have a lot of transaction in SERIALIZABLE isolation
> level, should it be some counts of pg_locks with mode =
> AccessExclusiveLock or something like that?

WHERE mode = 'SIReadLock'

> how can I get number of 'distinct objects' mentioned here?

The total 'SIReadLock' count must be less than
max_pred_locks_per_transaction * max_connections.

The default is small so that minimal space is reserved for those
not using serializable transactions. Many people have found that
they need to set it to 10 to 20 times the default values.

Due to the heuristics of how multiple fine-grained locks are
combined into coarser-grained locks it might sometimes be necessary
(if you have a lot of page locks in a lot of tables) to raise
max_connections beyond what you need for actual connections. I
have not actually seen this yet, but it could happen. If it does,
please share details of the workload and your settings, so that we
can look at possible adjustments to the lock granularity promotion
logic or the memory allocation techniques.

Please note the suggestions on performance in the documentation of
serializable transactions. In particular, if a transaction will
not be modifying data, setting it to READ ONLY can help a lot. Not
only will it help performance, but it will tend to reduce the
number of predicate locks needed.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company