Re: backups blocking everything

On Thu, Oct 27, 2011 at 2:15 PM, Samuel Gendler <sgendler@ideasculptor.com> wrote:

There are definitely no bloated tables.  The large tables are all insert-only, and old data is aggregated up and then removed by dropping whole partitions.  There should be no bloat whatsoever.  The OLTP side of things is pretty minimal, and I can pg_dump those schemas in seconds, so they aren't the problem, either.  I don't know what the I/O utilization is during the dump, offhand. I'll be doing a more thorough investigation tonight, though I suppose I could go look at the monitoring graphs if I weren't in the middle of 6 other things at the moment.  the joys of startup life.

Does pg_dump use work_mem, maintenance_work_mem, or both?  I'm seeing a huge spike in swap-in during the period when I can't get into the db, then a little bit of swap out toward the end.  We've got very little OLTP traffic - like one or two users logged in and interacting with the system at a time, at most, so I've got work_mem set pretty high, as most of our reporting queries do large aggregations that grind to a halt if they go to disk. Besides, we've got nearly 200GB of RAM.  But it would seem that pg_dump is allocating a large number of work_mem (or maintenance_work_mem) segments.