Re: Waiting on ExclusiveLock on extension

On 2015-04-16 14:23:25 -0700, Qingqing Zhou wrote:
> On Thu, Apr 16, 2015 at 1:24 PM, Andomar <andomar(at)aule(dot)net> wrote:
> > After upgrading our database from 9.3.5 to 9.4.1 last night, the server
> > suffers from high CPU spikes. During these spikes, there are a lot of these
> > messages in the logs:
> >
> > process X still waiting for ExclusiveLock on extension of relation Y of
> > database Z after 1036.234 ms
> >
> This issue has been complained several times, and here is the most recent one:
> http://www.postgresql.org/message-id/0DDFB621-7282-4A2B-8879-A47F7CECBCE4@simply.name
>
> PG 9.4.1 shall have much alleviated it by relaxing buffer pool related
> locks.

Hm. I'm not aware of related changes in 9.4? 9.5 should be a bit better,
but I don't think 9.4 will make much of a difference.

> > b) How can you find the name of the relation being extended? based on the
> > relation number.
> select <number>::regclass;

That's not correct. The relfilenode is only equivalent to the relation's
oid when you initially create it. But once rewritten it'll change.

Rather use

SELECT oid::regclass FROM pg_class WHERE pg_relation_filenode(oid) = 2937136;

> > Any suggestions on how to approach this issue are welcome.
> >
> There are some diagnosis in above link, see if it rings any bell. From
> PG kernel side, I think fundamentally we may want to extend many pages
> each time instead of one.

I don't really agree that that's the most important bit. See
http://archives.postgresql.org/message-id/20150329185619.GA29062%40alap3.anarazel.de

Greetings,

Andres Freund