Re: Max locks

On 2019-12-19 10:33, James Sewell wrote:
> I have a system which is giving me the log hint to increase 
> max_locks_per_transaction. This is somewhat expected due to the workload 
> - but what I can't understand is the numbers:
> 
> Based on the docs I calculate my theoretical max locks as:
> 
> max_locks_per_transaction * (max_connections + max_prepared_transactions)
> 256 * (600 + 0) = *153600*
> 
> However, looking at my Prometheus monitoring (polling every 15s) which 
> does a SELECT from pg_locks and groups by mode I can see there are over 
> 500K AccessShareLocks consistently (up to around 570K at peak).

max_locks_per_transactions only affects relation locks (also known as 
heavy weight locks), but pg_locks also shows other kinds of locks. 
Filter by locktype = 'relation' to get the appropriate view.

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services