Horton

Thread

  1. database response slows while pg_dump is running (8.4.2)

    Aleksey Tsalolikhin <atsaloli.tech@gmail.com> — 2010-06-10T20:25:37Z

    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

  2. Re: database response slows while pg_dump is running (8.4.2)

    Andy Colson <andy@squeakycode.net> — 2010-06-10T21:22:04Z

    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

  3. Re: database response slows while pg_dump is running (8.4.2)

    Greg Smith <greg@2ndquadrant.com> — 2010-06-10T21:23:58Z

    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

  4. Re: database response slows while pg_dump is running (8.4.2)

    Scott Marlowe <scott.marlowe@gmail.com> — 2010-06-10T21:28:38Z

    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?

  5. Re: database response slows while pg_dump is running (8.4.2)

    Andy Colson <andy@squeakycode.net> — 2010-06-10T21:34:06Z

    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

  6. Re: database response slows while pg_dump is running (8.4.2)

    Aleksey Tsalolikhin <atsaloli.tech@gmail.com> — 2010-06-10T21:49:58Z

    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

  7. Re: database response slows while pg_dump is running (8.4.2)

    Steve Crawford <scrawford@pinpointresearch.com> — 2010-06-10T22:41:53Z

    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

  8. Re: database response slows while pg_dump is running (8.4.2)

    Aleksey Tsalolikhin <atsaloli.tech@gmail.com> — 2010-06-10T22:53:25Z

    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

  9. Re: database response slows while pg_dump is running (8.4.2)

    Alan Hodgson <ahodgson@simkin.ca> — 2010-06-10T23:00:58Z

    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."

  10. Re: database response slows while pg_dump is running (8.4.2)

    Stephen Frost <sfrost@snowman.net> — 2010-06-11T01:01:48Z

    * 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

  11. Re: database response slows while pg_dump is running (8.4.2)

    Adrian von Bidder <avbidder@fortytwo.ch> — 2010-06-11T06:43:53Z

    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

  12. Re: database response slows while pg_dump is running (8.4.2)

    Steve Crawford <scrawford@pinpointresearch.com> — 2010-06-11T15:37:48Z

    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

  13. Re: database response slows while pg_dump is running (8.4.2)

    Aleksey Tsalolikhin <atsaloli.tech@gmail.com> — 2010-06-11T21:50:32Z

    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