Обсуждение: Auto VACUUM

#track_activities = on
track_counts = on
#track_functions = none                 # none, pl, all
#track_activity_query_size = 1024
#update_process_title = on
#stats_temp_directory = 'pg_stat_tmp'

#------------------------------------------------------------------------------
# AUTOVACUUM PARAMETERS
#------------------------------------------------------------------------------

autovacuum = on                 # Enable autovacuum subprocess?  'on'
                                        # requires track_counts to also be on.
#log_autovacuum_min_duration = -1       # -1 disables, 0 logs all actions and
                                        # their durations, > 0 logs only
                                        # actions running at least this number
                                        # of milliseconds.
autovacuum_max_workers = 10             # max number of autovacuum subprocesses
autovacuum_naptime = 180min             # 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 = 20ms    # default vacuum cost delay for
                                        # autovacuum, in milliseconds;
                                        # -1 means use vacuum_cost_delay
#autovacuum_vacuum_cost_limit = -1      # default vacuum cost limit for
                                        # autovacuum, -1 means use
                                        # vacuum_cost_limit

On Wed, 2010-03-03 at 12:46 -0500, akp geek wrote:
> Hi All -
>
>           I need some help from you. this question is in follow up
> with my earlier questions. I turned the autovacuum and restarted the
> db and the settings I have as follows. It seems the autovacuum process
> has not been turned on. It's almost more than 3 hours I have restarted
> my DB with following setting.  I have ps -ef to see the proces list.
> Is there some thing I am doing wrong.
>
> Can you please help?

I'dd suggest leaving the "naptime" in the default (60 seconds)

Your value is very high... too high... I'dd say....

Use values around 60 seconds (never minutes)...

>
> Regards
>
> # - Query/Index Statistics Collector -
> #track_activities = on
> track_counts = on
> #track_functions = none                 # none, pl, all
> #track_activity_query_size = 1024
> #update_process_title = on
> #stats_temp_directory = 'pg_stat_tmp'
>
>
> #------------------------------------------------------------------------------
> # AUTOVACUUM PARAMETERS
> #------------------------------------------------------------------------------
>
> autovacuum = on                 # Enable autovacuum subprocess?  'on'
>                                         # requires track_counts to
> also be on.
> #log_autovacuum_min_duration = -1       # -1 disables, 0 logs all
> actions and
>                                         # their durations, > 0 logs
> only
>                                         # actions running at least
> this number
>                                         # of milliseconds.
> autovacuum_max_workers = 10             # max number of autovacuum
> subprocesses
> autovacuum_naptime = 180min             # 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 = 20ms    # default vacuum cost delay
> for
>                                         # autovacuum, in milliseconds;
>                                         # -1 means use
> vacuum_cost_delay
> #autovacuum_vacuum_cost_limit = -1      # default vacuum cost limit
> for
>                                         # autovacuum, -1 means use
>                                         # vacuum_cost_limit
>
>

What's the complete error message?
Vacuum is using maintenance_work_mem.  What is your setting
maintenance_work_mem compared to your RAM size.

Igor Neyman

> -----Original Message-----
> From: akp geek [mailto:akpgeek@gmail.com]
> Sent: Wednesday, March 03, 2010 1:10 PM
> To: Joao Ferreira gmail
> Cc: pgsql-general
> Subject: Re: Auto VACUUM
>
> thank you . I changed the value to 1M and I started seeing
> the autovacuum being triggered. But I am getting the
> following message
>
>  ERROR:  canceling autovacuum task, is it because the table
> are getting updated and the vacuum process in place and
> vacuum happens at a later point of time
>
> Regards
>
>
>
> On Wed, Mar 3, 2010 at 1:02 PM, Joao Ferreira gmail
> <joao.miguel.c.ferreira@gmail.com> wrote:
>
>
>     On Wed, 2010-03-03 at 12:46 -0500, akp geek wrote:
>     > Hi All -
>     >
>     >           I need some help from you. this question is
> in follow up
>     > with my earlier questions. I turned the autovacuum
> and restarted the
>     > db and the settings I have as follows. It seems the
> autovacuum process
>     > has not been turned on. It's almost more than 3 hours
> I have restarted
>     > my DB with following setting.  I have ps -ef to see
> the proces list.
>     > Is there some thing I am doing wrong.
>     >
>     > Can you please help?
>
>
>     I'dd suggest leaving the "naptime" in the default (60 seconds)
>
>     Your value is very high... too high... I'dd say....
>
>     Use values around 60 seconds (never minutes)...
>
>
>     >
>     > Regards
>     >
>     > # - Query/Index Statistics Collector -
>     > #track_activities = on
>     > track_counts = on
>     > #track_functions = none                 # none, pl, all
>     > #track_activity_query_size = 1024
>     > #update_process_title = on
>     > #stats_temp_directory = 'pg_stat_tmp'
>     >
>     >
>     >
> #-------------------------------------------------------------
> -----------------
>     > # AUTOVACUUM PARAMETERS
>     >
> #-------------------------------------------------------------
> -----------------
>     >
>     > autovacuum = on                 # Enable autovacuum
> subprocess?  'on'
>     >                                         # requires
> track_counts to
>     > also be on.
>     > #log_autovacuum_min_duration = -1       # -1
> disables, 0 logs all
>     > actions and
>     >                                         # their
> durations, > 0 logs
>     > only
>     >                                         # actions
> running at least
>     > this number
>     >                                         # of milliseconds.
>     > autovacuum_max_workers = 10             # max number
> of autovacuum
>     > subprocesses
>     > autovacuum_naptime = 180min             # 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 = 20ms    # default
> vacuum cost delay
>     > for
>     >                                         # autovacuum,
> in milliseconds;
>     >                                         # -1 means use
>     > vacuum_cost_delay
>     > #autovacuum_vacuum_cost_limit = -1      # default
> vacuum cost limit
>     > for
>     >                                         # autovacuum,
> -1 means use
>     >                                         # vacuum_cost_limit
>     >
>     >
>
>
>
>     --
>     Sent via pgsql-general mailing list
> (pgsql-general@postgresql.org)
>     To make changes to your subscription:
>     http://www.postgresql.org/mailpref/pgsql-general
>
>
>
>

On Thu, 2010-03-04 at 11:04 -0500, akp geek wrote:
> My RAM size is 8GB. I have set the maintenance_work_mem = 500MB this
> morning.
>
> ERROR:  canceling autovacuum task  with table name
>
> Thanks for the help

You likely have a lock that is conflicting with autovacuum and it
cancels itself to not conflict.

Joshua D. Drake



--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering
Respect is earned, not gained through arbitrary and repetitive use or Mr. or Sir.

On Thu, 2010-03-04 at 11:04 -0500, akp geek wrote:
> My RAM size is 8GB. I have set the maintenance_work_mem = 500MB this
> morning.
>
> ERROR:  canceling autovacuum task  with table name
>
> Thanks for the help

You likely have a lock that is conflicting with autovacuum and it
cancels itself to not conflict.

Joshua D. Drake



--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering
Respect is earned, not gained through arbitrary and repetitive use or Mr. or Sir.

On Fri, 2010-03-05 at 10:03 -0500, akp geek wrote:
> Hi All -
>
>              I am still having the issue, even after I turned on the
> auto vaccum. I have quick question. How do I know that auto vacuum
> process is running. When I restarted my database , I got the message
> auto vacuum launcher started. But is there a way that I can check that
> the process is really working.
>

u can "tail -f <postgres-log-file>"

in my case I have "tail -f /var/pgsql/data/logfile"

in your case it could be diferent

watch for lines containing the words "vacuum", "autovacuum", "will
analyse" etc etc

Joao

>