On Fri, Oct 28, 2011 at 2:20 PM, Merlin Moncure
<mmoncure@gmail.com> wrote:
hrm -- it doesn't look like you are i/o bound -- postgres is
definitely the bottleneck. taking a dump off of production is
throwing something else out of whack which is affecting your other
processes.
band aid solutions might be:
*) as noted above, implement hot standby and move dumps to the standby
*) consider adding connection pooling so your system doesn't
accumulate N processes during dump
Both are in the works. The 1st one is more involved, but I'm going to move our monitoring to a pool next week, so I at least stop getting locked out. We'll be moving to a non-superuser user, as well. That's an artifact of the very early days that we never got around to correcting.
a better diagnosis might involve:
*) strace of one of your non-dump proceses to see where the blocking
is happening
*) profiling one of your user processes and compare good vs bad time
This only happens at a particularly anti-social time, so we're taking the easy way out up front and just killing various suspected processes each night in order to narrow things down. It looks like it is actually an interaction between a process that runs a bunch of fairly poorly architected queries running on a machine set up with the wrong time zone, which was causing it to run at exactly the same time as the backups. We fixed the time zone problem last night and didn't have symptoms, so that's the fundamental problem, but the report generation process has a lot of room for improvement, regardless. There's definitely lots of room for improvement, so it's now really about picking the resolutions that offer the most bang for the buck. I think a hot standby for backups and report generation is the biggest win, and I can work on tuning the report generation at a later date.
Is there anything out of the ordinary about your application that's
worth mentioning? using lots of subtransactions? prepared
transactions? tablespaces? huge amounts of tables? etc?
Nope. Pretty normal.
Have you checked syslogs/dmesg/etc for out of the ordinary system events?
nothing.
Thanks for taking the time to go through my information and offer up suggestions, everyone!
--sam