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
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.