Re: Performance features the 4th

From: Jan Wieck <JanWieck(at)Yahoo(dot)com>
To: "scott(dot)marlowe" <scott(dot)marlowe(at)ihs(dot)com>
Cc: "Matthew T(dot) O'Connor" <matthew(at)zeut(dot)net>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Christopher Browne <cbbrowne(at)acm(dot)org>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Performance features the 4th
Date: 2003-11-09 23:09:52
Message-ID: 3FAEC940.30408@Yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

scott.marlowe wrote:

> On Fri, 7 Nov 2003, Matthew T. O'Connor wrote:
>
>> ----- Original Message -----
>> From: "Jan Wieck" <JanWieck(at)Yahoo(dot)com>
>> > Tom Lane wrote:
>> > > Gaetano and a couple of other people did experiments that seemed to show
>> > > it was useful. I think we'd want to change the shape of the knob per
>> > > later suggestions (sleep 10 ms every N blocks, instead of N ms every
>> > > block) but it did seem that there was useful bang for little buck there.
>> >
>> > I thought it was "sleep N ms every M blocks".
>> >
>> > Have we seen any numbers? Anything at all? Something that gives us a
>> > clue by what factor one has to multiply the total time a "VACUUM
>> > ANALYZE" takes, to get what effect in return?
>>
>> I have some time on sunday to do some testing. Is there a patch that I can
>> apply that implements either of the two options? (sleep 10ms every M blocks
>> or sleep N ms every M blocks).
>>
>> I know Tom posted the original patch that sleept N ms every 1 block (where N
>> is > 10 due to OS limitations). Jan can you post a patch that has just the
>> sleep code in it? Or should it be easy enough for me to cull out of the
>> larger patch you posted?
>
> The reason for the change is that the minumum sleep period on many systems
> is 10mS, which meant that vacuum was running 20X slower than normal.
> While it might be necessary in certain very I/O starved situations to make
> it this slow, it would probably be better to be able to get a vacuum that
> ran at about 1/2 to 1/5 speed for most folks. So, since the delta can't
> less than 10mS on most systems, it's better to just leave it at a fixed
> amount and change the number of pages vacuumed per sleep.

I disagree with that. If you limit yourself to the number of pages being
the only knob you have and set the napping time fixed, you can only
lower the number of sequentially read pages to slow it down. Making read
ahead absurd in an IO starved situation ...

I'll post a patch doing

every N pages nap for M milliseconds

using two GUC variables and based on a select(2) call later.

Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Hannu Krosing 2003-11-09 23:23:48 Re: Dreaming About Redesigning SQL
Previous Message Gaetano Mendola 2003-11-09 22:51:21 Re: Performance features the 4th