Re: Configuring autovacuum for the first time...
От | ANdreas Wenk |
---|---|
Тема | Re: Configuring autovacuum for the first time... |
Дата | |
Msg-id | 4A4E6E5D.2070004@netzmeister-st-pauli.de обсуждение исходный текст |
Ответ на | Configuring autovacuum for the first time... ("Nagle, Gail A \(US SSA\)" <gail.nagle@baesystems.com>) |
Список | 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
В списке pgsql-novice по дате отправления:
Предыдущее
От: Tom LaneДата:
Сообщение: Re: Setting shmmax in /etc/rc on Mac OS X to install Postgres
Следующее
От: Doug GrahamДата:
Сообщение: Re: Setting shmmax in /etc/rc on Mac OS X to install Postgres