Thread: How to monitor locks (max_pred_locks_per_transaction)?
Hi,
I've got
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?
Documentation says, that it is an average value, and every single transaction could lock more objects:
objects (e.g., tables); hence, no more than this many distinct objects can be locked at any one time. This parameter controls the average number of object locks allocated for each transaction; individual transactions can lock more objects as long as the locks of all transactions fit in the lock table.
how can I get number of 'distinct objects' mentioned here?
Andrey Lizenko <lizenko79@gmail.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
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@ymail.com> wrote:
Andrey Lizenko <lizenko79@gmail.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
Andrey Lizenko <lizenko79@gmail.com> wrote: > does max_locks_per_transaction limit all others modes of locks by > the same way? In the sense that the count of all other locks must be less than max_locks_per_transaction * max_connections, yes. Most other locks do not persist past the end of transactions or the closing of connections (advisory locks and locks related to prepared transactions being edge-case exceptions). No other types of locks have the granularity promotion -- most other locks showing in sp_locks are on relations or transactions. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company