>From: pgsql-performance-ow...@postgresql.org >[mailto:pgsql-performance-ow...@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 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. Brad. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
