pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Stray autovacuum daemon
5+ messages / 2 participants
[nested] [flat]

* Stray autovacuum daemon
@ 2008-02-22 11:05  Mikko Partio <mpartio@gmail.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Mikko Partio @ 2008-02-22 11:05 UTC (permalink / raw)
  To: pgsql-admin

Hello list

I have a largish postgresql (8.3) instance with autovacuum turned on. I am
doing some heavy importing and I want to vacuum the relevant tables manually
since autovacuum seems to trigger the vacuum in the middle of the import
process which slows things down.

Now, the problem is that I cannot turn autovacuum off! I have tried to set
autovacuum = off at postgresql.conf with no avail. I have also tried to
disable the vacuum by inserting rows to pg_autovacuum but still the vacuum
processes just pop up. What's even more weird is that autovacuum is
vacuuming the same tables over and over. No sign of xid wraparound issues
show in the logs. How can I stop autovacuum without restarting the database
instance?

Relevant non-default configuration values:

autovacuum = off
log_autovacuum_min_duration = 0
autovacuum_naptime = 1min
autovacuum_vacuum_threshold = 500
autovacuum_analyze_threshold = 500

vacuum_cost_delay = 5

Logs:

2008-02-22 12:59:34 EET [23258]: [2-1] LOG:  automatic vacuum of table
"xxx": index scans: 0
        pages: 0 removed, 15150 remain
        tuples: 0 removed, 2123107 remain
        system usage: CPU 0.01s/0.00u sec elapsed 151.48 sec



Regards

Mikko

^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Stray autovacuum daemon
@ 2008-02-22 15:44  Alvaro Herrera <alvherre@commandprompt.com>
  parent: Mikko Partio <mpartio@gmail.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Alvaro Herrera @ 2008-02-22 15:44 UTC (permalink / raw)
  To: Mikko Partio <mpartio@gmail.com>; +Cc: pgsql-admin

Mikko Partio escribió:

> Now, the problem is that I cannot turn autovacuum off! I have tried to set
> autovacuum = off at postgresql.conf with no avail. I have also tried to
> disable the vacuum by inserting rows to pg_autovacuum but still the vacuum
> processes just pop up. What's even more weird is that autovacuum is
> vacuuming the same tables over and over. No sign of xid wraparound issues
> show in the logs. How can I stop autovacuum without restarting the database
> instance?

If your database is in danger of Xid wraparound, autovacuum will run on
it no matter what you do.  Check pg_class.relfrozenxid and
pg_database.datfrozenxid, and the current Xid counter (from
pg_controldata I think).  Also take a look at the description of the
autovacuum_min_freeze_age param (or was it max_freeze_age?  I forget).

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Stray autovacuum daemon
@ 2008-02-22 17:01  Mikko Partio <mpartio@gmail.com>
  parent: Alvaro Herrera <alvherre@commandprompt.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Mikko Partio @ 2008-02-22 17:01 UTC (permalink / raw)
  To: Alvaro Herrera <alvherre@commandprompt.com>; +Cc: pgsql-admin

On Fri, Feb 22, 2008 at 5:44 PM, Alvaro Herrera <alvherre@commandprompt.com>
wrote:

> Mikko Partio escribió:
>
> > Now, the problem is that I cannot turn autovacuum off! I have tried to
> set
> > autovacuum = off at postgresql.conf with no avail. I have also tried to
> > disable the vacuum by inserting rows to pg_autovacuum but still the
> vacuum
> > processes just pop up. What's even more weird is that autovacuum is
> > vacuuming the same tables over and over. No sign of xid wraparound
> issues
> > show in the logs. How can I stop autovacuum without restarting the
> database
> > instance?
>
> If your database is in danger of Xid wraparound, autovacuum will run on
> it no matter what you do.  Check pg_class.relfrozenxid and
> pg_database.datfrozenxid, and the current Xid counter (from
> pg_controldata I think).  Also take a look at the description of the
> autovacuum_min_freeze_age param (or was it max_freeze_age?  I forget).
>


But I thought vacuum would send some messages to log if xid wraparound is
looming?

SELECT relname, age(relfrozenxid) FROM pg_class WHERE relkind = 'r' order by
2 desc limit 4;
    relname    |    age
---------------+-----------
 table1 | 2141707556
 table2 | 2141707556
 table3  | 2141707556
 table4  | 2141707556


SELECT datname, age(datfrozenxid) FROM pg_database;
  datname  |    age
-----------+------------
 template1 | 1833384067
 template0 | 1833384065
 postgres  | 1933384063
 mydb     | 2040873036

Seems ok to me ... ? autovacuum_freeze_max_age and vacuum_freeze_min_age
have the default value.

Regards

Mikko

^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Stray autovacuum daemon
@ 2008-02-22 18:15  Mikko Partio <mpartio@gmail.com>
  parent: Mikko Partio <mpartio@gmail.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Mikko Partio @ 2008-02-22 18:15 UTC (permalink / raw)
  To: Alvaro Herrera <alvherre@commandprompt.com>; +Cc: pgsql-admin

On Fri, Feb 22, 2008 at 7:01 PM, Mikko Partio <mpartio@gmail.com> wrote:

>
>
> On Fri, Feb 22, 2008 at 5:44 PM, Alvaro Herrera <
> alvherre@commandprompt.com> wrote:
>
> > Mikko Partio escribió:
> >
> > > Now, the problem is that I cannot turn autovacuum off! I have tried to
> > set
> > > autovacuum = off at postgresql.conf with no avail. I have also tried
> > to
> > > disable the vacuum by inserting rows to pg_autovacuum but still the
> > vacuum
> > > processes just pop up. What's even more weird is that autovacuum is
> > > vacuuming the same tables over and over. No sign of xid wraparound
> > issues
> > > show in the logs. How can I stop autovacuum without restarting the
> > database
> > > instance?
> >
> > If your database is in danger of Xid wraparound, autovacuum will run on
> > it no matter what you do.  Check pg_class.relfrozenxid and
> > pg_database.datfrozenxid, and the current Xid counter (from
> > pg_controldata I think).  Also take a look at the description of the
> > autovacuum_min_freeze_age param (or was it max_freeze_age?  I forget).
> >
>
>

I think I may have found the problem, the import uses a temporary table
which I guess autovacuum can not handle? Manually vacuuming it changed the
relfrozenxids of other tables dramatically.

Regards

Mikko

^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Stray autovacuum daemon
@ 2008-02-22 18:27  Alvaro Herrera <alvherre@commandprompt.com>
  parent: Mikko Partio <mpartio@gmail.com>
  0 siblings, 0 replies; 5+ messages in thread

From: Alvaro Herrera @ 2008-02-22 18:27 UTC (permalink / raw)
  To: Mikko Partio <mpartio@gmail.com>; +Cc: pgsql-admin

Mikko Partio escribió:

> I think I may have found the problem, the import uses a temporary table
> which I guess autovacuum can not handle? Manually vacuuming it changed the
> relfrozenxids of other tables dramatically.

Ah, yes -- that would be it.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.



^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2008-02-22 18:27 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-02-22 11:05 Stray autovacuum daemon Mikko Partio <mpartio@gmail.com>
2008-02-22 15:44 ` Alvaro Herrera <alvherre@commandprompt.com>
2008-02-22 17:01   ` Mikko Partio <mpartio@gmail.com>
2008-02-22 18:15     ` Mikko Partio <mpartio@gmail.com>
2008-02-22 18:27       ` Alvaro Herrera <alvherre@commandprompt.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox