Re: Waiting on ExclusiveLock on extension

On Fri, Apr 17, 2015 at 1:14 AM, Andres Freund <andres(at)anarazel(dot)de> wrote:

> 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:
>
> > > 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;
>

But the thing being reported in the lock wait log message is the relation
OID itself, not the relfilenode.

You would use your query if you get the number by watching the file names
in the file system as they grow,
but not if you get it from the log message.

Cheers,

Jeff

On Fri, Apr 17, 2015 at 1:14 AM, Andres Freund <andres(at)anarazel(dot)de> wrote:

> 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
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>