Skip site navigation (1) Skip section navigation (2)

Re: pg_dump & performance degradation

From: Philip Warner <pjw(at)rhyme(dot)com(dot)au>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-hackers(at)postgresql(dot)org, brianb-pggeneral(at)edsamail(dot)com
Subject: Re: pg_dump & performance degradation
Date: 2000-07-29 04:14:11
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-generalpgsql-hackers
At 13:36 28/07/00 -0400, Tom Lane wrote:
>Philip Warner <pjw(at)rhyme(dot)com(dot)au> writes:
>> Well...pg_dump sits in a loop reading COPY output; my hope was to see how
>> long the copy took, and then wait an appropriate amount of time. The dummy
>> select works nicely as a sleep call, but I can't really tell how long to
>> sleep without a sub-second timer, or something that tells me the time
>> between two calls.
>Seems like just delaying for a user-specifiable number of microseconds
>between blocks or lines of COPY output would get the job done. 

You're probably right; and if I can't trust setitimer, sigaction and pause,
then I guess I have no choice.

> I'm not
>clear what the reason is for needing to measure anything --- the user is
>going to be tweaking the parameter anyway to arrive at what he feels is
>an acceptable overall system load from the backup operation, so how are
>you making his life easier by varying the delay?
>We do have an existing dependency on gettimeofday() in postgres.c's
>ShowUsage(), so if you really feel a compulsion to measure then that's
>what to use.  I don't see what it's buying you though.

The plan was for the user to specify a single number that was the ratio of
time spent sleeping to the time spent 'working' (ie. reading COPY lines).

In the ordinary case this value would be 0 (no sleep), and for a very low
load model it might be as high as 10 - for every 100ms spent working it
spends 1000ms sleeping.

This was intended to handle the arbitrary speed variations that occur when
reading, eg, large toasted rows and reading lots of small normal rows. A
simple 'wait 200ms' model would be fine for the former, but way too long
for the latter.

>(AFAIR no one's ever asked for this before, so...)

like most of these things (at least for me), it is personally relevant: I
also experience severe peformance degradation during backups.

I'll look at gettimeofday...

Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.C.N. 008 659 498)             |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from   |/

In response to


pgsql-hackers by date

Next:From: Tom LaneDate: 2000-07-29 04:39:17
Subject: Hmm ... shouldn't path_distance be MIN distance not MAX distance?
Previous:From: Tom LaneDate: 2000-07-29 03:58:03
Subject: pre-6.1-to-6.1 conversion procs slated for destruction

pgsql-general by date

Next:From: Andrew SnowDate: 2000-07-29 04:25:59
Subject: RE: Re: 4 billion record limit?
Previous:From: Tom LaneDate: 2000-07-29 03:43:40
Subject: Re: Hopefully simple date conversion question

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group