SEO

Jim C. Nasby wrote:

> I'll generally start with a cost delay of 20ms and adjust based on IO
> utilization.

I've been considering set a default autovacuum cost delay to 10ms; does
this sound reasonable?

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

On Thu, Jan 25, 2007 at 12:52:02AM -0300, Alvaro Herrera wrote:
> Jim C. Nasby wrote:
>
> > I'll generally start with a cost delay of 20ms and adjust based on IO
> > utilization.
>
> I've been considering set a default autovacuum cost delay to 10ms; does
> this sound reasonable?

For a lightly loaded system, sure. For a heavier load that might be too
much, but of course that's very dependent on not only your hardware, but
how much you want vacuum to interfere with normal operations. Though,
I'd say as a default it's probably better to be more aggressive rather
than less.

Also, it might be better to only set autovac_cost_delay by default;
presumably if someone's running vacuum by hand they want it done pronto.
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Jim C. Nasby wrote:
> On Thu, Jan 25, 2007 at 12:52:02AM -0300, Alvaro Herrera wrote:
>> Jim C. Nasby wrote:
>>
>>> I'll generally start with a cost delay of 20ms and adjust based on IO
>>> utilization.
>> I've been considering set a default autovacuum cost delay to 10ms; does
>> this sound reasonable?

The problem in here is that we can not easily find a direct relation
between
Cost delay <-> CPU/IO utilization <--> real performance (response
time in peak hour)

It is very hard for any normal user to set this correctly. I think the
experience / trial-and-error approach is awful for the user, every DBA
need to be an expert of vacuum to keep the system stable. For vacuum is
still a big threat to the performance, a more intelligent way is needed.

A lot of efforts have contributed to make vacuum to be a more
lightweight operation, but I think we should still need more efforts on
how to make it can be used easily and safely.

So I have proposed the "vacuum in time" feature in previous; just let
vacuum know how long can it runs, and then it will minimize the impact
in the time span for you. Some argue that it should not have the
maintenance window assumption, but the most safely way is to run in the
maintenance window.





---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

On Thu, Jan 25, 2007 at 07:52:50PM +0900, Galy Lee wrote:
> It is very hard for any normal user to set this correctly. I think the
> experience / trial-and-error approach is awful for the user, every DBA
> need to be an expert of vacuum to keep the system stable. For vacuum is
> still a big threat to the performance, a more intelligent way is needed.

Agreed.

> So I have proposed the "vacuum in time" feature in previous; just let
> vacuum know how long can it runs, and then it will minimize the impact
> in the time span for you. Some argue that it should not have the
> maintenance window assumption, but the most safely way is to run in the
> maintenance window.

Most systems I work on don't have a maintenance window. For those that
do, the window is at best once a day, and that's nowhere near often
enough to be vacuuming any database I've run across. I'm not saying they
don't exist, but effort put into restricting vacuums to a maintenance
window would serve very few people. It'd be much better to put effort
into things like piggyback vacuum.
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

On Thu, Jan 25, 2007 at 08:04:49AM -0800, Joshua D. Drake wrote:
>
> It really depends on the system. Most of our systems run anywhere from
> 10-25ms. I find that any more than that, Vacuum takes too long.


How do you measure the impact of setting it to 12 as opposed to 15?

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

On Jan 25, 2007, at 10:33 AM, Ray Stell wrote:
> On Thu, Jan 25, 2007 at 08:04:49AM -0800, Joshua D. Drake wrote:
>>
>> It really depends on the system. Most of our systems run anywhere
>> from
>> 10-25ms. I find that any more than that, Vacuum takes too long.
>
>
> How do you measure the impact of setting it to 12 as opposed to 15?

If you've got a tool that will report disk utilization as a
percentage it's very easy; I'll decrease the setting until I'm at
about 90% utilization with the system's normal workload (leaving some
room for spikes, etc). Sometimes I'll also tune the costs if reads
vs. writes are a concern.
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)



---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org