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
