On Fri, Nov 9, 2012 at 5:28 PM, Lists <lists@xxxxxxxxxxxxxxxxxx> wrote: > As I've spent a considerable amount of time trying to sort this out, I'm > posting it for the benefit other users. SNIP > D) concurrent use of pg_dump; Not usually a problem, unless it's overloading your IO subsystem. > C) use of transactions, especially prepared transactions and multiple > savepoints; > E) use of numerous databases on a single server, average about 50; These two can be killers. Long running transactions can cause autovacuum processes to stall out or be autocancelled. As well, since the default nap time is 1 minute, it will take at least 50 minutes to vacuum each db as nap time is how long autovac waits between databases. Reducing autovacuum nap time to 5 or 10 seconds would be a good move here, also possibly making it more aggressive by increasing max worker threads, decreasing cost delay (possibly to zero or close to it) and / or increasing cost limit. After making such a change then watching iostat when vacuum is running to see how hard its hitting your IO subsystem. I'm guessing that with SSDs it isn't gonna be a big problem. As Greg Smith has pointed out in the past, usually the answer to an autovacuum problem is making it more, not less aggressive. Unless you're flooding your IO this is almost always the right answer. Keep in mind that autovacuum by default is setup to be VERY unaggressive because it may be running on a netbook for all it knows. To tune autovacuum with 50 databases, start by dropping nap time to something much lower, like 10s. Then if you need to, drop cost delay until you get to 0. If you get to 0 and it's still not hitting your IO too hard, but not keeping up, then increase cost limit. If you get to something in the 5000 to 10000 range, and its still not keeping up then start bumping the thread count -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
