On Jul 18, 2012, at 5:08 AM, Sergey Konoplev wrote: > 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'; 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
