From: Kiriakos Tsourapas <ktsour@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: Postgres becoming slow, only full vacuum fixes it
Date: Wed, 26 Sep 2012 16:50:29 +0300
Message-ID: <34C83EA2-7004-4D37-9674-ADFB4A475C07@gmail.com> (raw)
In-Reply-To: <20120926113953.221020@gmx.com>
References: <20120926113953.221020@gmx.com>
Hi Kevin,
On Sep 26, 2012, at 14:39, Kevin Grittner wrote:
>
> I am concerned that your initial email said that you had this
> setting:
>
> autovacuum_naptime = 28800
>
> This is much too high for most purposes; small, frequently-modified
> tables won't be kept in good shape with this setting. Perhaps you're
> not having that problem at the moment, but it's risky to assume that
> you don't and never will. When autovacuum wakes up and there is
> nothing to do it should go back to sleep very quickly.
>
I used the 28800 (8hours) setting after I realized that the default 1min was not helping.
I also changed other parameters when I changed it to 8 hours, to make sure tables would be auto vacuumed.
The problem with my setting was that autovacuum gets stopped if a lock is needed on the table. So, it was very bad choice to run it every 8 hours, because usually it got stopped and never did anything.
So, I turned back to the original setting of 1min but changed the autovacuum_vacuum_scale_factor to 1% instead of 20%. Hopefully tables will be more frequently vacuumed now and the problem will not appear again.
> Don't expect too much from just making autovacuum run more often
> until you have eliminated existing bloat (autovacuum generally just
> limits further growth of bloat) and updated to the latest 8.4 minor
> release. The following bugs fixes are among many you are living
> without until you upgrade:
Can you please suggest of a way to
- find if there is existing bloat
- eliminate it
Thank you=
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: ktsour@gmail.com
Subject: Re: Postgres becoming slow, only full vacuum fixes it
In-Reply-To: <34C83EA2-7004-4D37-9674-ADFB4A475C07@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox