pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedProcess 11812 still waiting for ExclusiveLock on extension of relation
3+ messages / 2 participants
[nested] [flat]
* Process 11812 still waiting for ExclusiveLock on extension of relation
@ 2012-07-17 15:57 David Kerr <dmk@mr-paradox.net>
0 siblings, 1 reply; 3+ messages in thread
From: David Kerr @ 2012-07-17 15:57 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Process 11812 still waiting for ExclusiveLock on extension of relation
@ 2012-07-18 12:08 Sergey Konoplev <sergey.konoplev@postgresql-consulting.com>
parent: David Kerr <dmk@mr-paradox.net>
0 siblings, 1 reply; 3+ messages in thread
From: Sergey Konoplev @ 2012-07-18 12:08 UTC (permalink / raw)
To: David Kerr <dmk@mr-paradox.net>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Process 11812 still waiting for ExclusiveLock on extension of relation
@ 2012-07-19 01:43 David Kerr <dmk@mr-paradox.net>
parent: Sergey Konoplev <sergey.konoplev@postgresql-consulting.com>
0 siblings, 0 replies; 3+ messages in thread
From: David Kerr @ 2012-07-19 01:43 UTC (permalink / raw)
To: Sergey Konoplev <sergey.konoplev@postgresql-consulting.com>; +Cc: 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 <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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2012-07-19 01:43 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-07-17 15:57 Process 11812 still waiting for ExclusiveLock on extension of relation David Kerr <dmk@mr-paradox.net>
2012-07-18 12:08 ` Sergey Konoplev <sergey.konoplev@postgresql-consulting.com>
2012-07-19 01:43 ` David Kerr <dmk@mr-paradox.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox