Re:

On Sun, Apr 21, 2013 at 6:46 AM, sunil virmani <sunhcl(at)gmail(dot)com> wrote:
> Hi,
>
> My databases are updated regularly so I am vacuuming frequently (every one
> hour). Recently i also added template1 database to avoid over wrapping
> problem. But somehow i am seeing strange behavior.
>
> Most of the time all db vacuuming finish in 30 secs.
>
> but once in a day or two
> - My actual DB is taking less than 30 secs for vacuuming.
> - Sometime template1 is taking 5 mins for vacuuming.
> - Queries become exceptionally slow at that time for 5 mins ( specially
> during the end).
>
> I am wondering what could be the reason of long time of template1 vacumming
> sometime and slow query at end of vacumming.
>
> Do we need to template1 analyze regularly? What is ideal frequency of
> template1 vacuuming only and analyze?
>
> My DB version is little old - 8.1.18.

Well upgrade as soon as possible. 9.1 is pretty darn stable.

There are two possible things that cause this kind of slowdown. One is
a checkpoint. This is where postgresql writes out its own dirty
buffers, and the other is a back OS level write flush. Both of these
will cause your system to slow to a crawl. The fix for checkpointing
is to adjust your postgresql.conf file's completion target and other
settings, many of which, like completion target, do not exist in 8.1.
Increasing checkpoint segments and checkpoint timeouts may help here.

Depending on your OS you may or may not be able to reduce the two
dirty*ratio settings, vm.dirty_background_ratio and vm.dirty_ratio. On
many servers reducing these to 0 or something under 5 is a good first
step. In almost no circumstance is a high setting good for large
memory, database, or file server machines.

Another possibility is that your kswap daemon is going nuts and
swapping for no reason. Turning off swap can stop it. You'll see lots
of so/si in iostat when that's happening, but no real reason for it.
(i.e. no memory pressure, plenty free memory etc)

I'm gonna just assume since you're running an old postgres you're
probably not on more modern numa hardware and don't have an issue with
zone_reclaim_mode = 1 that I've seen before.