Skip site navigation (1) Skip section navigation (2)

Re: Configuring autovacuum for the first time...

From: ANdreas Wenk <a(dot)wenk(at)netzmeister-st-pauli(dot)de>
To: "Nagle, Gail A \(US SSA\)" <gail(dot)nagle(at)baesystems(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Configuring autovacuum for the first time...
Date: 2009-07-03 20:47:25
Message-ID: 4A4E6E5D.2070004@netzmeister-st-pauli.de (view raw or flat)
Thread:
Lists: pgsql-novice
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

In response to

pgsql-novice by date

Next:From: Doug GrahamDate: 2009-07-03 21:59:53
Subject: Re: Setting shmmax in /etc/rc on Mac OS X to install Postgres
Previous:From: Tom LaneDate: 2009-07-03 20:40:16
Subject: Re: Setting shmmax in /etc/rc on Mac OS X to install Postgres

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group