Обсуждение:

On Sun, Apr 21, 2013 at 6:46 AM, sunil virmani <sunhcl@gmail.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.

I was gonna tell you to turn off full page writes, but in 8.1 that
setting is ignored.  For reference, here's the pages that you should
look at for this:

http://www.postgresql.org/docs/8.1/static/runtime-config-wal.html
http://www.postgresql.org/docs/8.1/static/wal-configuration.html

For 9.1, the same pages:

http://www.postgresql.org/docs/9.1/static/runtime-config-wal.html
http://www.postgresql.org/docs/9.1/static/wal-configuration.html

For future reference, if you want to learn more about performance
tuning postgresql, Performance PostgreSQL by Greg Smith is THE book to
have.

W dniu 2013-04-21 19:28, Scott Marlowe pisze:
>> My DB version is little old - 8.1.18.
> Well upgrade as soon as possible. 9.1 is pretty darn stable.

Scott,

excuse me this somewhat off-topic question.

Good to hear that 9.1 is so stable, because this is what I currently use in production. But why I
still use it, is only because I failed to manage my task to migrate to 9.2, so far. Anyway, this
task in on my long-term agenda.

So, let me understand why, as you recommend the OP upgrading, you only mention the 9.1, while there
have already been a few releases of 9.2. Isn't 9.2 stable enough?


Best regards
Irek.