Re: How to monitor locks (max_pred_locks_per_transaction)?

Thanks for your detailed explanation, Kevin. I will check my system again
keeping SIReadLock in mind.

By the way, does max_locks_per_transaction limit all others modes of locks
by the same way?

On 7 January 2015 at 00:34, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:

> 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
>

--
Regards, Andrey Lizenko