Re: Waiting on ExclusiveLock on extension 9.3, 9.4 and 9.5 - Mailing list pgsql-general
| From | Tom Dearman |
|---|---|
| Subject | Re: Waiting on ExclusiveLock on extension 9.3, 9.4 and 9.5 |
| Date | |
| Msg-id | 18E8A746-C220-4C0D-9EC6-65A1FBDADDFD@gmail.com Whole thread Raw |
| In response to | Waiting on ExclusiveLock on extension 9.3, 9.4 and 9.5 (Tom Dearman <tom.dearman@gmail.com>) |
| Responses |
Re: Waiting on ExclusiveLock on extension 9.3, 9.4 and 9.5
(Jim Nasby <Jim.Nasby@BlueTreble.com>)
|
| List | pgsql-general |
The processes involved are always client (java) processes which finish (normally) quickly. The actual process can be any of the client processes, the point is that an extension to the relation is going on and it seems to acquire excessive locks. You typically see a whole series of process id contentions all waiting for the same extension. I have included an example below. 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.
2015-10-28 15:42:26 GMT [41907]: [9-1] db=betwave,user=betwave_app_user LOG: process 41907 acquired ExclusiveLock on extension of relation 26789 of database 26192 after 2135.238 ms
2015-10-28 15:42:26 GMT [41955]: [4-1] db=betwave,user=betwave_app_user LOG: process 41955 acquired ExclusiveLock on extension of relation 26789 of database 26192 after 2134.035 ms
.......
2015-10-28 15:42:26 GMT [41700]: [9-1] db=betwave,user=betwave_app_user LOG: process 41700 acquired ExclusiveLock on extension of relation 26789 of database 26192 after 2104.766 ms
.......
2015-10-28 15:42:26 GMT [42006]: [19-1] db=betwave,user=betwave_app_user LOG: process 42006 acquired ExclusiveLock on extension of relation 26789 of database 26192 after 2087.745 ms
.......
2015-10-28 15:46:37 GMT [41911]: [25-1] db=betwave,user=betwave_app_user LOG: process 41911 still waiting for ExclusiveLock on extension of relation 26789 of database 26192 after 1000.193 ms
2015-10-28 15:46:37 GMT [41911]: [26-1] db=betwave,user=betwave_app_user DETAIL: Process holding the lock: 41907. Wait queue: 41911, 41918, 41929.
On 28 Oct 2015, at 16:56, Igor Neyman <ineyman@perceptron.com> wrote:From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Tom Dearman
Sent: Wednesday, October 28, 2015 11:44 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Waiting on ExclusiveLock on extension 9.3, 9.4 and 9.5We 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
pgsql-general by date: