Re: Waiting on ExclusiveLock on extension 9.3, 9.4 and 9.5

On 10/28/15 12:11 PM, Tom Dearman wrote:
> It is also interesting that a later attempt to get the exclusive lock by
> process 41911 says it is waiting for id 41907 even though according to
> the log other processes have already acquired the lock.

Those would be different acquisitions of the same lock.

It's important to understand that lock is an *internal* lock that is
used to protect extending a file on disk. There are 20 places in code
that attempt to acquire this lock (search for LockRelationForExtension),
but the case in access/heap/hio.c is probably pretty representative. It
gets the extension lock, finds a buffer to extend the relation into,
locks the buffer that holds the existing tuple (for an UPDATE only), and
then releases the extension lock.

Looking through there, the obvious places for things to get hung up are
either on extending the file on disk, or finding a place to put the new
buffer in the buffer pool. The later is why there are recommendations on
shrinking shared_buffers. The larger shared_buffers are, the longer it
takes to scan them.

But there's a flip-side: if you set shared_buffers too small, that means
that the system is working very hard to keep ALL the buffers it has in
shared_buffers, which makes it even more expensive to find a victim buffer.

What was shared_buffers originally set too, and how large is this database?
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com