Thread: backups blocking everything
I've got a large mixed-used database, with the data warehouse side of things consisting of several tables at hundreds of millions of rows, plus a number of tables with tens of millions. There is partitioning, but as the volume of data has risen, individual partitions have gotten quite large. Hardware is 2x4 core 2.0Ghz Xeon processors, 176GB of RAM, 4 drives in raid 10 for WAL logs and 16 or 20 spindles for data, also in RAID 10. Total database size is currently 399GB - via pg_database_size(). It's also worth noting that we switched from 8.4 to 9.0.4 only about a month ago, and we were not seeing this problem on 8.4.x. The database is growing, but not at some kind of exponential rate. full backup, compressed, on the old hardware was 6.3GB and took about 1:45:00 to be written. Recent backups are 8.3GB and taking 3 or 4 hours. We were not seeing al queries stall out during the backups on 8.4, so far as I am aware.
The time it takes for pg_dump to run has grown from 1 hour to 3 and even 4 hours over the last 6 months, with more than half of that increase occurring since we upgrade to 9.0.x. In the last several weeks (possibly since the upgrade to 9.0.4), we are seeing all connections getting used up (our main apps use connection pools, but monitoring and some utilities are making direct connections for each query, and some of them don't check for the prior query to complete before sending another, which slowly eats up available connections). Even the connection pool apps cease functioning during the backup, however, as all of the connections wind up in parse waiting state. I also see lots of sockets in close wait state for what seems to be an indefinite period while the backup is running and all connections are used up. I assume all of this is the result of pg_dump starting a transaction or otherwise blocking other access. I can get everything using a pool, that's not a huge problem to solve, but that won't fix the fundamental problem of no queries being able to finish while the backup is happening.
I know I'm not the only one running a database of this size. How do others handle backups? At the moment, I don't have replication happening. I can use the old hardware to replicate to. It doesn't have quite the i/o capacity and nowhere near as much RAM, but I wouldn't be looking to use it for querying unless I lost the primary, and it is definitely capable of handling the insert load, at least when the inserts are being done directly. I'm not sure if it is easier or harder for it to handle the same inserts via streaming replication. My question is, what are the performance repercussions of running a pg_dump backup off the replicated server. If it experiences the same kind of lockup, will SR get so far behind that it can't catch up? Is there some other preferred way to get a backup of a large db?
And finally, is the lockout I'm experiencing actually the result of a bug or misuse of pg_dump in some way?
On Thu, Oct 27, 2011 at 11:47 AM, Samuel Gendler <sgendler@ideasculptor.com> wrote: > I've got a large mixed-used database, with the data warehouse side of things > consisting of several tables at hundreds of millions of rows, plus a number > of tables with tens of millions. There is partitioning, but as the volume > of data has risen, individual partitions have gotten quite large. Hardware > is 2x4 core 2.0Ghz Xeon processors, 176GB of RAM, 4 drives in raid 10 for > WAL logs and 16 or 20 spindles for data, also in RAID 10. Total database > size is currently 399GB - via pg_database_size(). It's also worth noting > that we switched from 8.4 to 9.0.4 only about a month ago, and we were not > seeing this problem on 8.4.x. The database is growing, but not at some kind > of exponential rate. full backup, compressed, on the old hardware was 6.3GB > and took about 1:45:00 to be written. Recent backups are 8.3GB and taking 3 > or 4 hours. We were not seeing al queries stall out during the backups on > 8.4, so far as I am aware. > The time it takes for pg_dump to run has grown from 1 hour to 3 and even 4 > hours over the last 6 months, with more than half of that increase occurring > since we upgrade to 9.0.x. In the last several weeks (possibly since the > upgrade to 9.0.4), we are seeing all connections getting used up (our main > apps use connection pools, but monitoring and some utilities are making > direct connections for each query, and some of them don't check for the > prior query to complete before sending another, which slowly eats up > available connections). Even the connection pool apps cease functioning > during the backup, however, as all of the connections wind up in parse > waiting state. I also see lots of sockets in close wait state for what > seems to be an indefinite period while the backup is running and all > connections are used up. I assume all of this is the result of pg_dump > starting a transaction or otherwise blocking other access. I can get > everything using a pool, that's not a huge problem to solve, but that won't > fix the fundamental problem of no queries being able to finish while the > backup is happening. > I know I'm not the only one running a database of this size. How do others > handle backups? At the moment, I don't have replication happening. I can > use the old hardware to replicate to. It doesn't have quite the i/o > capacity and nowhere near as much RAM, but I wouldn't be looking to use it > for querying unless I lost the primary, and it is definitely capable of > handling the insert load, at least when the inserts are being done directly. > I'm not sure if it is easier or harder for it to handle the same inserts > via streaming replication. My question is, what are the performance > repercussions of running a pg_dump backup off the replicated server. If it > experiences the same kind of lockup, will SR get so far behind that it can't > catch up? Is there some other preferred way to get a backup of a large db? > And finally, is the lockout I'm experiencing actually the result of a bug or > misuse of pg_dump in some way? I can't speak to the slower backups on 9.0.x issue, but if I were you I'd be implementing hot standby and moving the backups to the standby (just be aware that pg_dump will effectively pause replication and cause WAL files to accumulate during the dump). merlin
On Thu, Oct 27, 2011 at 1:45 PM, Nicholson, Brad (Toronto, ON, CA) <bnicholson@hp.com> wrote:
>From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Samuel Gendler
>Sent: Thursday, October 27, 2011 12:47 PM
>To: pgsql-performance@postgresql.org
>Subject: [PERFORM] backups blocking everything>What is the I/O utilization like during the dump? I've seen this situation in the past and it was caused be excessively bloated tables causing I/O starvation while they are getting dumped.
>I've got a large mixed-used database, with the data warehouse side of things consisting of several tables at hundreds of millions of rows, plus a number of tables with tens of >millions. There is partitioning, but as the volume of data has risen, individual partitions have gotten quite large. Hardware is 2x4 core 2.0Ghz Xeon processors, 176GB of RAM, 4 drives in >raid 10 for WAL logs and 16 or 20 spindles for data, also in RAID 10. Total database size is currently 399GB - via pg_database_size(). It's also worth noting that we switched from 8.4 to >9.0.4 only about a month ago, and we were not seeing this problem on 8.4.x. The database is growing, but not at some kind of exponential rate. full backup, compressed, on the old hardware >was 6.3GB and took about 1:45:00 to be written. Recent backups are 8.3GB and taking 3 or 4 hours. We were not seeing al queries stall out during the backups on 8.4, so far as I am aware.
>
>The time it takes for pg_dump to run has grown from 1 hour to 3 and even 4 hours over the last 6 months, with more than half of that increase occurring since we upgrade to 9.0.x. In the >last several weeks (possibly since the upgrade to 9.0.4), we are seeing all connections getting used up (our main apps use connection pools, but monitoring and some utilities are making >direct connections for each query, and some of them don't check for the prior query to complete before sending another, which slowly eats up available connections). Even the connection >pool apps cease functioning during the backup, however, as all of the connections wind up in parse waiting state. I also see lots of sockets in close wait state for what seems to be an >indefinite period while the backup is running and all connections are used up. I assume all of this is the result of pg_dump starting a transaction or otherwise blocking other access. I >can get everything using a pool, that's not a huge problem to solve, but that won't fix the fundamental problem of no queries being able to finish while the backup is happening.
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.
>From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Samuel Gendler >Sent: Thursday, October 27, 2011 12:47 PM >To: pgsql-performance@postgresql.org >Subject: [PERFORM] backups blocking everything > >I've got a large mixed-used database, with the data warehouse side of things consisting of several tables at hundreds ofmillions of rows, plus a number of tables with tens of >millions. There is partitioning, but as the volume of data hasrisen, individual partitions have gotten quite large. Hardware is 2x4 core 2.0Ghz Xeon processors, 176GB of RAM, 4 drivesin >raid 10 for WAL logs and 16 or 20 spindles for data, also in RAID 10. Total database size is currently 399GB -via pg_database_size(). It's also worth noting that we switched from 8.4 to >9.0.4 only about a month ago, and we werenot seeing this problem on 8.4.x. The database is growing, but not at some kind of exponential rate. full backup, compressed,on the old hardware >was 6.3GB and took about 1:45:00 to be written. Recent backups are 8.3GB and taking 3 or4 hours. We were not seeing al queries stall out during the backups on 8.4, so far as I am aware. > >The time it takes for pg_dump to run has grown from 1 hour to 3 and even 4 hours over the last 6 months, with more thanhalf of that increase occurring since we upgrade to 9.0.x. In the >last several weeks (possibly since the upgrade to9.0.4), we are seeing all connections getting used up (our main apps use connection pools, but monitoring and some utilitiesare making >direct connections for each query, and some of them don't check for the prior query to complete beforesending another, which slowly eats up available connections). Even the connection >pool apps cease functioning duringthe backup, however, as all of the connections wind up in parse waiting state. I also see lots of sockets in closewait state for what seems to be an >indefinite period while the backup is running and all connections are used up. Iassume all of this is the result of pg_dump starting a transaction or otherwise blocking other access. I >can get everythingusing a pool, that's not a huge problem to solve, but that won't fix the fundamental problem of no queries beingable to finish while the backup is happening. What is the I/O utilization like during the dump? I've seen this situation in the past and it was caused be excessivelybloated tables causing I/O starvation while they are getting dumped. Brad.
On Thu, Oct 27, 2011 at 2:15 PM, Samuel Gendler <sgendler@ideasculptor.com> 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:
On Thu, Oct 27, 2011 at 6:29 PM, Samuel Gendler <sgendler@ideasculptor.com> wrote: > > 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. > # 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 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 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 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? Have you checked syslogs/dmesg/etc for out of the ordinary system events? merlin
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