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

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

> Hi,
>
> On Tue, Jul 17, 2012 at 7:57 PM, David Kerr <dmk(at)mr-paradox(dot)net> 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