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 <kgri...@ymail.com> wrote: > Andrey Lizenko <lizenk...@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
