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