Обсуждение: Process 11812 still waiting for ExclusiveLock on extension of relation

Поиск
Список
Период
Сортировка

Process 11812 still waiting for ExclusiveLock on extension of relation

От
David Kerr
Дата:
Howdy,

I've got a couple of tables that are taking a little longer than normal to extend, resulting
in some slow inserts.

They're fairly large tables, ~200GB pg_total_relation_size (90GB for just the table)

I suspect that this is related to a sustained heavy load that would stop autovacuum from
getting at this table... Does that sound plausible?

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 =) )

I suspect that Partitioning would help.  Any other ideas?


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


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

От
Sergey Konoplev
Дата:
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

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

От
David Kerr
Дата:
On Jul 18, 2012, at 5:08 AM, Sergey Konoplev wrote:

> 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';
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