Re: backups blocking everything

On Thu, Oct 27, 2011 at 2:15 PM, Samuel Gendler
<sgendler(at)ideasculptor(dot)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.

# show work_mem;
work_mem
----------
512MB
(1 row)

# show maintenance_work_mem;
maintenance_work_mem
----------------------
2GB

To be honest, I'm not entirely certain how to interpret some of the graphs
I'm looking at in this context.

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):

http://photos.smugmug.com/photos/i-sQ4hVCz/0/L/i-sQ4hVCz-L.png

Swap Usage:

http://photos.smugmug.com/photos/i-T25vcZ2/0/L/i-T25vcZ2-L.png

Swap rate:

http://photos.smugmug.com/photos/i-WDDcN9W/0/L/i-WDDcN9W-L.png

CPU utilization:

http://photos.smugmug.com/photos/i-4xkGqjB/0/L/i-4xkGqjB-L.png

Load Average:

http://photos.smugmug.com/photos/i-p4n94X4/0/L/i-p4n94X4-L.png

disk IO for system disk (where the backup is being written to):

http://photos.smugmug.com/photos/i-gbCxrnq/0/M/i-gbCxrnq-M.png

disk IO for WAL volume:

http://photos.smugmug.com/photos/i-5wNwrDX/0/M/i-5wNwrDX-M.png

disk IO for data volume:

http://photos.smugmug.com/photos/i-r7QGngG/0/M/i-r7QGngG-M.png

Various postgres monitors - the graph names are self explanatory:

http://photos.smugmug.com/photos/i-23sTvLP/0/M/i-23sTvLP-M.png
http://photos.smugmug.com/photos/i-73rphrf/0/M/i-73rphrf-M.png
http://photos.smugmug.com/photos/i-rpKvrVJ/0/L/i-rpKvrVJ-L.png
http://photos.smugmug.com/photos/i-QbNQFJM/0/L/i-QbNQFJM-L.png