Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id 0A5891297414 for ; Tue, 17 Jul 2012 12:58:10 -0300 (ADT) Received: from vexbert.mr-paradox.net ([2001:470:b:28:f::1]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1SrAA2-0000WG-UZ for pgsql-performance@postgresql.org; Tue, 17 Jul 2012 15:58:09 +0000 Received: by vexbert.mr-paradox.net (Postfix, from userid 18391) id 9BA428464D; Tue, 17 Jul 2012 11:57:52 -0400 (EDT) Date: Tue, 17 Jul 2012 08:57:52 -0700 From: David Kerr To: pgsql-performance@postgresql.org Subject: Process 11812 still waiting for ExclusiveLock on extension of relation Message-ID: <20120717155752.GC38474@mr-paradox.net> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline User-Agent: Mutt/1.4.2.3i X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201207/204 X-Sequence-Number: 47385 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