pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedAutovacuum does not stay turned off
6+ messages / 3 participants
[nested] [flat]
* Autovacuum does not stay turned off
@ 2008-08-26 15:27 Jerry Champlin <jchamplin@absolute-performance.com>
0 siblings, 2 replies; 6+ messages in thread
From: Jerry Champlin @ 2008-08-26 15:27 UTC (permalink / raw)
To: pgsql-performance
We are attempting to turn off autovacuum but it keeps coming back. We
can't afford the performance hit from vacuum while end users are
accessing our system.
Postgresql Version: 8.3.3
OS: Linux 2.6.18-53.el5PAE #1 SMP
Running PostgreSQL setting:
sspg=# show autovacuum;
autovacuum
------------
off
(1 row)
pg.log Log Entries:
2008-08-26 15:24:50 GMTLOG: autovacuum launcher started
-- and then we manually kill it
postgres 32371 0.0 0.1 1133768 23572 ? Ss 15:16 0:00
postgres: autovacuum worker process rollup_data_store
# kill 32371
2008-08-26 15:24:53 GMTFATAL: terminating autovacuum process due to
administrator command
Does anyone know what will cause this bahavior for autovacuum?
Thank you in advance
-Jerry
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Autovacuum does not stay turned off
@ 2008-08-26 16:14 hubert depesz lubaczewski <depesz@depesz.com>
parent: Jerry Champlin <jchamplin@absolute-performance.com>
1 sibling, 1 reply; 6+ messages in thread
From: hubert depesz lubaczewski @ 2008-08-26 16:14 UTC (permalink / raw)
To: Jerry Champlin <jchamplin@absolute-performance.com>; +Cc: pgsql-performance
On Tue, Aug 26, 2008 at 09:27:48AM -0600, Jerry Champlin wrote:
> Does anyone know what will cause this bahavior for autovacuum?
http://www.postgresql.org/docs/current/interactive/runtime-config-autovacuum.html
-> autovacuum_freeze_max_age
depesz
--
Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/
jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Autovacuum does not stay turned off
@ 2008-08-26 16:21 Andrew Sullivan <ajs@commandprompt.com>
parent: Jerry Champlin <jchamplin@absolute-performance.com>
1 sibling, 0 replies; 6+ messages in thread
From: Andrew Sullivan @ 2008-08-26 16:21 UTC (permalink / raw)
To: pgsql-performance
On Tue, Aug 26, 2008 at 09:27:48AM -0600, Jerry Champlin wrote:
>
> Does anyone know what will cause this bahavior for autovacuum?
You're probably approaching the wraparound limit in some database.
If you think you can't afford the overhead when users are accessing
the system, when are you vacuuming?
A
--
Andrew Sullivan
ajs@commandprompt.com
+1 503 667 4564 x104
http://www.commandprompt.com/
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Autovacuum does not stay turned off
@ 2008-08-26 16:45 Jerry Champlin <jchamplin@absolute-performance.com>
parent: hubert depesz lubaczewski <depesz@depesz.com>
0 siblings, 1 reply; 6+ messages in thread
From: Jerry Champlin @ 2008-08-26 16:45 UTC (permalink / raw)
To: depesz@depesz.com; +Cc: pgsql-performance@postgresql.org, Andrew Sullivan <ajs@commandprompt.com>
This makes sense. What queries can I run to see how close to the limit
we are? We need to determine if we should stop the process which
updates and inserts into this table until after the critical time this
afternoon when we can perform the required maintenance on this table.
hubert depesz lubaczewski wrote:
> On Tue, Aug 26, 2008 at 09:27:48AM -0600, Jerry Champlin wrote:
>
>> Does anyone know what will cause this bahavior for autovacuum?
>>
>
> http://www.postgresql.org/docs/current/interactive/runtime-config-autovacuum.html
> -> autovacuum_freeze_max_age
>
> depesz
>
>
Andrew Sullivan wrote:
> On Tue, Aug 26, 2008 at 09:27:48AM -0600, Jerry Champlin wrote:
>
>> Does anyone know what will cause this bahavior for autovacuum?
>>
>
> You're probably approaching the wraparound limit in some database.
>
> If you think you can't afford the overhead when users are accessing
> the system, when are you vacuuming?
>
> A
>
>
We are changing the table structure tonight. These two tables are very
highly updated. The goal is to use autovacuum but not have it take 10
days to run on a 13MM record table.
Thanks
-Jerry
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Autovacuum does not stay turned off
@ 2008-08-26 17:04 hubert depesz lubaczewski <depesz@depesz.com>
parent: Jerry Champlin <jchamplin@absolute-performance.com>
0 siblings, 1 reply; 6+ messages in thread
From: hubert depesz lubaczewski @ 2008-08-26 17:04 UTC (permalink / raw)
To: Jerry Champlin <jchamplin@absolute-performance.com>; +Cc: pgsql-performance@postgresql.org, Andrew Sullivan <ajs@commandprompt.com>
On Tue, Aug 26, 2008 at 10:45:31AM -0600, Jerry Champlin wrote:
> This makes sense. What queries can I run to see how close to the limit
> we are? We need to determine if we should stop the process which
> updates and inserts into this table until after the critical time this
> afternoon when we can perform the required maintenance on this table.
select datname, age(datfrozenxid) from pg_database;
Best regards,
depesz
--
Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/
jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Autovacuum does not stay turned off
@ 2008-08-26 18:06 Jerry Champlin <jchamplin@absolute-performance.com>
parent: hubert depesz lubaczewski <depesz@depesz.com>
0 siblings, 0 replies; 6+ messages in thread
From: Jerry Champlin @ 2008-08-26 18:06 UTC (permalink / raw)
To: pgsql-performance
Thanks for the help. The applied solution follows. We will be taking a
number of maintenance steps to manage these very high update tables
which I will summarize later as I suspect we are not the only ones with
this challenge.
http://www.postgresql.org/docs/current/interactive/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND
http://www.postgresql.org/docs/current/interactive/catalog-pg-autovacuum.html
data_store=# SELECT relname, oid, age(relfrozenxid) FROM pg_class WHERE
relkind = 'r';
...
hour_summary | 16392 | 252934596
percentile_metadata | 20580 | 264210966
(51 rows)
data_store=# insert into pg_autovacuum values
(16392,false,350000000,2,350000000,1,200,200,350000000,500000000);
INSERT 0 1
data_store=# insert into pg_autovacuum values
(20580,false,350000000,2,350000000,1,200,200,350000000,500000000);
INSERT 0 1
data_store=#
hubert depesz lubaczewski wrote:
> On Tue, Aug 26, 2008 at 10:45:31AM -0600, Jerry Champlin wrote:
>
>> This makes sense. What queries can I run to see how close to the limit
>> we are? We need to determine if we should stop the process which
>> updates and inserts into this table until after the critical time this
>> afternoon when we can perform the required maintenance on this table.
>>
>
> select datname, age(datfrozenxid) from pg_database;
>
> Best regards,
>
> depesz
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2008-08-26 18:06 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-08-26 15:27 Autovacuum does not stay turned off Jerry Champlin <jchamplin@absolute-performance.com>
2008-08-26 16:14 ` hubert depesz lubaczewski <depesz@depesz.com>
2008-08-26 16:45 ` Jerry Champlin <jchamplin@absolute-performance.com>
2008-08-26 17:04 ` hubert depesz lubaczewski <depesz@depesz.com>
2008-08-26 18:06 ` Jerry Champlin <jchamplin@absolute-performance.com>
2008-08-26 16:21 ` Andrew Sullivan <ajs@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