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