Re: Process 11812 still waiting for ExclusiveLock on extension of relation

От: David Kerr
Тема: Re: Process 11812 still waiting for ExclusiveLock on extension of relation
Дата: ,
Msg-id: 8FB06688-7378-44D9-B2BA-AC7EEE552AFC@mr-paradox.net
(см: обсуждение, исходный текст)
Ответ на: Re: Process 11812 still waiting for ExclusiveLock on extension of relation  (Sergey Konoplev)
Список: pgsql-performance

On Jul 18, 2012, at 5:08 AM, Sergey Konoplev wrote:

> Hi,
>
> On Tue, Jul 17, 2012 at 7:57 PM, David Kerr <> wrote:
>> I suspect that this is related to a sustained heavy load that would stop autovacuum from
>> getting at this table... Does that sound plausible?
>
> Well, not sure. Let us look at the table's statistics first.
>
> \x
> select * from pg_stat_user_tables where relname = 'yourtablename';
the load is controlled and only lasts a few hours. at this point auto vacuum has gotten to the table and done it's
thing.

>
>> I'm wondering what options I have to smooth over these episodes / speed up the extensions.
>> I'm thinking of something like, CLUSTER or VACUUM FULL (those take quite a run so I'd like
>> some direction on it before i TiaS =) )
>
> Instead of CLUSTER I would suggest you to use one of the tools below.
> They do not block the table as CLUSTER does.
>
> pg_reorg http://reorg.projects.postgresql.org/pg_reorg.html
> Faster, but requires a lot of IO and additional disk space, also it
> needs PK on the table.
>
> pgcompactor http://code.google.com/p/pgtoolkit/
> Allows to smooth IO, auto-determines reorganizing necessity for tables
> and indexes, no PK restriction.

I haven't given these projects much thought in the past, but I guess we're getting to the size where that sort
of thing might come in handy. I'll have a look.

>
>> I suspect that Partitioning would help.  Any other ideas?
>
> Partitioning is a good thing to think about when you deal with big tables.

Yeah. unless you're using hibernate which expects inserts to return the # of rows entered (unless
you disable that) which we are. or you have fairly dynamic data that doesn't have a great partition key.


thanks


В списке pgsql-performance по дате сообщения:

От: David Kerr
Дата:
Сообщение: Re: Process 11812 still waiting for ExclusiveLock on extension of relation
От: Felix Scheicher
Дата:
Сообщение: queries are fast after dump->restore but slow again after some days dispite vacuum