On Thu, Oct 27, 2011 at 2:15 PM, Samuel Gendler <sgendler@xxxxxxxxxxxxxxxx> wrote: 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.
To be honest, I'm not entirely certain how to interpret some of the graphs I'm looking at in this context.
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.
# show work_mem;
work_mem
----------
512MB
(1 row)
# show maintenance_work_mem;
maintenance_work_mem
----------------------
2GB
here are some pictures of what is going on. The db monitoring itself goes away when it eats all of the connections, but you can see what direction they are headed and the values when it finally manages to get a connection again at the end of the period. All of the other numbers are just host monitoring, so they are continuous through the shutout.
Memory usage on the host (shared buffers is set to 8GB):
Swap Usage:
Swap rate:
CPU utilization:
Load Average:
disk IO for system disk (where the backup is being written to):
disk IO for WAL volume:
disk IO for data volume:
Various postgres monitors - the graph names are self explanatory:
