Обсуждение: database response slows while pg_dump is running (8.4.2)
PostgreSQL 8.4.2 response time slows during pg_dump of a 14 GB database. Specifically, I am monitoring how long it takes to login to our Web app - usually under 2 seconds, but when pg_dump is running, that changes to 2 - 18 seconds. Is there any way I can keep my response fast but still get my pg_dump? Or should I move my pg_dump to a Slony-I slave? Best, -at
On 6/10/2010 3:25 PM, Aleksey Tsalolikhin wrote:
> PostgreSQL 8.4.2 response time slows during pg_dump of a 14 GB database.
>
> Specifically, I am monitoring how long it takes to login to our Web
> app - usually under 2 seconds, but when pg_dump is running, that
> changes to 2 - 18 seconds.
>
> Is there any way I can keep my response fast but still get my pg_dump?
> Or should I move my pg_dump to a Slony-I slave?
>
> Best,
> -at
>
It depends:
if you are cpu bound, you could try nice.
if you are network bound then dont pg_dump across the network
if you are io bound, pg_dump to a different drive, or pg_dump across
the network.
Isnt the slave a backup anyway? Do you need another dump?
-Andy
Aleksey Tsalolikhin wrote: > Is there any way I can keep my response fast but still get my pg_dump? > http://wiki.postgresql.org/wiki/Priorities covers the usual techniques you might use to slow down your pg_dump to where it doesn't impact the main server as much. > Or should I move my pg_dump to a Slony-I slave? > Dumping from a slave is also a popular approach worth considering. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support greg@2ndQuadrant.com www.2ndQuadrant.us
On Thu, Jun 10, 2010 at 3:22 PM, Andy Colson <andy@squeakycode.net> wrote: > On 6/10/2010 3:25 PM, Aleksey Tsalolikhin wrote: >> >> PostgreSQL 8.4.2 response time slows during pg_dump of a 14 GB database. >> >> Specifically, I am monitoring how long it takes to login to our Web >> app - usually under 2 seconds, but when pg_dump is running, that >> changes to 2 - 18 seconds. >> >> Is there any way I can keep my response fast but still get my pg_dump? >> Or should I move my pg_dump to a Slony-I slave? >> >> Best, >> -at >> > > It depends: > if you are cpu bound, you could try nice. > if you are network bound then dont pg_dump across the network > if you are io bound, pg_dump to a different drive, or pg_dump across > the network. > > > Isnt the slave a backup anyway? Do you need another dump? Replication does not equal backup. For instance how would you recover from "delete from table" with no where clause with replication?
On 6/10/2010 4:28 PM, Scott Marlowe wrote: > On Thu, Jun 10, 2010 at 3:22 PM, Andy Colson<andy@squeakycode.net> wrote: >> On 6/10/2010 3:25 PM, Aleksey Tsalolikhin wrote: >>> >>> PostgreSQL 8.4.2 response time slows during pg_dump of a 14 GB database. >>> >>> Specifically, I am monitoring how long it takes to login to our Web >>> app - usually under 2 seconds, but when pg_dump is running, that >>> changes to 2 - 18 seconds. >>> >>> Is there any way I can keep my response fast but still get my pg_dump? >>> Or should I move my pg_dump to a Slony-I slave? >>> >>> Best, >>> -at >>> >> >> It depends: >> if you are cpu bound, you could try nice. >> if you are network bound then dont pg_dump across the network >> if you are io bound, pg_dump to a different drive, or pg_dump across >> the network. >> >> >> Isnt the slave a backup anyway? Do you need another dump? > > Replication does not equal backup. For instance how would you recover > from "delete from table" with no where clause with replication? Ah, thats easy.. you just have to run faster than the packet, back to the room with the slave it in, and unplug the network... :-) but yeah, good point. -Andy
On Thu, Jun 10, 2010 at 1:25 PM, Aleksey Tsalolikhin <atsaloli.tech@gmail.com> wrote: > PostgreSQL 8.4.2 response time slows during pg_dump of a 14 GB database. > ... > > Is there any way I can keep my response fast but still get my pg_dump? > Or should I move my pg_dump to a Slony-I slave? Thank you for your kind replies. More data: 1. We're not dumping across the network. The pg_dump process runs on the same physical server as PostgreSQL. 2. We're not CPU bound - CPU utilization goes from 95% idle to 75% idle during the pg_dump. 3. We ARE disk bound. Disk utilization goes to 94% during the dump. There is an order of magnitude more reads than writes. Greg, I didn't see anything in the "I/O" section of http://wiki.postgresql.org/wiki/Priorities I could use. We ionice our pg_dump process, but it's a PostgreSQL process that stresses the disk. Thanks anyway - glad to hear dumping from a slave is a popular approach, that's what we'll do. This is good, it'll allow us to increase our pg_dumps from 1 a day to as many as we want to feel safe. Best, -at
On 06/10/2010 02:49 PM, Aleksey Tsalolikhin wrote: > > ... > >> Is there any way I can keep my response fast but still get my pg_dump? >> Or should I move my pg_dump to a Slony-I slave? >> > > 1. We're not dumping across the network. The pg_dump > process runs on the same physical server as PostgreSQL. > > ... > 3. We ARE disk bound... So are you writing the backup to the same disk(s) that support the db? What happens if you do the dump from a different machine? You'll increase network traffic, of course, but reduce your disk load. And you won't be keeping your dump on the same machine as your live data. Cheers, Steve
On Thu, Jun 10, 2010 at 3:41 PM, Steve Crawford <scrawford@pinpointresearch.com> wrote: > So are you writing the backup to the same disk(s) that support the db? What > happens if you do the dump from a different machine? You'll increase network > traffic, of course, but reduce your disk load. And you won't be keeping your > dump on the same machine as your live data. Good point. I tried that, actually, but was still disk-bound. (Mostly read activity.) I am moving the pg_dump process to a Slony slave. Thanks! Aleksey
On Thursday, June 10, 2010, Aleksey Tsalolikhin <atsaloli.tech@gmail.com> wrote: > Thanks anyway - glad to hear dumping from a slave is > a popular approach, that's what we'll do. This is good, > it'll allow us to increase our pg_dumps from 1 a day to > as many as we want to feel safe. You should look into PITR backups. You can keep a very current running backup with little overhead. -- "No animals were harmed in the recording of this episode. We tried but that damn monkey was just too fast."
* Aleksey Tsalolikhin (atsaloli.tech@gmail.com) wrote:
> I am moving the pg_dump process to a Slony slave.
Good idea.
> Good point. I tried that, actually, but was still disk-bound.
> (Mostly read activity.)
You could maybe try ionice'ing the PG process that is the pg_dump
connection... Identifying which one that is would be slightly tricky,
but not all *that* difficult. Don't know if you really want to go there
tho.
Thanks,
Stephen
Вложения
Heyho!
On Thursday 10 June 2010 23.49:58 Aleksey Tsalolikhin wrote:
> 3. We ARE disk bound. Disk utilization goes to 94% during
> the dump. There is an order of magnitude more reads than
> writes.
Just speculation, I've not tried this. Perhaps pipe the output of pg_dump
through a software that bandwidth-limits the throughput? (I don't know if
such a command exists, but it probably could be done in 4 lines of Perl or
30 lines of C if necessary.) Or are there so many buffers involved that
this would just cause data to pile up in memory, waiting to be written out?
cheers
-- vbi
--
There’s a big confusion in the terms, as the numbers used in Arabic are
the Hindu numbers and the numbers used in Latin languages are the Arabic
numbers.
-- Lior Kaplan, 1.4.2008
Вложения
On 06/10/2010 11:43 PM, Adrian von Bidder wrote: > > Just speculation, I've not tried this. Perhaps pipe the output of pg_dump > through a software that bandwidth-limits the throughput? (I don't know if > such a command exists, > pv (pipe view) Allows you to monitor rate of transfers through a pipe. Also has options to specify max transfer rate (-L). It's handy for quick-n-dirty disk-rate tests - mostly only useful for bulk read (pv /some/big/file > /dev/null) Or monitor the progress of your dump: pg_dump <your options> | pv > your.dump May not be installed by default - "apt-get install pv" works for Ubuntu but for CentOS/RHEL but you may need to get it from rpmforge or use the source: http://www.ivarch.com/programs/pv.shtml Cheers, Steve
On Fri, Jun 11, 2010 at 08:43:53AM +0200, Adrian von Bidder wrote: > > Just speculation, I've not tried this. Perhaps pipe the output of pg_dump > through a software that bandwidth-limits the throughput? Perhaps. However, moving the pg_dump to a Slony slave has solved my problem. Thanks!! Aleksey