Re: Waiting on ExclusiveLock on extension

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. PG 9.4.1 shall be actually better in relation extension
handling - a possible explanation is that your 9.3.5 database has been
used for a while thus there are holes in pages, so not many extensions
are required.

>
> a) I'm assuming "extension" means growing the physical diskspace used by a
> table-- is that correct?
Yes.

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

> c) Why would Postgres grow a table twice in the same millisecond? Could it
> be an index with a high fill factor?
PG extends one page each time when new space needed.

> 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.

Regards,
Qingqing