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