Re: Auto VACUUM

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?

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

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

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(dot)miguel(dot)c(dot)ferreira(at)gmail(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

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(at)gmail(dot)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(dot)miguel(dot)c(dot)ferreira(at)gmail(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
>
>
>

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

Regards

On Wed, Mar 3, 2010 at 4:31 PM, Igor Neyman <ineyman(at)perceptron(dot)com> wrote:

> 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(at)gmail(dot)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(dot)miguel(dot)c(dot)ferreira(at)gmail(dot)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(at)postgresql(dot)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.

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.

I have not been getting any error messages like the one I used
to get
ERROR: canceling autovacuum task with table name

Can you please share your thoughts?

Regards

On Thu, Mar 4, 2010 at 11:34 AM, Joshua D. Drake <jd(at)commandprompt(dot)com>wrote:

> 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

>

Thanks for the help. Will do that.

Regards

On Fri, Mar 5, 2010 at 10:35 AM, Joao Ferreira gmail <
joao(dot)miguel(dot)c(dot)ferreira(at)gmail(dot)com> wrote:

> 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
>
> >
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>