Re: backups blocking everything

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

 





On Thu, Oct 27, 2011 at 1:45 PM, Nicholson, Brad (Toronto, ON, CA) <bnicholson@xxxxxx> wrote:
>From: pgsql-performance-owner@xxxxxxxxxxxxxx [mailto:pgsql-performance-owner@xxxxxxxxxxxxxx] On Behalf Of Samuel Gendler
>Sent: Thursday, October 27, 2011 12:47 PM
>To: pgsql-performance@xxxxxxxxxxxxxx
>Subject: 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.

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.

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.



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

  Powered by Linux