Hi, On Tue, Jul 17, 2012 at 7:57 PM, David Kerr <dmk@xxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxx) > 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@xxxxxxxxx Skype: gray-hemp Phone: +79160686204 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
