Обсуждение: Configuring autovacuum for the first time...

Hello,

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”.

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

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

Thank you for your answers and advice,

Gail

postgresql.conf contents:

#------------------------------------------------------------------------------

# RUNTIME STATISTICS

#------------------------------------------------------------------------------

# - Query/Index Statistics Collector -

#track_activities = on

track_counts = on

#update_process_title = on

# - Statistics Monitoring -

#log_parser_stats = off

#log_planner_stats = off

#log_executor_stats = off

#log_statement_stats = off

#------------------------------------------------------------------------------

# AUTOVACUUM PARAMETERS

#------------------------------------------------------------------------------

autovacuum = on                                          # Enable autovacuum subprocess?  'on'

                                                            # requires track_counts to also be on.

log_autovacuum_min_duration = 0                     # -1 disables, 0 logs all actions and

                                                            # their durations, > 0 logs only

                                                            # actions running at least that time.

autovacuum_max_workers = 3               # max number of autovacuum subprocesses

autovacuum_naptime = 1min                  # time between autovacuum runs

autovacuum_vacuum_threshold = 50      # min number of row updates before

                                                            # vacuum

autovacuum_analyze_threshold = 50      # min number of row updates before

                                                            # analyze

autovacuum_vacuum_scale_factor = 0.2            # fraction of table size before vacuum

autovacuum_analyze_scale_factor = 0.1 # fraction of table size before analyze

autovacuum_freeze_max_age = 200000000        # maximum XID age before forced vacuum

                                                            # (change requires restart)

autovacuum_vacuum_cost_delay = 20   # default vacuum cost delay for

                                                            # autovacuum, -1 means use

                                                            # vacuum_cost_delay

autovacuum_vacuum_cost_limit = -1      # default vacuum cost limit for

                                                            # autovacuum, -1 means use

                                                            # vacuum_cost_limit