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

Hi,

On Tue, Jul 17, 2012 at 7:57 PM, David Kerr <dmk@mr-paradox.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';

> 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 suspect that Partitioning would help.  Any other ideas?

Partitioning is a good thing to think about when you deal with big tables.

>
>
> Jul 17 08:11:52 perf: [3-1] user=test,db=perf LOG:  process 11812 still waiting for ExclusiveLock
> on extension of relation 60777 of database 16387 after 1000.270 ms
>
> System resouces were fine:
>
> PGDATA
> ------
> 07/17/12 08:11:48
> Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
> dm-2              1.20  3085.20   77.20 3994.20 15363.20 56680.00    17.69    15.57    3.82   0.06  26.22
>
> 07/17/12 08:11:53
> Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
> dm-2              0.40  2097.20   51.80 2610.20 10344.00 37659.20    18.03     5.23    1.96   0.05  14.28
>
>
> PGXLOG
> ------
> 07/17/12 08:11:48
> Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
> dm-4              0.00  3958.20    0.00  600.40     0.00 36449.60    60.71     0.44    0.74   0.73  43.54
>
> 07/17/12 08:11:53
> Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
> dm-4              0.00  2905.20    0.00  403.40     0.00 26492.80    65.67     0.32    0.80   0.79  31.96
>
> CPU
> ------
>                 CPU     %user     %nice   %system   %iowait    %steal     %idle
> 08:11:48        all     24.49      0.00      3.19      1.17      0.00     71.15
> 08:11:53        all     17.53      0.00      3.13      0.68      0.00     78.65
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance



--
Sergey Konoplev

a database architect, software developer at PostgreSQL-Consulting.com
http://www.postgresql-consulting.com

Jabber: gray.ru@gmail.com Skype: gray-hemp Phone: +79160686204