Nagle, Gail A (US SSA) wrote:
> 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
postgres 2461 2454 0 21:00 ? 00:00:00 postgres: stats
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
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,
In response to
pgsql-novice by date
|Next:||From: Doug Graham||Date: 2009-07-03 21:59:53|
|Subject: Re: Setting shmmax in /etc/rc on Mac OS X to install Postgres|
|Previous:||From: Tom Lane||Date: 2009-07-03 20:40:16|
|Subject: Re: Setting shmmax in /etc/rc on Mac OS X to install Postgres |