Обсуждение: Max locks
Hello all,
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).
How is this possible???
Cheers,
James Sewell,
The contents of this email are confidential and may be subject to legal or professional privilege and copyright. No representation is made that this email is free of viruses or other defects. If you have received this communication in error, you may not copy or distribute any part of it or otherwise disclose its contents to anyone. Please advise the sender of your incorrect receipt of this correspondence.
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
>>>>> "Peter" == Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes: Peter> max_locks_per_transactions only affects relation locks (also Peter> known as heavy weight locks), but pg_locks also shows other Peter> kinds of locks. pg_locks shows exactly two types of locks: "heavy" locks (which are not merely relation locks but also object, tuple, extension, transaction and advisory locks), and predicate locks (SIReadLock) which are limited by a separate configuration parameter (and are not relevant in this case based on the prior IRC discussion). Peter> Filter by locktype = 'relation' to get the appropriate view. This is incorrect (some predicate locks may be tagged 'relation' as well, and some heavy locks will not be relation locks). There's also the question of fastpath locks, but I believe there can only be a small number of these (16?) per backend, so that wouldn't account for this. I think what's going on is that the max size of the lock hashtable isn't strictly enforced; it'll add enough memory space for the configured number of locks to the total size of the shared memory segment, but it won't actually report an error until shared memory is actually exhausted, and it's possible that there may be unused space. (Performance may degrade if there are more locks than the configured maximum, because the hash table will have been sized for that maximum and can't be grown.) See comments for ShmemInitHash. -- Andrew (irc:RhodiumToad)
pg_locks shows exactly two types of locks: "heavy" locks (which are not
merely relation locks but also object, tuple, extension, transaction and
advisory locks), and predicate locks (SIReadLock) which are limited by a
separate configuration parameter (and are not relevant in this case
based on the prior IRC discussion).
Peter> Filter by locktype = 'relation' to get the appropriate view.
This is incorrect (some predicate locks may be tagged 'relation' as
well, and some heavy locks will not be relation locks).
There's also the question of fastpath locks, but I believe there can
only be a small number of these (16?) per backend, so that wouldn't
account for this.
OK - So how would I calculate the total number of locks which are relevant here (it’s a bit unclear if this is all heavy locks and how to find them)? Ideally I want to alert on locks > configured locks
I think what's going on is that the max size of the lock hashtable isn't
strictly enforced; it'll add enough memory space for the configured
number of locks to the total size of the shared memory segment, but it
won't actually report an error until shared memory is actually
exhausted, and it's possible that there may be unused space.
(Performance may degrade if there are more locks than the configured
maximum, because the hash table will have been sized for that maximum
and can't be grown.) See comments for ShmemInitHash.
How much memory is consumed per configured lock? Is this removed from the shared buffers size or added to it?
Thanks for the follow up!
James Sewell,
Chief Architect
Suite 112, Jones Bay Wharf, 26-32 Pirrama Road, Pyrmont NSW 2009
P (+61) 2 8099 9000 W www.jirotech.com F (+61) 2 8099 9099
Chief Architect
Suite 112, Jones Bay Wharf, 26-32 Pirrama Road, Pyrmont NSW 2009
P (+61) 2 8099 9000 W www.jirotech.com F (+61) 2 8099 9099
The contents of this email are confidential and may be subject to legal or professional privilege and copyright. No representation is made that this email is free of viruses or other defects. If you have received this communication in error, you may not copy or distribute any part of it or otherwise disclose its contents to anyone. Please advise the sender of your incorrect receipt of this correspondence.