Re: Configuring autovacuum for the first time...

Nagle, Gail A (US SSA) wrote:
> Hello,

Hi,

> We are moving from a development environment to a production testing
> environment. We are complete novices!
>
> Clearly, we now need to pay more attention to DB maintenance.
>
> We are running PostGreSQL 8.3 on Windows XP, 32 bit. We currently have a
> small template database with only one trigger and two tables plus postgiis.
>
> We expect to have up to 25 replications of the database and 25 users in
> the future.
>
>
>
> In preparation to use the recommended auto-vacuum capability, we first
> checked that there were no entries in the pg_autovacuum table.
>
> We then used pgAdmin III to manually vaccum each existing database. This
> reduced file system memory use by about 1.5 MB.

> Finally, we stopped the server, edited the postgresql.conf file as shown
> below, and restarted the server.
>
> With only one exception, we took the default values in the
> postgresql.conf file. That exception was for log_autovacuum_min_duration
> which we set to 0.
>
> The log says “autovacuum launcher started”.

This is a reliable information. You can trust this ;-)

> Should we see a particular process running to be sure we have activated
> auto vacuuming correctly by the above actions?

I am not sure for Windows. As an example this is the output for
processes running on Linux:

postgres 2454 1 0 21:00 ? 00:00:00
/var/lib/postgresql/8.4/bin/postgres -D /var/lib/postgresql/8.4/data
postgres 2455 2454 0 21:00 ? 00:00:00 postgres: logger process
postgres 2458 2454 0 21:00 ? 00:00:00 postgres: writer process
postgres 2459 2454 0 21:00 ? 00:00:00 postgres: wal writer process
postgres 2460 2454 0 21:00 ? 00:00:00 postgres: autovacuum
launcher process
postgres 2461 2454 0 21:00 ? 00:00:00 postgres: stats
collector process
postgres 5201 3562 0 21:39 pts/1 00:00:00 su postgres
postgres 5209 5201 0 21:39 pts/1 00:00:00 bash
postgres 7104 5209 0 22:20 pts/1 00:00:00 psql8.4 -E -U postgres
-p 5433
postgres 7106 2454 0 22:20 ? 00:00:00 postgres: postgres
postgres [local] idle

> Assuming the default values a reasonable starting place, how will we
> know if we need to modify these configuration settings in the future?

The postgresql.conf settings are very conservative. You should tune the
settings. A good tart is to use pgtune
(http://pgfoundry.org/projects/pgtune/) pgtune will create an
alternative postgresql.conf based on a given template and based on the
hardware you are using.

Furthermore there are also monitoring solutions like nagios, pgtop, or
pgFouine (http://pgfouine.projects.postgresql.org) And for sure - if
the database is becoming slow while using it you have to analyse why and
maybe change the settings in postgresql.conf. As you allready saw, there
are different parameter for VACUUM. A starting point to check if your
database is still with good performance is to use EXPLAIN and EXPLAIN
ANALYZE in combination with VACUUM.

So the advice is to setup a good monitoring solution.

> Thank you for your answers and advice,
>
> Gail

Cheers

Andy