Thread: Vacuuming
There's a script running on my server hosting a PostgreSQL database that does some type of vacuuming routine every
Fridayat 5:00 p.m. Specifically, the script executes this command:
psql -d database_name -c "vacuum full verbose"
and e-mails the verbose output to me. Today's report today contains about 900,000 characters.
I don't event know where to begin reading that report, interpreting what it's telling me, determining what's important,
orascertaining what's routing and unimportant.
Can anybody give me any guidance or point me to a document that I should read to understand what the PostgreSQL vacuum
doesand why this is an important function and how to decipher the verbose output?
On Fri, Feb 12, 2010 at 7:32 PM, <peter@vfemail.net> wrote: > > There's a script running on my server hosting a PostgreSQL database that does some type of vacuuming routine every Fridayat 5:00 p.m. Specifically, the script executes this command: > > psql -d database_name -c "vacuum full verbose" > > and e-mails the verbose output to me. Today's report today contains about 900,000 characters. > > I don't event know where to begin reading that report, interpreting what it's telling me, determining what's important,or ascertaining what's routing and unimportant. > > Can anybody give me any guidance or point me to a document that I should read to understand what the PostgreSQL vacuumdoes and why this is an important function and how to decipher the verbose output? > You should never run VACUUM FULL on a regular basis. In fact, it's recommended never to use FULL. See: http://wiki.postgresql.org/wiki/VACUUM_FULL
APseudoUtopia wrote: > On Fri, Feb 12, 2010 at 7:32 PM, <peter@vfemail.net> wrote: >> There's a script running on my server hosting a PostgreSQL database that does some type of vacuuming routine every Fridayat 5:00 p.m. Specifically, the script executes this command: >> >> psql -d database_name -c "vacuum full verbose" >> >> and e-mails the verbose output to me. Today's report today contains about 900,000 characters. >> >> I don't event know where to begin reading that report, interpreting what it's telling me, determining what's important,or ascertaining what's routing and unimportant. >> >> Can anybody give me any guidance or point me to a document that I should read to understand what the PostgreSQL vacuumdoes and why this is an important function and how to decipher the verbose output? >> > > You should never run VACUUM FULL on a regular basis. In fact, it's > recommended never to use FULL. > See: http://wiki.postgresql.org/wiki/VACUUM_FULL > I can't seem to find how to start autovacuum daemon? Once running how can I monitor its effect?
In postgresql.conf parameter file, there should be the following section:
#------------------------------------------------------------------------------
# AUTOVACUUM PARAMETERS
#------------------------------------------------------------------------------
autovacuum = on # Enable autovacuum subprocess? 'on'
# requires track_counts to also be on.
log_autovacuum_min_duration = 10000 # -1 disables, 0 logs all actions and
# their durations, > 0 logs only
# actions running at least this number
# of milliseconds.
autovacuum_max_workers = 5 # 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
On Sat, 2010-02-13 at 12:53 -0500, Frank Bax wrote:
#------------------------------------------------------------------------------
# AUTOVACUUM PARAMETERS
#------------------------------------------------------------------------------
autovacuum = on # Enable autovacuum subprocess? 'on'
# requires track_counts to also be on.
log_autovacuum_min_duration = 10000 # -1 disables, 0 logs all actions and
# their durations, > 0 logs only
# actions running at least this number
# of milliseconds.
autovacuum_max_workers = 5 # 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
On Sat, 2010-02-13 at 12:53 -0500, Frank Bax wrote:
APseudoUtopia wrote: > On Fri, Feb 12, 2010 at 7:32 PM, <peter@vfemail.net> wrote: >> There's a script running on my server hosting a PostgreSQL database that does some type of vacuuming routine every Friday at 5:00 p.m. Specifically, the script executes this command: >> >> psql -d database_name -c "vacuum full verbose" >> >> and e-mails the verbose output to me. Today's report today contains about 900,000 characters. >> >> I don't event know where to begin reading that report, interpreting what it's telling me, determining what's important, or ascertaining what's routing and unimportant. >> >> Can anybody give me any guidance or point me to a document that I should read to understand what the PostgreSQL vacuum does and why this is an important function and how to decipher the verbose output? >> > > You should never run VACUUM FULL on a regular basis. In fact, it's > recommended never to use FULL. > See: http://wiki.postgresql.org/wiki/VACUUM_FULL > I can't seem to find how to start autovacuum daemon? Once running how can I monitor its effect?
-- Mladen Gogala Sr. Oracle DBA 1500 Broadway New York, NY 10036 (212) 329-5251 www.vmsinfo.com |
Mladen Gogala wrote:
> In postgresql.conf parameter file, there should be the following section:
>
> autovacuum = on # Enable autovacuum subprocess? 'on'
> # requires track_counts to also be on.
> log_autovacuum_min_duration = 10000 # -1 disables, 0 logs all
> actions and
> # their durations, > 0 logs only
> # actions running at least this
> number
> # of milliseconds.
> autovacuum_max_workers = 5 # 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
I start with:
autovacuum = on # Enable autovacuum subprocess? 'on'
# requires track_counts to also be on.
log_autovacuum_min_duration = 0 # -1 disables, 0 logs all
autovacuum_max_workers = 3 # max number of autovacuum subprocesses
autovacuum_naptime = 60min # time between autovacuum runs
Then stop/start postmaster...
2010-02-13 17:36:35 EST LOG: received fast shutdown request
2010-02-13 17:36:35 EST LOG: aborting any active transactions
2010-02-13 17:36:35 EST LOG: shutting down
2010-02-13 17:36:35 EST LOG: database system is shut down
2010-02-13 17:36:36 EST LOG: database system was shut down at
2010-02-13 17:36:35 EST
2010-02-13 17:36:36 EST LOG: database system is ready to accept
connections
2010-02-13 17:36:36 EST LOG: autovacuum launcher started
It's now 19:06 and no autovacuum; why not?
On Sat, 2010-02-13 at 19:08 -0500, Frank Bax wrote: > It's now 19:06 and no autovacuum; why not? ...it is because of the remaining autovac settings. Autovac determines the relations to be vacuumed based on the scale_factor and threshold settings. Enabling autovacuum does not necessarily mean that it will vacuum tables. See: http://www.postgresql.org/docs/current/static/routine-vacuuming.html#AUTOVACUUM. -- Devrim GÜNDÜZ, RHCE PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz