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

Re: Analyse without locking?

From: Richard Neill <rn214(at)cam(dot)ac(dot)uk>
Cc: PostgreSQL Performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Analyse without locking?
Date: 2009-11-29 01:31:23
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
Thanks for your explanations.

Tom Lane wrote:
> Richard Neill <rn214(at)cam(dot)ac(dot)uk> writes:
>> Now, I understand that increasing checkpoint_segments is generally a 
>> good thing (subject to some limit), but doesn't that just mean that 
>> instead of say a 1 second outage every minute, it's a 10 second outage 
>> every 10 minutes?
> In recent PG versions you can spread the checkpoint I/O out over a
> period of time, so it shouldn't be an "outage" at all, just background
> load.  Other things being equal, a longer checkpoint cycle is better
> since it improves the odds of being able to coalesce multiple changes
> to the same page into a single write.  The limiting factor is your
> threshold of pain on how much WAL-replay work would be needed to recover
> after a crash.

That makes sense. I think that 64 is sane - it means crash-recovery 
takes less than 1 minute, yet we aren't seeing the warning that 
checkpoints are too frequent.
>> Is it possible (or even sensible) to do a manual vacuum analyze with 
>> nice/ionice?
> There's no support for that in PG.  You could try manually renice'ing
> the backend that's running your VACUUM but I'm not sure how well it
> would work; there are a number of reasons why it might be
> counterproductive.  Fooling with the vacuum_cost_delay parameters is the
> recommended way to make a vacuum run slower and use less of the machine.

I see why it might not work well - priority inversion etc.

What I was trying to achieve is to say that vacuum can have all the 
spare idle CPU/IO that's available, but must *immediately* back off when 
something else needs the CPU/IO/Locks.

For example,
   nice -n 20 yes > /dev/null
   ionice -c 3 dd if=/dev/zero > tmp.del

will both get quite a lot of work done on a medium-loaded system (try 
this on your own laptop), but have zero impact on other processes.

On the other hand, changing vacuum_cost_delay means that vacuum runs 
slowly even if the CPU is otherwise idle; yet it still impacts on the 
responsiveness of some queries.


In response to

pgsql-performance by date

Next:From: Bruce MomjianDate: 2009-11-29 13:05:31
Subject: Re: OpenMP in PostgreSQL-8.4.0
Previous:From: Greg SmithDate: 2009-11-28 22:28:39
Subject: Re: Analyse without locking?

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