Обсуждение: Process 11812 still waiting for ExclusiveLock on extension of relation
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
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
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