Re: backups blocking everything

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




On Thu, Oct 27, 2011 at 2:15 PM, Samuel Gendler <sgendler@xxxxxxxxxxxxxxxx> 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

 


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux