Horton

Thread

  1. pg_dump & performance degradation

    Philip Warner <pjw@rhyme.com.au> — 2000-07-28T14:41:52Z

    Brian Baquiran in the [GENERAL] list recently asked if it was possible to
'throttle-down' pg_dump so that it did not cause an IO bottleneck when
copying large tables.

Can anyone see a reason not to pause periodically?

The only problem I have with pausing is that pg_dump runs in a single
transaction, and I have an aversion to keeping TX's open too long, but this
is born of experience with other databases, and may not be relevant to PG.

If it is deemed acceptable, can anyone offer a sensible scheme for pausing?

eg. Allow the user to specify an active:sleep ratio, then after ever 'get'
on the COPY command, see how much time has elaped since it last slept, and
if more than, say, 100ms, then sleep for an amount of time based on the
user's choice.

Finally, can anyone point me to the most portable subsecond timer routines?


----------------------------------------------------------------
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://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

  2. Re: pg_dump & performance degradation

    Tom Lane <tgl@sss.pgh.pa.us> — 2000-07-28T16:22:37Z

    Philip Warner <pjw@rhyme.com.au> writes:
> Brian Baquiran in the [GENERAL] list recently asked if it was possible to
> 'throttle-down' pg_dump so that it did not cause an IO bottleneck when
> copying large tables.

> Can anyone see a reason not to pause periodically?

Because it'd slow things down?

As long as the default behavior is "no pauses", I have no strong
objection.

> Finally, can anyone point me to the most portable subsecond timer routines?

You do not want a timer routine, you want a delay.  I think using a
dummy select() with a timeout parameter might be the most portable way.
Anyway we've used it for a long time --- see the spinlock backoff code
in s_lock.c.

			regards, tom lane

  3. Re: pg_dump & performance degradation

    Philip Warner <pjw@rhyme.com.au> — 2000-07-28T16:52:57Z

    At 12:22 28/07/00 -0400, Tom Lane wrote:
>Philip Warner <pjw@rhyme.com.au> writes:
>> Brian Baquiran in the [GENERAL] list recently asked if it was possible to
>> 'throttle-down' pg_dump so that it did not cause an IO bottleneck when
>> copying large tables.
>
>> Can anyone see a reason not to pause periodically?
>
>Because it'd slow things down?

Cute.


>> Finally, can anyone point me to the most portable subsecond timer routines?
>
>You do not want a timer routine, you want a delay.  I think using a
>dummy select() with a timeout parameter might be the most portable way.
>Anyway we've used it for a long time --- see the spinlock backoff code
>in s_lock.c.

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.

Would there be a portability problem with using setitimer, pause, & sigaction?


----------------------------------------------------------------
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://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

  4. Re: pg_dump & performance degradation

    Tom Lane <tgl@sss.pgh.pa.us> — 2000-07-28T17:36:35Z

    Philip Warner <pjw@rhyme.com.au> writes:
>> You do not want a timer routine, you want a delay.  I think using a
>> dummy select() with a timeout parameter might be the most portable way.
>> Anyway we've used it for a long time --- see the spinlock backoff code
>> in s_lock.c.

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

> Would there be a portability problem with using setitimer, pause, &
> sigaction?

Signal behavior is not very portable, and I'd counsel against
introducing any new portability risks for what's fundamentally a pretty
third-order feature.  (AFAIR no one's ever asked for this before, so...)
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.

			regards, tom lane

  5. Re: pg_dump & performance degradation

    Philip Warner <pjw@rhyme.com.au> — 2000-07-29T04:14:11Z

    At 13:36 28/07/00 -0400, Tom Lane wrote:
>Philip Warner <pjw@rhyme.com.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://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

  6. Re: pg_dump & performance degradation

    Tom Lane <tgl@sss.pgh.pa.us> — 2000-07-29T04:57:46Z

    Philip Warner <pjw@rhyme.com.au> writes:
> 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.

But ... but ... you have no idea at all how much time the backend has
expended to provide you with those rows, nor how much of the elapsed
time was used up by unrelated processes.  It's pointless to suppose
that you are regulating system load this way --- and I maintain that
system load is what the dbadmin would really like to regulate.

You may as well keep it simple and not introduce unpredictable
dependencies into the behavior of the feature.

			regards, tom lane

  7. Re: pg_dump & performance degradation

    Philip Warner <pjw@rhyme.com.au> — 2000-07-29T05:58:17Z

    At 00:57 29/07/00 -0400, Tom Lane wrote:
>Philip Warner <pjw@rhyme.com.au> writes:
>> 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.
>
>But ... but ... you have no idea at all how much time the backend has
>expended to provide you with those rows, nor how much of the elapsed
>time was used up by unrelated processes.

True & true.

But where the time was used is less important to me; if it was used by PG,
or by another process, then it still means that there was a consumer who I
was fighting. All I am trying to do is prevent the consumption of all
available resources by pg_dump. I realize that this is totally opposed to
the notion of a good scheduler, but it does produce good results for me:
when I put delays in pg_dump, I couldn't really tell (from the system
performance) that backup was running.


> It's pointless to suppose
>that you are regulating system load this way --- 

That is true, but what I am regulating is consumption of available
resources (except in the case of delays caused by excessive lock contention).

For the most part, my backups go to 100%CPU, and huge numbers of I/Os. Most
importantly this affects web server response times as well as the time
taken for 'production' database queries (usually via the web).

With a backup process that sleeps, I get free CPU time & I/Os for
opportunistic processes (web servers & db queries), and a backup that takes
more time. This seems like a Good Thing.

Since backups on VMS never cause this sort of problem, I assume I am just
battling the Linux scheduler, rather than a deficiency in Postgres. Maybe
things would be different if I could set the priority on the backend from
the client...that might bear thinking about, but for R/W transactions it
would be a disaster to allow setting of priorities of backend processes.


>and I maintain that
>system load is what the dbadmin would really like to regulate.

In my case, because the scheduler does not cope well at 100% load, I think
I need to keep some resources in reserve. But I agree in principal. 


>You may as well keep it simple and not introduce unpredictable
>dependencies into the behavior of the feature.

This is certainly still an option; I might base the choice on some
empirical tests. I get very different results between a large table with
many columns and a large table with a small number of columns. I'll have to
keep investigating the causes.



----------------------------------------------------------------
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://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

  8. Re: pg_dump & performance degradation

    Don Baccus <dhogaza@pacifier.com> — 2000-07-29T12:28:02Z

    At 02:14 PM 7/29/00 +1000, Philip Warner wrote:

>>(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 can't think of any Unix utility that does this, offhand.

"nice" doesn't help at all when you try it?



- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.

  9. Re: pg_dump & performance degradation

    Philip Warner <pjw@rhyme.com.au> — 2000-07-29T13:27:45Z

    At 05:28 29/07/00 -0700, Don Baccus wrote:
>At 02:14 PM 7/29/00 +1000, Philip Warner wrote:
>
>>>(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 can't think of any Unix utility that does this, offhand.
>
>"nice" doesn't help at all when you try it?

Only marginally; and what I really need to do is 'nice' the backend, and
when I do that it still only helps only a little - even if I drop it to the
lowest priority. I think a process with only a little CPU can still do a
lot of I/O requests, and perhaps it is getting swapped back in to service
the requests. This is just guesswork.




----------------------------------------------------------------
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://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

  10. Re: pg_dump & performance degradation

    Tom Lane <tgl@sss.pgh.pa.us> — 2000-07-29T15:34:05Z

    Philip Warner <pjw@rhyme.com.au> writes:
>> "nice" doesn't help at all when you try it?

> Only marginally; and what I really need to do is 'nice' the backend, and
> when I do that it still only helps only a little - even if I drop it to the
> lowest priority. I think a process with only a little CPU can still do a
> lot of I/O requests, and perhaps it is getting swapped back in to service
> the requests. This is just guesswork.

I think that's true --- on most Unixes, 'nice' level only affects CPU
scheduling not I/O scheduling.

It would be a bad idea to nice down a backend anyway, if the intent is
to speed up other backends.  The Unix scheduler has no idea about
application-level locking, so you'll get priority-inversion problems:
once the nice'd backend has acquired any sort of lock, other backends
that may be waiting for that lock are at the mercy of the low priority
setting.  In effect, your entire database setup may be running at the
nice'd priority relative to anything else on the system.

I think Philip's idea of adding some delays into pg_dump is a reasonable
answer.  I'm just recommending a KISS approach to implementing the
delay, in the absence of evidence that a more complex mechanism will
actually buy anything...

			regards, tom lane

  11. Re: pg_dump & performance degradation

    Philip Warner <pjw@rhyme.com.au> — 2000-07-31T03:20:15Z

    At 11:34 29/07/00 -0400, Tom Lane wrote:
>
>I think Philip's idea of adding some delays into pg_dump is a reasonable
>answer.  I'm just recommending a KISS approach to implementing the
>delay, in the absence of evidence that a more complex mechanism will
>actually buy anything...
>

The results of some experiments:

Unfortunately I have been unable to devise a proper test of the 'fixed'
sleep method: the COPY loop is sufficiently tight so that on a fast machine
even a small delay inside *each* iteration makes for a very slow process
(on my machine, I think the smallest allowed delay is 10ms, and the COPY
loop runs in about 100-300 usec intervals while the COPY buffer is being
dumped). As a result I have had to activate the sleep code only when the
time since it last sleep is > 100ms. Which means that the 'throttle time'
specified by the user is effectively a ratio anyway, and I have implemented
it as such.

Basically, the user can specify a number of ms to wait per second of
'running'. This ratio is checked on each iteration, and when the amount of
time to sleep exceeds 100ms, the sleep call is made, and the timer is reset.

eg. 

    pg_dump dbname -T1000 --tab=big-table > /dev/null

will rest for an average of 1 second for each second running (during COPY);
the actual 'sleeps' will occur every 100ms or so, and last for 100ms.


    pg_dump dbname -T30000 --tab=big-table > /dev/null

will rest for an average of 30 seconds for each second running (during
COPY); the actual 'sleeps' will occur every 3ms or so and last for 100ms

    pg_dump dbname -T500 --tab=big-table > /dev/null

will rest for an average of 0.5 seconds for each second running (during
COPY); the actual 'sleeps' will occur every 200ms or so and last for 100ms.

etc.

This is actually more complex that I had hoped (originally I planned to
just do a simple ratio), but experimentation of a *very* slow mashine (P90)
and a fast-ish one (PIII 550) showed that a range of values to achieve 50%
CPU utilization (on an unloaded machine) varied from as high as 30:1 down
to 0.3:1 (the CPU boost from IO on a P90 is enormous - postmaster runs at
90% CPU until a ratio of about 15:1).

These times are obviously highly subjective, and the only real conclusion I
can draw from them are:

- It's too hard to predict (as Tom suggested)
- It's important to allow for a very tight loop in coding the 'sleep' code.

This is disappointing in the sense that I had hoped to get a
one-number-suits-all-tables-at-a-given-time-on-a-given-machine solution,
but experiments with tables with lots of columns and tables with large
toasted values reveals quite a wide variation even with this model.

Unless someone has a further suggestion, I'll just clean it up and submit
it...


----------------------------------------------------------------
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://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

  12. Re: pg_dump & performance degradation

    Bruce Momjian <pgman@candle.pha.pa.us> — 2000-08-01T01:37:34Z

    I do not think it is a good idea to add some sleep-ability to pg_dump
unless we can get a general agreement that this is a valuable feature.


> At 13:36 28/07/00 -0400, Tom Lane wrote:
> >Philip Warner <pjw@rhyme.com.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://www.rhyme.com.au          |                /           \|
>                                  |    --________--
> PGP key available upon request,  |  /
> and from pgp5.ai.mit.edu:11371   |/
> 


-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

  13. Re: pg_dump & performance degradation

    Bruce Momjian <pgman@candle.pha.pa.us> — 2000-08-01T01:48:41Z

    > It would be a bad idea to nice down a backend anyway, if the intent is
> to speed up other backends.  The Unix scheduler has no idea about
> application-level locking, so you'll get priority-inversion problems:
> once the nice'd backend has acquired any sort of lock, other backends
> that may be waiting for that lock are at the mercy of the low priority
> setting.  In effect, your entire database setup may be running at the
> nice'd priority relative to anything else on the system.
> 
> I think Philip's idea of adding some delays into pg_dump is a reasonable
> answer.  I'm just recommending a KISS approach to implementing the
> delay, in the absence of evidence that a more complex mechanism will
> actually buy anything...

I am worried about feature creep here.  Does any other database
implement this?  I can accept it as a config.h flag, but it seems
publishing it as a pg_dump flag is just way too complicated for users.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

  14. Re: pg_dump & performance degradation

    Philip Warner <pjw@rhyme.com.au> — 2000-08-01T03:06:51Z

    At 21:48 31/07/00 -0400, Bruce Momjian wrote:
>> It would be a bad idea to nice down a backend anyway, if the intent is
>> to speed up other backends.  The Unix scheduler has no idea about
>> application-level locking, so you'll get priority-inversion problems:
>> once the nice'd backend has acquired any sort of lock, other backends
>> that may be waiting for that lock are at the mercy of the low priority
>> setting.  In effect, your entire database setup may be running at the
>> nice'd priority relative to anything else on the system.
>> 
>> I think Philip's idea of adding some delays into pg_dump is a reasonable
>> answer.  I'm just recommending a KISS approach to implementing the
>> delay, in the absence of evidence that a more complex mechanism will
>> actually buy anything...
>
>I am worried about feature creep here.

I agree; it's definitely a non-critical feature. But then, it is only 80
lines of code in one place (including 28 non-code lines). I am not totally
happy with the results it produces, so I have no objection to removing it
all. I just need some more general feedback...


>I can accept it as a config.h flag, 

You mean stick it in a bunch of ifdefs? What is the gain there?


>but it seems
>publishing it as a pg_dump flag is just way too complicated for users.

I've missed something, obviously. What is the problem here?


----------------------------------------------------------------
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://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

  15. Re: pg_dump & performance degradation

    Bruce Momjian <pgman@candle.pha.pa.us> — 2000-08-01T03:41:59Z

    > >> I think Philip's idea of adding some delays into pg_dump is a reasonable
> >> answer.  I'm just recommending a KISS approach to implementing the
> >> delay, in the absence of evidence that a more complex mechanism will
> >> actually buy anything...
> >
> >I am worried about feature creep here.
> 
> I agree; it's definitely a non-critical feature. But then, it is only 80
> lines of code in one place (including 28 non-code lines). I am not totally
> happy with the results it produces, so I have no objection to removing it
> all. I just need some more general feedback...
> 
> 
> >I can accept it as a config.h flag, 
> 
> You mean stick it in a bunch of ifdefs? What is the gain there?
> 
> 
> >but it seems
> >publishing it as a pg_dump flag is just way too complicated for users.
> 
> I've missed something, obviously. What is the problem here?

I am more concerned with giving people a pg_dump option of questionable
value.  I don't have problems adding it to the C code because it may be
of use to some people.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

  16. Re: pg_dump & performance degradation

    Philip Warner <pjw@rhyme.com.au> — 2000-08-01T04:00:28Z

    At 23:41 31/07/00 -0400, Bruce Momjian wrote:
>> 
>> I've missed something, obviously. What is the problem here?
>
>I am more concerned with giving people a pg_dump option of questionable
>value.

And, sadly, I think questionable effectiveness...


>I don't have problems adding it to the C code because it may be
>of use to some people.

It's pretty easy code to write; I'm actually more inclined to remove it and
avoid the clutter. I'll probably put a comment in the code as to how to do
it, and what the pitfalls are - then I/we don't need to maintain any more
unused code.


----------------------------------------------------------------
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://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

  17. Re: pg_dump & performance degradation

    Don Baccus <dhogaza@pacifier.com> — 2000-08-01T04:12:57Z

    At 01:06 PM 8/1/00 +1000, Philip Warner wrote:

>I agree; it's definitely a non-critical feature. But then, it is only 80
>lines of code in one place (including 28 non-code lines). I am not totally
>happy with the results it produces, so I have no objection to removing it
>all. I just need some more general feedback...

Have you tried pg_dump on a multi-processor machine, which most serious
database-backed websites run on these days?   Do you see the same performance
degradation?  My site runs on a dual P450 with RAID 1 LVD disks, and cost
me exactly $2100 to build (would've been less if I'd laid off the extra
cooling fans!)

If not, the target audience has shrunk even further.   Any way you cut it,
backups want to be scheduled for low-volume hours and even internationally
popular sites don't have steady volume for each hour of the 24 hour day.

(after all, some hours of the day that correspond to the normal "traffic
jam" hours fall mostly in areas like the south pacific where few people
live, even if each and every one is an avid user the load they put on
popular sites is bound to be low compared to, say, the eastern seaboard
or Europe)

If your site's so popular that it has to be perky 24 hours a day for a
rabid base of worldwide fans, then you can either:

1. afford to invest in hardware (if not, you're not asking enough of your
   fans)
2. afford to invest in other improvements in your website.

Doing such unheard of things to a utility (again, is there any Unix or NT
or VMS etc utility that has such a user option?) makes me think you're 
finger-pointing at the wrong part of your service.

But, then again, I'm the kinda guy that looks for simple solutions to simple
problems...



- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.

  18. Re: pg_dump & performance degradation

    Don Baccus <dhogaza@pacifier.com> — 2000-08-01T04:15:10Z

    At 11:41 PM 7/31/00 -0400, Bruce Momjian wrote:
>I am more concerned with giving people a pg_dump option of questionable
>value.  I don't have problems adding it to the C code because it may be
>of use to some people.

I'm uncomfortable because I know of no other *nix utility that has such
options.

If they exist, can someone enumerate them?

It's hard to see why PG is such a special case in this regard, requiring
such an overbearingly groty kludge.




- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.

  19. Re: pg_dump & performance degradation

    Bruce Momjian <pgman@candle.pha.pa.us> — 2000-08-01T04:26:06Z

    > At 11:41 PM 7/31/00 -0400, Bruce Momjian wrote:
> >I am more concerned with giving people a pg_dump option of questionable
> >value.  I don't have problems adding it to the C code because it may be
> >of use to some people.
> 
> I'm uncomfortable because I know of no other *nix utility that has such
> options.
> 
> If they exist, can someone enumerate them?
> 
> It's hard to see why PG is such a special case in this regard, requiring
> such an overbearingly groty kludge.

Yes, agreed.  I read a nice article about Mozilla feature-creep, and how
it killed them:

	http://www.suck.com/daily/2000/07/31/

We have been pretty good about keeping ourselves lean and directed.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

  20. Re: pg_dump & performance degradation

    Philip Warner <pjw@rhyme.com.au> — 2000-08-01T04:31:28Z

    At 21:12 31/07/00 -0700, Don Baccus wrote:
>At 01:06 PM 8/1/00 +1000, Philip Warner wrote:
>
>>I agree; it's definitely a non-critical feature. But then, it is only 80
>>lines of code in one place (including 28 non-code lines). I am not totally
>>happy with the results it produces, so I have no objection to removing it
>>all. I just need some more general feedback...
>
>Have you tried pg_dump on a multi-processor machine, which most serious
>database-backed websites run on these days?   Do you see the same performance
>degradation?  My site runs on a dual P450 with RAID 1 LVD disks, and cost
>me exactly $2100 to build (would've been less if I'd laid off the extra
>cooling fans!)

The original request came from a person with a "4-CPU Xeon with 2GB of
RAM", but the "solution" does not seem to work for them (I think), so it's
probably a waste of time.


>Doing such unheard of things to a utility (again, is there any Unix or NT
>or VMS etc utility that has such a user option?) makes me think you're 
>finger-pointing at the wrong part of your service.

I agree; I think in an earlier post I did say that I was trying to 'fix' an
OS issue in the application, which is really not a good thing to do...but
there *is* a problem. Just no solution, apparently.


>But, then again, I'm the kinda guy that looks for simple solutions to simple
>problems...

It was simple when I started. Honest...



----------------------------------------------------------------
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://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

  21. Re: pg_dump & performance degradation

    Philip Warner <pjw@rhyme.com.au> — 2000-08-01T04:34:48Z

    At 21:15 31/07/00 -0700, Don Baccus wrote:
>At 11:41 PM 7/31/00 -0400, Bruce Momjian wrote:
>>I am more concerned with giving people a pg_dump option of questionable
>>value.  I don't have problems adding it to the C code because it may be
>>of use to some people.
>
>I'm uncomfortable because I know of no other *nix utility that has such
>options.
>
>If they exist, can someone enumerate them?
>
>It's hard to see why PG is such a special case in this regard, requiring
>such an overbearingly groty kludge.

Don't beat about the bush. What are you trying to say, here? ;-}.


----------------------------------------------------------------
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://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

  22. Re: pg_dump & performance degradation

    Chris <chrisb@nimrod.itg.telstra.com.au> — 2000-08-01T04:38:43Z

    Is this the sort of problem that nice() might solve, or not?

Bruce Momjian wrote:
> 
> > >> I think Philip's idea of adding some delays into pg_dump is a reasonable
> > >> answer.  I'm just recommending a KISS approach to implementing the
> > >> delay, in the absence of evidence that a more complex mechanism will
> > >> actually buy anything...
> > >
> > >I am worried about feature creep here.
> >
> > I agree; it's definitely a non-critical feature. But then, it is only 80
> > lines of code in one place (including 28 non-code lines). I am not totally
> > happy with the results it produces, so I have no objection to removing it
> > all. I just need some more general feedback...
> >
> >
> > >I can accept it as a config.h flag,
> >
> > You mean stick it in a bunch of ifdefs? What is the gain there?
> >
> >
> > >but it seems
> > >publishing it as a pg_dump flag is just way too complicated for users.
> >
> > I've missed something, obviously. What is the problem here?
> 
> I am more concerned with giving people a pg_dump option of questionable
> value.  I don't have problems adding it to the C code because it may be
> of use to some people.
> 
> --
>   Bruce Momjian                        |  http://candle.pha.pa.us
>   pgman@candle.pha.pa.us               |  (610) 853-3000
>   +  If your life is a hard drive,     |  830 Blythe Avenue
>   +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

  23. Re: pg_dump & performance degradation

    Philip Warner <pjw@rhyme.com.au> — 2000-08-01T04:56:51Z

    At 14:38 1/08/00 +1000, Chris Bitmead wrote:
>
>Is this the sort of problem that nice() might solve, or not?
>

Doesn't seem to; the problem is the IO, I think. Besides, it's a bad idea
to change priorities on backend processes (which is where most of the work
is done).


----------------------------------------------------------------
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://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

  24. Re: [HACKERS] pg_dump & performance degradation

    brianb-pggeneral@edsamail.com — 2000-08-01T05:36:58Z

    Don Baccus writes:

> At 01:06 PM 8/1/00 +1000, Philip Warner wrote:
> 
> >I agree; it's definitely a non-critical feature. But then, it is only 80
> >lines of code in one place (including 28 non-code lines). I am not totally
> >happy with the results it produces, so I have no objection to removing it
> >all. I just need some more general feedback...
> 
> Have you tried pg_dump on a multi-processor machine, which most serious
> database-backed websites run on these days?   Do you see the same performance
> degradation?  My site runs on a dual P450 with RAID 1 LVD disks, and cost
> me exactly $2100 to build (would've been less if I'd laid off the extra
> cooling fans!)

Hi Don. 

I'm the one having the problem. The machine in question is a 4-CPU Xeon
with 4GB RAM running Linux and Postgres6.5. It's not a web application
(although AOLserver is running on the same machine). The table has more
than 10 million rows, but I don't know exactly how many.

In any case, I tried Philip's throttle code, ported to pg_dump for 6.5 but
it doesn't seem to help. It seems that the backend doing the COPY is taking
up all the I/O. If there's no way around that I will probably just have to
schedule a couple of hours of downtime to dump the data.

I would like to extend my thanks to Philip and everyone else for their help
and suggestions.

Brian
--
Brian Baquiran <brianb@edsamail.com>
http://www.baquiran.com/  AIM: bbaquiran 
Work: +63(2)7182222       Home: +63(2) 9227123

I'm smarter than average. Therefore, average, to me, seems kind of stupid. 
People weren't purposely being stupid. It just came naturally.
                              -- Bruce "Tog" Toganazzini

  25. Re: pg_dump & performance degradation

    Thomas Swan <tswan@olemiss.edu> — 2000-08-01T06:49:44Z

    My $.02 ...
  :)

Anyway, the command line option makes the most sense.    An arbitrary or 
relative sleep timer specified as an option is an ideal way to go about 
it.  I really liked the
pg_dump -T {pick a number, any number} idea.   maybe a -d  for delay or 
something...

Note:  A command line option is not too difficult for someone to use.  (I 
saw someone mention this.)   If you're using a GUI and rely on it... get 
someone to add a check box for the option.   Otherwise if your actually 
using a command-line utility, you've got the hardest part licked...  (It's 
a nice FAQ item, why is my db so slow when doing a backup? ... how can I 
fix this? ...)

This seems a little rhetorical in some respects... I would want a backup or 
dump of the DB to hit as quickly as possible... (scheduled for a low load 
time).  But if, hypothetically, the load is constant, then a more relaxed 
backup might be in order...

All in all, hard coding in the delay, would aggravate me and quite a few 
others; can't you hear it, "Why the *#!##@$ is this taking so long?"

-
- Thomas Swan
- Graduate Student  - Computer Science
- The University of Mississippi
-
- "People can be categorized into two fundamental
- groups, those that divide people into two groups
- and those that don't."

  26. Re: pg_dump & performance degradation

    Philip Warner <pjw@rhyme.com.au> — 2000-08-01T06:54:49Z

    At 01:49 1/08/00 -0500, Thomas Swan wrote: 

>>>>

<excerpt>

All in all, hard coding in the delay, would aggravate me and quite a few
others; can't you hear it, "Why the *#!##@$ is this taking so long?"


</excerpt>

<<<<<<<<


Just in case there is a misunderstanding, this was never, ever, part of
the plan or implementation. The 'throttle' code was driven by a command
option...



>>>>



----------------------------------------------------------------

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://www.rhyme.com.au          |                /           \|

                                 |    --________--

PGP key available upon request,  |  /

and from pgp5.ai.mit.edu:11371   |/

  27. Re: pg_dump & performance degradation

    Thomas Swan <tswan@olemiss.edu> — 2000-08-01T07:48:32Z

    >>All in all, hard coding in the delay, would aggravate me and quite a few 
>>others; can't you hear it, "Why the *#!##@$ is this taking so long?"
>Just in case there is a misunderstanding, this was never, ever, part of 
>the plan or implementation. The 'throttle' code was driven by a command 
>option...
It was... my apologies...

Thomas

-
- Thomas Swan
- Graduate Student  - Computer Science
- The University of Mississippi
-
- "People can be categorized into two fundamental
- groups, those that divide people into two groups
- and those that don't."

  28. Re: pg_dump & performance degradation

    Bruce Momjian <pgman@candle.pha.pa.us> — 2000-08-01T12:46:38Z

    > 
> Is this the sort of problem that nice() might solve, or not?

No.  Nice only handles CPU scheduling, not I/O.  In fact, most kernels
give I/O bound processed higher priority because they are using valuable
shared resources while doing the I/O, so the kernel wants it to finish
as quickly as possible.

> 
> Bruce Momjian wrote:
> > 
> > > >> I think Philip's idea of adding some delays into pg_dump is a reasonable
> > > >> answer.  I'm just recommending a KISS approach to implementing the
> > > >> delay, in the absence of evidence that a more complex mechanism will
> > > >> actually buy anything...
> > > >
> > > >I am worried about feature creep here.
> 


-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

  29. Re: pg_dump & performance degradation

    Don Baccus <dhogaza@pacifier.com> — 2000-08-01T13:05:53Z

    At 02:31 PM 8/1/00 +1000, Philip Warner wrote:

>>Have you tried pg_dump on a multi-processor machine, which most serious
>>database-backed websites run on these days?   Do you see the same
performance
>>degradation?  My site runs on a dual P450 with RAID 1 LVD disks, and cost
>>me exactly $2100 to build (would've been less if I'd laid off the extra
>>cooling fans!)
>
>The original request came from a person with a "4-CPU Xeon with 2GB of
>RAM", but the "solution" does not seem to work for them (I think), so it's
>probably a waste of time.

It seems really strange that pg_dump could suck the guts out of a
four-processor
machine.  What kind of device were they backing up to?   Disk?





- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.

  30. Re: pg_dump & performance degradation

    Philip Warner <pjw@rhyme.com.au> — 2000-08-01T13:37:04Z

    At 06:05 1/08/00 -0700, Don Baccus wrote:
>At 02:31 PM 8/1/00 +1000, Philip Warner wrote:
>
>>>Have you tried pg_dump on a multi-processor machine, which most serious
>>>database-backed websites run on these days?   Do you see the same
>performance
>>>degradation?  My site runs on a dual P450 with RAID 1 LVD disks, and cost
>>>me exactly $2100 to build (would've been less if I'd laid off the extra
>>>cooling fans!)
>>
>>The original request came from a person with a "4-CPU Xeon with 2GB of
>>RAM", but the "solution" does not seem to work for them (I think), so it's
>>probably a waste of time.
>
>It seems really strange that pg_dump could suck the guts out of a
>four-processor
>machine.  What kind of device were they backing up to?   Disk?

I *think* you missed the point; the problem is that the CPU is perfectly
adequate to "suck the guts" out of his disks. If it was just a CPU issue,
then 'nice' would work.

The throttle stuff is there to slow down pg_dump and thereby reduce IO
load. AFAIK, there is no supported way of limiting IO demand in Unix. But
I'd love to be corrected. My meagre PIII 550 is perfectly able to simulate
the problem (with just a million rows) - and the code seems to help. But
not apparently for the 4-CPU Xeon (although I don't think he has tested the
most recent code).

But, given the negative response to the code (and the fact that it seems to
only be somewhat effective), I'll probably be ditching it.


----------------------------------------------------------------
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://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/