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

From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Tom Dearman
Sent: Wednesday, October 28, 2015 11:44 AM
To: pgsql-general(at)postgresql(dot)org
Subject: [GENERAL] Waiting on ExclusiveLock on extension 9.3, 9.4 and 9.5

We have a performance problem when our postgres is under high load. The CPU usage is very low, we have 48 cores for our postgres and the idle time averages at 90%. The problem is we get spikes in our transaction times which don’t appear with any obvious regularity and when we get the larger spikes, if I look in the postgres log we see that there is locking on 'process 41915 acquired ExclusiveLock on extension of relation 27177 of database 26192’. The actual relation changes one time it might be one table and another time another, though they are always big tables. I have looked at various previous threads and the only suggestions are either that the disk io is maxed out, which from our observations we don’t believe is the case for us, or that ‘shared_buffers’ is to large - so we have reduced this right down to 1G. In the previous threads there was an indication that the underlying problem was a lock which I believe has been either removed or much improved in 9.5 (see Lock scalability improvements), however we have not seen any improvement in the relation extension locking problem that we see. The version of 9.5 that we have tested is beta1. Any help in showing us how to improve this would be greatly appreciated.

Do you know what “process 41915” is? And what it was doing to cause ExclusiveLock?

Regards,
Igor Neyman