On Fri, Apr 17, 2015 at 1:14 AM, Andres Freund <andres@anarazel.de> wrote:
On 2015-04-16 14:23:25 -0700, Qingqing Zhou wrote: > On Thu, Apr 16, 2015 at 1:24 PM, Andomar <andomar@aule.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@anarazel.de> wrote:
On 2015-04-16 14:23:25 -0700, Qingqing Zhou wrote: > On Thu, Apr 16, 2015 at 1:24 PM, Andomar <andomar@aule.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.