pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedautovacuum
29+ messages / 20 participants
[nested] [flat]
* autovacuum
@ 2006-01-23 05:07 Enzo Daddario <enzo@pienetworks.com>
2006-01-26 14:41 ` Re: autovacuum Matthew T. O'Connor <matthew@zeut.net>
0 siblings, 1 reply; 29+ messages in thread
From: Enzo Daddario @ 2006-01-23 05:07 UTC (permalink / raw)
To: pgsql-admin
Hi All,
I am concerned with the impact autovacuum of table(s) would have on
regular DB activity.
With our current DB's the majority of tables have either a low number of
updates or a large number of inserts (which I believe should not be a
problem), however, a small number of tables have an extremely high
number of updates (up to 150 000)
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: autovacuum
2006-01-23 05:07 autovacuum Enzo Daddario <enzo@pienetworks.com>
@ 2006-01-26 14:41 ` Matthew T. O'Connor <matthew@zeut.net>
2006-02-01 17:14 ` Re: autovacuum Chris Browne <cbbrowne@acm.org>
2006-02-01 20:50 ` Re: autovacuum Chris Browne <cbbrowne@acm.org>
0 siblings, 2 replies; 29+ messages in thread
From: Matthew T. O'Connor @ 2006-01-26 14:41 UTC (permalink / raw)
To: Enzo Daddario <enzo@pienetworks.com>; +Cc: pgsql-admin
Legit concern. However one of the things that autovacuum is supposed to
do is not vacuum tables that don't need it. This can result in an overal
reduction in vacuum overhead. In addition, if you see that autovacuum is
firing off vacuum commands during the day and they are impacting your
response time, then you can play with the vacuum cost delay settings that
are design to throttle down the IO impact vacuum commands can have. In
addition if you use 8.1, you can set per table thresholds, per table
vacuum cost delay settings, and autovacuum will respect the work done by
non-autovacuum vacuum commands. Meaning that if you manually vacuum
tables at night during a maintenance window, autovacuum will take that
into account. Contrib autovacuum couldn't do this.
Hope that helps. Real world feed-back is always welcome.
Matt
> I am concerned with the impact autovacuum of table(s) would have on
> regular DB activity.
>
> With our current DB's the majority of tables have either a low number of
> updates or a large number of inserts (which I believe should not be a
> problem), however, a small number of tables have an extremely high
> number of updates (up to 150 000)
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: autovacuum
2006-01-23 05:07 autovacuum Enzo Daddario <enzo@pienetworks.com>
2006-01-26 14:41 ` Re: autovacuum Matthew T. O'Connor <matthew@zeut.net>
@ 2006-02-01 17:14 ` Chris Browne <cbbrowne@acm.org>
2006-02-01 17:29 ` Re: [HACKERS] autovacuum Alvaro Herrera <alvherre@alvh.no-ip.org>
1 sibling, 1 reply; 29+ messages in thread
From: Chris Browne @ 2006-02-01 17:14 UTC (permalink / raw)
To: pgsql-admin@postgresql.org.pgsql-hackers
matthew@zeut.net ("Matthew T. O'Connor") writes:
> Legit concern. However one of the things that autovacuum is supposed to
> do is not vacuum tables that don't need it. This can result in an overal
> reduction in vacuum overhead. In addition, if you see that autovacuum is
> firing off vacuum commands during the day and they are impacting your
> response time, then you can play with the vacuum cost delay settings that
> are design to throttle down the IO impact vacuum commands can have. In
> addition if you use 8.1, you can set per table thresholds, per table
> vacuum cost delay settings, and autovacuum will respect the work done by
> non-autovacuum vacuum commands. Meaning that if you manually vacuum
> tables at night during a maintenance window, autovacuum will take that
> into account. Contrib autovacuum couldn't do this.
>
> Hope that helps. Real world feed-back is always welcome.
I have a question/suggestion...
Something we found useful with Slony-I was the notion of checking the
eldest XID on the system to see if there was any point at all in
bothering to vacuum. I don't see anything analagous in autovacuum.c;
this might well be a useful addition.
In the Slony-I cleanup thread loop, we collect, in each iteration, the
current earliest XID.
In each iteration of this loop, we check to see if that XID has
changed.
- First time thru, it changes from 0 to 'some value' and so tries to do
a vacuum.
- But supposing you have some long running transaction (say, a pg_dump
that runs for 2h), it becomes pretty futile to bother trying to
vacuum things for the duration of that transaction, because that
long running transaction will, via MVCC, hold onto any old tuples.
It strikes me as a slick idea for autovacuum to take on that
behaviour. If the daily backup runs for 2h, then it is quite futile
to bother vacuuming a table multiple times during that 2h period when
none of the tuples obsoleted during the 2h period will be able to be
cleaned out until the end.
Presumably this means that, during that 2h period, pg_autovacuum would
probably only issue ANALYZE statements...
--
let name="cbbrowne" and tld="ntlug.org" in String.concat "@" [name;tld];;
http://www.ntlug.org/~cbbrowne/languages.html
Rules of the Evil Overlord #51. "If one of my dungeon guards begins
expressing concern over the conditions in the beautiful princess'
cell, I will immediately transfer him to a less people-oriented
position." <http://www.eviloverlord.com/;
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: [HACKERS] autovacuum
2006-01-23 05:07 autovacuum Enzo Daddario <enzo@pienetworks.com>
2006-01-26 14:41 ` Re: autovacuum Matthew T. O'Connor <matthew@zeut.net>
2006-02-01 17:14 ` Re: autovacuum Chris Browne <cbbrowne@acm.org>
@ 2006-02-01 17:29 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2006-02-01 17:43 ` Re: [HACKERS] autovacuum Matthew T. O'Connor <matthew@zeut.net>
2006-02-01 17:54 ` Re: autovacuum Chris Browne <cbbrowne@acm.org>
2006-02-01 18:18 ` Re: [HACKERS] autovacuum Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 3 replies; 29+ messages in thread
From: Alvaro Herrera @ 2006-02-01 17:29 UTC (permalink / raw)
To: Chris Browne <cbbrowne@acm.org>; +Cc: pgsql-admin; pgsql-hackers@postgresql.org
Chris Browne wrote:
> It strikes me as a slick idea for autovacuum to take on that
> behaviour. If the daily backup runs for 2h, then it is quite futile
> to bother vacuuming a table multiple times during that 2h period when
> none of the tuples obsoleted during the 2h period will be able to be
> cleaned out until the end.
Hmm, yeah, sounds useful. There's one implementation issue to notice
however, and it's that the autovacuum process dies and restarts for each
iteration, so there's no way for it to remember previous state unless
it's saved somewhere permanent, as the stats info is.
However this seems at least slightly redundant with the "maintenance
window" feature -- you could set a high barrier to vacuum during the
daily backup period instead. (Anybody up for doing this job?)
--
Alvaro Herrera http://www.amazon.com/gp/registry/5ZYLFMCVHXC
"No single strategy is always right (Unless the boss says so)"
(Larry Wall)
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: [HACKERS] autovacuum
2006-01-23 05:07 autovacuum Enzo Daddario <enzo@pienetworks.com>
2006-01-26 14:41 ` Re: autovacuum Matthew T. O'Connor <matthew@zeut.net>
2006-02-01 17:14 ` Re: autovacuum Chris Browne <cbbrowne@acm.org>
2006-02-01 17:29 ` Re: [HACKERS] autovacuum Alvaro Herrera <alvherre@alvh.no-ip.org>
@ 2006-02-01 17:43 ` Matthew T. O'Connor <matthew@zeut.net>
2 siblings, 0 replies; 29+ messages in thread
From: Matthew T. O'Connor @ 2006-02-01 17:43 UTC (permalink / raw)
To: Chris Browne <cbbrowne@acm.org>; pgsql-admin; pgsql-hackers@postgresql.org
Alvaro Herrera wrote:
> Chris Browne wrote:
>
>> It strikes me as a slick idea for autovacuum to take on that
>> behaviour. If the daily backup runs for 2h, then it is quite futile
>> to bother vacuuming a table multiple times during that 2h period when
>> none of the tuples obsoleted during the 2h period will be able to be
>> cleaned out until the end.
>
> Hmm, yeah, sounds useful. There's one implementation issue to notice
> however, and it's that the autovacuum process dies and restarts for each
> iteration, so there's no way for it to remember previous state unless
> it's saved somewhere permanent, as the stats info is.
>
> However this seems at least slightly redundant with the "maintenance
> window" feature -- you could set a high barrier to vacuum during the
> daily backup period instead. (Anybody up for doing this job?)
I can't promise anything, but it's on my list of things to hopefully
find time for in the coming months. No way I can start it in Feb, but
maybe sometime in March. Anyone else?
Matt
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: autovacuum
2006-01-23 05:07 autovacuum Enzo Daddario <enzo@pienetworks.com>
2006-01-26 14:41 ` Re: autovacuum Matthew T. O'Connor <matthew@zeut.net>
2006-02-01 17:14 ` Re: autovacuum Chris Browne <cbbrowne@acm.org>
2006-02-01 17:29 ` Re: [HACKERS] autovacuum Alvaro Herrera <alvherre@alvh.no-ip.org>
@ 2006-02-01 17:54 ` Chris Browne <cbbrowne@acm.org>
2 siblings, 0 replies; 29+ messages in thread
From: Chris Browne @ 2006-02-01 17:54 UTC (permalink / raw)
To: pgsql-hackers@postgresql.org
alvherre@alvh.no-ip.org (Alvaro Herrera) writes:
> Chris Browne wrote:
>
>> It strikes me as a slick idea for autovacuum to take on that
>> behaviour. If the daily backup runs for 2h, then it is quite futile
>> to bother vacuuming a table multiple times during that 2h period when
>> none of the tuples obsoleted during the 2h period will be able to be
>> cleaned out until the end.
>
> Hmm, yeah, sounds useful. There's one implementation issue to notice
> however, and it's that the autovacuum process dies and restarts for each
> iteration, so there's no way for it to remember previous state unless
> it's saved somewhere permanent, as the stats info is.
Hmm. It restarts repeatedly??? Hmmm...
> However this seems at least slightly redundant with the "maintenance
> window" feature -- you could set a high barrier to vacuum during the
> daily backup period instead. (Anybody up for doing this job?)
In effect, this would be an alternative to the "window" feature. You
open the window by starting pg_dump; pg_autovacuum would automatically
notice that as the eldest XID, and stop work until the pg_dump
actually finished.
In a way, it strikes me as more elegant; it would automatically notice
"backup windows," noticing *exact* start and end times...
--
select 'cbbrowne' || '@' || 'ntlug.org';
http://www.ntlug.org/~cbbrowne/linuxxian.html
"I'm sorry, Mr. Kipling, but you just don't know how to use the
English Language." -- Editor of the San Francisco Examiner, informing
Rudyard Kipling, who had one article published in the newspaper, that
he needn't bother submitting a second, 1889
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: [HACKERS] autovacuum
2006-01-23 05:07 autovacuum Enzo Daddario <enzo@pienetworks.com>
2006-01-26 14:41 ` Re: autovacuum Matthew T. O'Connor <matthew@zeut.net>
2006-02-01 17:14 ` Re: autovacuum Chris Browne <cbbrowne@acm.org>
2006-02-01 17:29 ` Re: [HACKERS] autovacuum Alvaro Herrera <alvherre@alvh.no-ip.org>
@ 2006-02-01 18:18 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-02-01 19:51 ` Re: autovacuum Chris Browne <cbbrowne@acm.org>
2 siblings, 1 reply; 29+ messages in thread
From: Tom Lane @ 2006-02-01 18:18 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@alvh.no-ip.org>; +Cc: Chris Browne <cbbrowne@acm.org>; pgsql-admin; pgsql-hackers@postgresql.org
Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> Hmm, yeah, sounds useful. There's one implementation issue to notice
> however, and it's that the autovacuum process dies and restarts for each
> iteration, so there's no way for it to remember previous state unless
> it's saved somewhere permanent, as the stats info is.
I think you'd really need to remember the previous oldest XID on a
per-table basis to get full traction out of the idea. But weren't we
thinking of tracking something isomorphic to this for purposes of
minimizing anti-wraparound VACUUMs?
regards, tom lane
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: autovacuum
2006-01-23 05:07 autovacuum Enzo Daddario <enzo@pienetworks.com>
2006-01-26 14:41 ` Re: autovacuum Matthew T. O'Connor <matthew@zeut.net>
2006-02-01 17:14 ` Re: autovacuum Chris Browne <cbbrowne@acm.org>
2006-02-01 17:29 ` Re: [HACKERS] autovacuum Alvaro Herrera <alvherre@alvh.no-ip.org>
2006-02-01 18:18 ` Re: [HACKERS] autovacuum Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-02-01 19:51 ` Chris Browne <cbbrowne@acm.org>
0 siblings, 0 replies; 29+ messages in thread
From: Chris Browne @ 2006-02-01 19:51 UTC (permalink / raw)
To: pgsql-hackers@postgresql.org
tgl@sss.pgh.pa.us (Tom Lane) writes:
> Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
>> Hmm, yeah, sounds useful. There's one implementation issue to notice
>> however, and it's that the autovacuum process dies and restarts for each
>> iteration, so there's no way for it to remember previous state unless
>> it's saved somewhere permanent, as the stats info is.
>
> I think you'd really need to remember the previous oldest XID on a
> per-table basis to get full traction out of the idea. But weren't
> we thinking of tracking something isomorphic to this for purposes of
> minimizing anti-wraparound VACUUMs?
I think I'd like that even better :-).
In the Slony-I case, the tables being vacuumed are ones where the
deletion is taking place within the same thread, so that having one
XID is plenty enough because the only thing that should be touching
the tables is the cleanup thread, which is invoked every 10 minutes.
One XID is enough "protection" for that, as least as a reasonable
approximation.
Tracking just the one eldest XID is still quite likely to be
*reasonably* useful with autovacuum, assuming there isn't a by-table
option. By-table would be better, though.
--
(reverse (concatenate 'string "gro.mca" "@" "enworbbc"))
http://www3.sympatico.ca/cbbrowne/sgml.html
"Politics is not a bad profession. If you succeed there are many
rewards, if you disgrace yourself you can always write a book."
-- Ronald Reagan
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: autovacuum
2006-01-23 05:07 autovacuum Enzo Daddario <enzo@pienetworks.com>
2006-01-26 14:41 ` Re: autovacuum Matthew T. O'Connor <matthew@zeut.net>
@ 2006-02-01 20:50 ` Chris Browne <cbbrowne@acm.org>
2006-02-01 21:21 ` Re: autovacuum Jim C. Nasby <jnasby@pervasive.com>
1 sibling, 1 reply; 29+ messages in thread
From: Chris Browne @ 2006-02-01 20:50 UTC (permalink / raw)
To: pgsql-admin
matthew@zeut.net ("Matthew T. O'Connor") writes:
> Hope that helps. Real world feed-back is always welcome.
While I'm at it, I should throw in an idea that I had a little while
back about a "vacuum request manager."
This is kind of orthogonal to everything else that has been happening
with pg_autovacuum...
One of the troubles we have been hitting with our homebrew scripts is
when locking doesn't turn out, and they start submitting multiple
vacuums at once, which sometimes builds up "to ill."
A thought I had was to create a daemon that would serially process
requests. It would just watch a table of requests, and when it finds
work, start work.
We'd then have some sort of "injection" process that would tell the
daemon "Here's new work!"
Requests would be defined thus:
/* cbbrowne@[local]/dba2 vacdb=*/ \d vacuum_requests
Table "public.vacuum_requests"
Column | Type | Modifiers
--------------+--------------------------+------------------------
vtable | text | not null
vhost | text | not null
vdatabase | text | not null
urgency | integer | not null default 1
created_on | timestamp with time zone | not null default now()
completed_on | timestamp with time zone |
failed_at | timestamp with time zone |
Indexes:
"vacuum_requests_pkey" primary key, btree (vtable, vhost, vdatabase, created_on)
"vr_priority" btree (vhost, vdatabase, urgency) WHERE ((completed_on IS NULL) AND (failed_at IS NULL))
/* cbbrowne@[local]/dba2 vacdb=*/ \d vacuum_start
Table "public.vacuum_start"
Column | Type | Modifiers
--------------+--------------------------+------------------------
vtable | text | not null
vhost | text | not null
vdatabase | text | not null
started_on | timestamp with time zone | not null default now()
completed_on | timestamp with time zone |
Indexes:
"vacuum_start_pkey" primary key, btree (vtable, vhost, vdatabase, started_on)
/* cbbrowne@[local]/dba2 vacdb=*/ \d vacuum_failures
Table "public.vacuum_failures"
Column | Type | Modifiers
------------+--------------------------+------------------------
vtable | text | not null
vhost | text | not null
vdatabase | text | not null
started_on | timestamp with time zone | not null
failed_on | timestamp with time zone | not null default now()
Indexes:
"vacuum_failures_pkey" primary key, btree (vtable, vhost, vdatabase, started_on)
This has a bit more generality than would be needed for handling just
one postmaster; host/database would allow this to be used to manage
multiple backends...
We have, in our "kludged-up scripts," three levels of granularity:
1. There are tables we vacuum every few minutes; they would be at
urgency 1; every few minutes, we would, in effect, run the query...
insert into vacuum_requests (vtable, vhost, vdatabase, urgency)
select t.fqtablename, h.hostname, tld.name, 1
from urgent_tables t, all_hosts h, all_tlds tld;
2. Then, there are "hourly" tables, at urgency level 2.
Once an hour, we run:
insert into vacuum_requests (vtable, vhost, vdatabase, urgency)
select t.fqtablename, h.hostname, tld.name, 2
from hourly_tables t, all_hosts h, all_tlds tld;
3. Once a day, we'd do something kind of like:
insert into vacuum_requests (vtable, vhost, vdatabase, urgency)
select table_schema || '.' || table_name, h.hostname, tld.name, 3
from information_schema.tables, all_hosts h, all_tlds tld
where table_type = 'BASE TABLE' and table_schema in ('public', 'pg_catalog');
The event loop for the daemon would be to look up the highest priority
table, and add an entry to vacuum_start.
Then it vacuums the table.
If that succeeds, the table is marked as complete in both
vacuum_start, and, FOR EVERY ENTRY CURRENTLY OUTSTANDING, in
vacuum_requests. Thus, if a table is queued up 20 times, it will be
vacuumed once, and marked as done 20 times.
If that fails, all the relevant entries in vacuum_start and
vacuum_requests are marked with the failure information, and a record
is added to the failures table.
We're putting this off, pending the thought that, with 8.1, it's worth
testing out pg_autovacuum again.
The above is an "in-the-database" way of queueing up requests,
associating priorities to them, and having the queue be
administrator-visible.
We were anticipating using our present quasi-kludgy scripts to add our
favorite tables to the queue; it would seem a nice/natural thing for
there to be some automatic process (ala the pg_autovacuum daemon) that
could add things to the queue based on its knowledge of updates.
My thought is that if anything about the above appears useful to
pg_autovacuum, I'd be happy if pg_autovacuum grabbed (stole? ;-)) some
of the ideas.
--
"cbbrowne","@","cbbrowne.com"
http://cbbrowne.com/info/sap.html
"The X-Files are too optimistic. The truth is *not* out there..."
-- Anthony Ord <nws@rollingthunder.co.uk>
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: autovacuum
2006-01-23 05:07 autovacuum Enzo Daddario <enzo@pienetworks.com>
2006-01-26 14:41 ` Re: autovacuum Matthew T. O'Connor <matthew@zeut.net>
2006-02-01 20:50 ` Re: autovacuum Chris Browne <cbbrowne@acm.org>
@ 2006-02-01 21:21 ` Jim C. Nasby <jnasby@pervasive.com>
2006-02-02 02:32 ` Re: autovacuum Christopher Browne <cbbrowne@acm.org>
0 siblings, 1 reply; 29+ messages in thread
From: Jim C. Nasby @ 2006-02-01 21:21 UTC (permalink / raw)
To: Chris Browne <cbbrowne@acm.org>; +Cc: pgsql-admin
This seems maybe a bit overkill to me. I think what would be more useful
is if autovacuum could execute more than one vacuum at a time, and you
could specify tables that are high priority (or possibly just say that
all tables with less than X live tuples in them are high priority). That
way a longer-running vacuum on a large table wouldn't prevent more
vacuum-sensative tables (such as queues) from being vacuumed frequently
enough.
On Wed, Feb 01, 2006 at 03:50:25PM -0500, Chris Browne wrote:
> matthew@zeut.net ("Matthew T. O'Connor") writes:
> > Hope that helps. Real world feed-back is always welcome.
>
> While I'm at it, I should throw in an idea that I had a little while
> back about a "vacuum request manager."
>
> This is kind of orthogonal to everything else that has been happening
> with pg_autovacuum...
>
> One of the troubles we have been hitting with our homebrew scripts is
> when locking doesn't turn out, and they start submitting multiple
> vacuums at once, which sometimes builds up "to ill."
>
> A thought I had was to create a daemon that would serially process
> requests. It would just watch a table of requests, and when it finds
> work, start work.
>
> We'd then have some sort of "injection" process that would tell the
> daemon "Here's new work!"
>
> Requests would be defined thus:
>
> /* cbbrowne@[local]/dba2 vacdb=*/ \d vacuum_requests
> Table "public.vacuum_requests"
> Column | Type | Modifiers
> --------------+--------------------------+------------------------
> vtable | text | not null
> vhost | text | not null
> vdatabase | text | not null
> urgency | integer | not null default 1
> created_on | timestamp with time zone | not null default now()
> completed_on | timestamp with time zone |
> failed_at | timestamp with time zone |
> Indexes:
> "vacuum_requests_pkey" primary key, btree (vtable, vhost, vdatabase, created_on)
> "vr_priority" btree (vhost, vdatabase, urgency) WHERE ((completed_on IS NULL) AND (failed_at IS NULL))
>
> /* cbbrowne@[local]/dba2 vacdb=*/ \d vacuum_start
> Table "public.vacuum_start"
> Column | Type | Modifiers
> --------------+--------------------------+------------------------
> vtable | text | not null
> vhost | text | not null
> vdatabase | text | not null
> started_on | timestamp with time zone | not null default now()
> completed_on | timestamp with time zone |
> Indexes:
> "vacuum_start_pkey" primary key, btree (vtable, vhost, vdatabase, started_on)
>
> /* cbbrowne@[local]/dba2 vacdb=*/ \d vacuum_failures
> Table "public.vacuum_failures"
> Column | Type | Modifiers
> ------------+--------------------------+------------------------
> vtable | text | not null
> vhost | text | not null
> vdatabase | text | not null
> started_on | timestamp with time zone | not null
> failed_on | timestamp with time zone | not null default now()
> Indexes:
> "vacuum_failures_pkey" primary key, btree (vtable, vhost, vdatabase, started_on)
>
>
> This has a bit more generality than would be needed for handling just
> one postmaster; host/database would allow this to be used to manage
> multiple backends...
>
> We have, in our "kludged-up scripts," three levels of granularity:
>
> 1. There are tables we vacuum every few minutes; they would be at
> urgency 1; every few minutes, we would, in effect, run the query...
>
> insert into vacuum_requests (vtable, vhost, vdatabase, urgency)
> select t.fqtablename, h.hostname, tld.name, 1
> from urgent_tables t, all_hosts h, all_tlds tld;
>
> 2. Then, there are "hourly" tables, at urgency level 2.
>
> Once an hour, we run:
>
> insert into vacuum_requests (vtable, vhost, vdatabase, urgency)
> select t.fqtablename, h.hostname, tld.name, 2
> from hourly_tables t, all_hosts h, all_tlds tld;
>
> 3. Once a day, we'd do something kind of like:
>
> insert into vacuum_requests (vtable, vhost, vdatabase, urgency)
> select table_schema || '.' || table_name, h.hostname, tld.name, 3
> from information_schema.tables, all_hosts h, all_tlds tld
> where table_type = 'BASE TABLE' and table_schema in ('public', 'pg_catalog');
>
> The event loop for the daemon would be to look up the highest priority
> table, and add an entry to vacuum_start.
>
> Then it vacuums the table.
>
> If that succeeds, the table is marked as complete in both
> vacuum_start, and, FOR EVERY ENTRY CURRENTLY OUTSTANDING, in
> vacuum_requests. Thus, if a table is queued up 20 times, it will be
> vacuumed once, and marked as done 20 times.
>
> If that fails, all the relevant entries in vacuum_start and
> vacuum_requests are marked with the failure information, and a record
> is added to the failures table.
>
> We're putting this off, pending the thought that, with 8.1, it's worth
> testing out pg_autovacuum again.
>
> The above is an "in-the-database" way of queueing up requests,
> associating priorities to them, and having the queue be
> administrator-visible.
>
> We were anticipating using our present quasi-kludgy scripts to add our
> favorite tables to the queue; it would seem a nice/natural thing for
> there to be some automatic process (ala the pg_autovacuum daemon) that
> could add things to the queue based on its knowledge of updates.
>
> My thought is that if anything about the above appears useful to
> pg_autovacuum, I'd be happy if pg_autovacuum grabbed (stole? ;-)) some
> of the ideas.
> --
> "cbbrowne","@","cbbrowne.com"
> http://cbbrowne.com/info/sap.html
> "The X-Files are too optimistic. The truth is *not* out there..."
> -- Anthony Ord <nws@rollingthunder.co.uk>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: autovacuum
2006-01-23 05:07 autovacuum Enzo Daddario <enzo@pienetworks.com>
2006-01-26 14:41 ` Re: autovacuum Matthew T. O'Connor <matthew@zeut.net>
2006-02-01 20:50 ` Re: autovacuum Chris Browne <cbbrowne@acm.org>
2006-02-01 21:21 ` Re: autovacuum Jim C. Nasby <jnasby@pervasive.com>
@ 2006-02-02 02:32 ` Christopher Browne <cbbrowne@acm.org>
2006-02-06 18:37 ` Re: autovacuum Scott Marlowe <smarlowe@g2switchworks.com>
0 siblings, 1 reply; 29+ messages in thread
From: Christopher Browne @ 2006-02-02 02:32 UTC (permalink / raw)
To: pgsql-admin
> This seems maybe a bit overkill to me. I think what would be more useful
> is if autovacuum could execute more than one vacuum at a time, and you
> could specify tables that are high priority (or possibly just say that
> all tables with less than X live tuples in them are high priority). That
> way a longer-running vacuum on a large table wouldn't prevent more
> vacuum-sensative tables (such as queues) from being vacuumed frequently
> enough.
Actually, I can think of a case for much the opposite, namely to want
to concurrently vacuum some LARGE tables...
Suppose you have 2 rather big tables that get updates on similar
schedules such that both will have a lot of dead tuples at similar
times.
And suppose both of these tables are Way Large, so that they take
six hours to vacuum.
I could argue for kicking off vacuums on both, at the same moment;
they'll both be occupying transactions for 1/4 of a day, and, with
possibly related patterns of updates, doing them one after the other
*wouldn't* forcibly get you more tuples cleaned than doing them
concurrently.
I'm not sure that's a case to push for, either, as something
pg_autovacuum is smart enough to handle; I'm just putting out some
ideas that got enough internal discussion to suggest they were
interesting enough to let others consider...
--
"cbbrowne","@","gmail.com"
http://cbbrowne.com/info/linuxdistributions.html
"Transported to a surreal landscape, a young girl kills the first
woman she meets and then teams up with three complete strangers to
kill again." -- Unknown, Marin County newspaper's TV listing for _The
Wizard of Oz_
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: autovacuum
2006-01-23 05:07 autovacuum Enzo Daddario <enzo@pienetworks.com>
2006-01-26 14:41 ` Re: autovacuum Matthew T. O'Connor <matthew@zeut.net>
2006-02-01 20:50 ` Re: autovacuum Chris Browne <cbbrowne@acm.org>
2006-02-01 21:21 ` Re: autovacuum Jim C. Nasby <jnasby@pervasive.com>
2006-02-02 02:32 ` Re: autovacuum Christopher Browne <cbbrowne@acm.org>
@ 2006-02-06 18:37 ` Scott Marlowe <smarlowe@g2switchworks.com>
0 siblings, 0 replies; 29+ messages in thread
From: Scott Marlowe @ 2006-02-06 18:37 UTC (permalink / raw)
To: Christopher Browne <cbbrowne@acm.org>; +Cc: pgsql-admin
On Wed, 2006-02-01 at 20:32, Christopher Browne wrote:
> > This seems maybe a bit overkill to me. I think what would be more useful
> > is if autovacuum could execute more than one vacuum at a time, and you
> > could specify tables that are high priority (or possibly just say that
> > all tables with less than X live tuples in them are high priority). That
> > way a longer-running vacuum on a large table wouldn't prevent more
> > vacuum-sensative tables (such as queues) from being vacuumed frequently
> > enough.
>
> Actually, I can think of a case for much the opposite, namely to want
> to concurrently vacuum some LARGE tables...
>
> Suppose you have 2 rather big tables that get updates on similar
> schedules such that both will have a lot of dead tuples at similar
> times.
>
> And suppose both of these tables are Way Large, so that they take
> six hours to vacuum.
>
> I could argue for kicking off vacuums on both, at the same moment;
> they'll both be occupying transactions for 1/4 of a day, and, with
> possibly related patterns of updates, doing them one after the other
> *wouldn't* forcibly get you more tuples cleaned than doing them
> concurrently.
>
> I'm not sure that's a case to push for, either, as something
> pg_autovacuum is smart enough to handle; I'm just putting out some
> ideas that got enough internal discussion to suggest they were
> interesting enough to let others consider...
This could be a big win on databases where those two tables were on
different table spaces, since vacuum now wouldn't be fighting for the
same thin I/O stream twice.
If the autovacuum daemon scheduled vacuums so that each tablespace had a
list of vacuums to run, but then ran those sets in parallel (i.e.
tablespace1 has one single vacuum running though a list while
tablespace2 has its own single vacuum.)
Maybe even a setting that told it the max number to run in parallel for
each tablespace. After all, a tablespace running on 30 hard drives in a
RAID-10 could handly several concurrent vacuums, while another
tablespace running on a single drive would be well limited to one vacuum
at a time.
^ permalink raw reply [nested|flat] 29+ messages in thread
* autovacuum
@ 2006-02-04 09:14 Alp UYANIK <alp@intersan.com.tr>
2006-02-04 14:54 ` Re: autovacuum Christopher Browne <cbbrowne@acm.org>
0 siblings, 1 reply; 29+ messages in thread
From: Alp UYANIK @ 2006-02-04 09:14 UTC (permalink / raw)
To: pgsql-admin
i have PostgreSQL 8.1.2
i want to enable autovacuum at my PostgreSQL 8.1.2 from postgresql.conf
i've got 50-60 insert and/or update queries in a second in that case tables
shouldn't be locked
does autovacuum locks tables while vacuuming?
and also i wonder what should be the configuration?
memory: 2gb ddr2
CPU: P4-prescott 3193.495 MHz
cache size : 2048 KB
Alp UYANIK
Software Engineer
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: autovacuum
2006-02-04 09:14 autovacuum Alp UYANIK <alp@intersan.com.tr>
@ 2006-02-04 14:54 ` Christopher Browne <cbbrowne@acm.org>
2006-02-04 19:12 ` Re: autovacuum Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 29+ messages in thread
From: Christopher Browne @ 2006-02-04 14:54 UTC (permalink / raw)
To: pgsql-admin
> i have PostgreSQL 8.1.2
>
> i want to enable autovacuum at my PostgreSQL 8.1.2 from postgresql.conf
> i've got 50-60 insert and/or update queries in a second in that case
> tables shouldn't be locked
> does autovacuum locks tables while vacuuming?
Of course it does; any request to access a relation will issue one or
more locks on the relation.
VACUUM issues an AccessShareLock request against each relation that is
vacuumed, which is probably nearly the same lock request your
applications will be requesting, save for the fact that they'll also
be submitting some RowExclusiveLock requests for individual rows of
relations.
http://www.postgresql.org/docs/8.1/interactive/mvcc.html
--
let name="cbbrowne" and tld="gmail.com" in name ^ "@" ^ tld;;
http://linuxdatabases.info/info/nonrdbms.html
"Bother," said Pooh, as he deleted his root directory.
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: autovacuum
2006-02-04 09:14 autovacuum Alp UYANIK <alp@intersan.com.tr>
2006-02-04 14:54 ` Re: autovacuum Christopher Browne <cbbrowne@acm.org>
@ 2006-02-04 19:12 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 29+ messages in thread
From: Tom Lane @ 2006-02-04 19:12 UTC (permalink / raw)
To: Christopher Browne <cbbrowne@acm.org>; +Cc: pgsql-admin
Christopher Browne <cbbrowne@acm.org> writes:
>> does autovacuum locks tables while vacuuming?
> Of course it does; any request to access a relation will issue one or
> more locks on the relation.
This is correct in general ...
> VACUUM issues an AccessShareLock request against each relation that is
> vacuumed, which is probably nearly the same lock request your
> applications will be requesting, save for the fact that they'll also
> be submitting some RowExclusiveLock requests for individual rows of
> relations.
... but wrong in detail. Actually VACUUM takes ShareUpdateExclusive
lock, which is a bit stronger than an ordinary reader's AccessShare
lock (it does not block ordinary reads or updates, but it does block
index creation as well as other attempts to VACUUM the table). Also,
RowExclusiveLock is a table-level lock. Data-modifying commands such
as UPDATE take RowExclusiveLock on the whole table as a means of letting
other sessions know that someone is changing the table. They also
take row-level locks on the specific rows they change.
This is all discussed here:
http://www.postgresql.org/docs/8.1/static/explicit-locking.html
regards, tom lane
^ permalink raw reply [nested|flat] 29+ messages in thread
* autovacuum
@ 2006-06-01 15:40 Sriram Dandapani <sdandapani@counterpane.com>
2006-06-01 15:51 ` Re: autovacuum Chris Hoover <revoohc@gmail.com>
0 siblings, 1 reply; 29+ messages in thread
From: Sriram Dandapani @ 2006-06-01 15:40 UTC (permalink / raw)
To: pgsql-admin
Hi
I have a table about 17G in size. I did a pg_relation_size before
deleting 2 millions rows. I waited a few hours(actually overnight) and
then checked the table size with pg_relation_size. The size reported was
exactly the same.
My autovacuum is set to run every 50 minutes.
Settings:
autovacuum = on # enable autovacuum subprocess?
autovacuum_naptime = 3000 # time between autovacuum runs,
in secs
autovacuum_vacuum_threshold = 10000 # min # of tuple updates before
# vacuum
autovacuum_analyze_threshold = 1500 # min # of tuple updates before
# analyze
autovacuum_vacuum_scale_factor = 0.4 # fraction of rel size before
# vacuum
autovacuum_analyze_scale_factor = 0.2 # fraction of rel size before
# analyze
autovacuum_vacuum_cost_delay = -1
vacuum_cost_delay = 200 # 0-1000 milliseconds
#vacuum_cost_page_hit = 1 # 0-10000 credits
#vacuum_cost_page_miss = 10 # 0-10000 credits
#vacuum_cost_page_dirty = 20 # 0-10000 credits
vacuum_cost_limit = 50
work_mem = 802400 # min 64, size in KB
maintenance_work_mem = 102400 # min 1024, size in KB
#max_stack_depth = 2048 # min 100, size in KB
# - Free Space Map -
max_fsm_pages = 2000000
Why doesn't pg_relation_size take the autovacuum maintenance into effect
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: autovacuum
2006-06-01 15:40 autovacuum Sriram Dandapani <sdandapani@counterpane.com>
@ 2006-06-01 15:51 ` Chris Hoover <revoohc@gmail.com>
0 siblings, 0 replies; 29+ messages in thread
From: Chris Hoover @ 2006-06-01 15:51 UTC (permalink / raw)
To: Sriram Dandapani <sdandapani@counterpane.com>; +Cc: pgsql-admin
Autovacuum does not release space back to the OS. It only marks the tuples
avaiable for reuse. To give the space back to the OS, you would have to run
a vacuum full on the table.
Chris
On 6/1/06, Sriram Dandapani <sdandapani@counterpane.com> wrote:
>
> Hi
>
>
>
> I have a table about 17G in size. I did a pg_relation_size before deleting
> 2 millions rows. I waited a few hours(actually overnight) and then checked
> the table size with pg_relation_size. The size reported was exactly the
> same.
>
>
>
> My autovacuum is set to run every 50 minutes.
>
>
>
> Settings:
>
>
>
> autovacuum = on # enable autovacuum subprocess?
>
> autovacuum_naptime = 3000 # time between autovacuum runs, in
> secs
>
> autovacuum_vacuum_threshold = 10000 # min # of tuple updates before
>
> # vacuum
>
> autovacuum_analyze_threshold = 1500 # min # of tuple updates before
>
> # analyze
>
> autovacuum_vacuum_scale_factor = 0.4 # fraction of rel size before
>
> # vacuum
>
> autovacuum_analyze_scale_factor = 0.2 # fraction of rel size before
>
> # analyze
>
> autovacuum_vacuum_cost_delay = -1
>
>
>
> vacuum_cost_delay = 200 # 0-1000 milliseconds
>
> #vacuum_cost_page_hit = 1 # 0-10000 credits
>
> #vacuum_cost_page_miss = 10 # 0-10000 credits
>
> #vacuum_cost_page_dirty = 20 # 0-10000 credits
>
> vacuum_cost_limit = 50
>
>
>
>
>
> work_mem = 802400 # min 64, size in KB
>
> maintenance_work_mem = 102400 # min 1024, size in KB
>
> #max_stack_depth = 2048 # min 100, size in KB
>
>
>
> # - Free Space Map -
>
> max_fsm_pages = 2000000
>
>
>
>
>
> Why doesn't pg_relation_size take the autovacuum maintenance into effect
>
^ permalink raw reply [nested|flat] 29+ messages in thread
* autovacuum
@ 2006-06-14 16:52 Sriram Dandapani <sdandapani@counterpane.com>
2006-06-14 20:06 ` Re: autovacuum Scott Marlowe <smarlowe@g2switchworks.com>
2006-06-15 11:20 ` Re: autovacuum Robin Iddon <robin@edesix.com>
0 siblings, 2 replies; 29+ messages in thread
From: Sriram Dandapani @ 2006-06-14 16:52 UTC (permalink / raw)
To: pgsql-admin
Hi
I have a table into which data flows at a high rate say 2 million rows
per hour. Every 15 minutes, these rows are processed and deleted.
At any point in time, the row count for the table hovers around 500K
(due to the deletes).
I have set max_fsm_pages to about 2000000. I still see the
pg_total_relation_size constantly reporting an increasing size.
How do I know if autovacuum is reclaiming space or is my max_fsm setting
plain wrong. (I understand that autovacuum marks tuples for reuse, but
shouldn't pg_total_relation_size take that into account)
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: autovacuum
2006-06-14 16:52 autovacuum Sriram Dandapani <sdandapani@counterpane.com>
@ 2006-06-14 20:06 ` Scott Marlowe <smarlowe@g2switchworks.com>
1 sibling, 0 replies; 29+ messages in thread
From: Scott Marlowe @ 2006-06-14 20:06 UTC (permalink / raw)
To: Sriram Dandapani <sdandapani@counterpane.com>; +Cc: pgsql-admin
On Wed, 2006-06-14 at 11:52, Sriram Dandapani wrote:
> Hi
>
>
>
> I have a table into which data flows at a high rate say 2 million rows
> per hour. Every 15 minutes, these rows are processed and deleted.
>
> At any point in time, the row count for the table hovers around 500K
> (due to the deletes).
>
>
>
> I have set max_fsm_pages to about 2000000. I still see the
> pg_total_relation_size constantly reporting an increasing size.
>
>
>
> How do I know if autovacuum is reclaiming space or is my max_fsm
> setting plain wrong. (I understand that autovacuum marks tuples for
> reuse, but shouldn’t pg_total_relation_size take that into account)
I wouldn't really rely on just autovacuum to do this.
But, if you must, adjust the threshholds to have it trigger more easily.
Me personally, if I'm running a script that deletes 500,000 rows, I put
a vacuum at the end of it. That way you KNOW the vacuum got run.
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: autovacuum
2006-06-14 16:52 autovacuum Sriram Dandapani <sdandapani@counterpane.com>
@ 2006-06-15 11:20 ` Robin Iddon <robin@edesix.com>
1 sibling, 0 replies; 29+ messages in thread
From: Robin Iddon @ 2006-06-15 11:20 UTC (permalink / raw)
To: pgsql-admin
> How do I know if autovacuum is reclaiming space or is my max_fsm setting
> plain wrong. (I understand that autovacuum marks tuples for reuse, but
> shouldn't pg_total_relation_size take that into account)
>
You can find out if your FSM settings are big enough by running vacuumdb
-av (and yes, it needs to be -a) - obviously you might choose not to run
this when the database is busy doing something else.
Capture the output - at the very end it will tell you whether or not
your FSM settings are adequate to map the reusable space in the database.
You might also want to read the documentation on autovacuum's logging
output options - this may tell you what you want to know - prior to 8.1
autovacuum was documented in (on my system)
/usr/doc/postgres-x.x.x/contrib/README.pg_autovacuum - post 8.1 it's
part of the main distribution and should be documented in the main PG docs.
Hope this helps,
Robin
^ permalink raw reply [nested|flat] 29+ messages in thread
* autovacuum?
@ 2008-04-17 19:48 Jesper Krogh <jesper@krogh.cc>
2008-04-18 02:59 ` Re: autovacuum? Pavan Deolasee <pavan.deolasee@gmail.com>
0 siblings, 1 reply; 29+ messages in thread
From: Jesper Krogh @ 2008-04-17 19:48 UTC (permalink / raw)
To: pgsql-admin
Hi.
I have a mostly insert/select database, so vacumming is generally not
much needed.. but among the tables I have one which is used as a
message-queue so a lot of insert/updates/deletes on small simple records
on that one.
How do I check that autovacumm is running at all? (I think i have
enabled it in /etc/postgresql/8.2/*/postgresql.conf ).
select relname,last_autovacuum,last_autoanalyze from pg_stat_user_tables
where relname = 'job';
relname | last_autovacuum | last_autoanalyze
---------+-------------------------------+-------------------------------
job | 2008-04-16 07:19:24.832413+02 | 2008-04-17 01:40:05.242914+02
So it seems to be running. But shouldn't it hit the table a lot more?
How do I check that it is somehow near optimal?
--
Jesper
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: autovacuum?
2008-04-17 19:48 autovacuum? Jesper Krogh <jesper@krogh.cc>
@ 2008-04-18 02:59 ` Pavan Deolasee <pavan.deolasee@gmail.com>
0 siblings, 0 replies; 29+ messages in thread
From: Pavan Deolasee @ 2008-04-18 02:59 UTC (permalink / raw)
To: Jesper Krogh <jesper@krogh.cc>; +Cc: pgsql-admin
On Fri, Apr 18, 2008 at 1:18 AM, Jesper Krogh <jesper@krogh.cc> wrote:
>
>
> So it seems to be running.
Yes.
> But shouldn't it hit the table a lot more?
That depends on your autovac settings, especially
autovacuum_vacuum_scale_factor. If it is set to 0.2 (which is
default), then autovacuum would hit the table whenever the percentage
of dead tuples is at least 20%. Again, the percentage of dead tuples
depends on your table size and rate of insert/update/delete.
> How do I check that it is somehow near optimal?
>
Again that depends on how much table bloat you can sustain. If you
don't want the table to bloat too much, you can set aggressive
settings for autovac. But that would trigger autovac more often and
may have performance implication on your other concurrent queries.
Thanks,
Pavan
--
Pavan Deolasee
EnterpriseDB http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 29+ messages in thread
* autovacuum
@ 2012-12-30 10:30 suhas.basavaraj12 <suhas.b@verse.in>
2012-12-30 11:30 ` Re: autovacuum Greg Williamson <gwilliamson39@yahoo.com>
0 siblings, 1 reply; 29+ messages in thread
From: suhas.basavaraj12 @ 2012-12-30 10:30 UTC (permalink / raw)
To: pgsql-admin
Hi,
Can i cancel ongoing autovacuum on a large table? Is it safe?
I am trying to do pg_cancel_backend(procid).Please suggest whether its safe
to do ?
Rgrds
Suhas
--
View this message in context: http://postgresql.1045698.n5.nabble.com/autovacuum-tp5738332.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: autovacuum
2012-12-30 10:30 autovacuum suhas.basavaraj12 <suhas.b@verse.in>
@ 2012-12-30 11:30 ` Greg Williamson <gwilliamson39@yahoo.com>
0 siblings, 0 replies; 29+ messages in thread
From: Greg Williamson @ 2012-12-30 11:30 UTC (permalink / raw)
To: pgsql-admin
Suhas --
>
>Can i cancel ongoing autovacuum on a large table? Is it safe?
>
>I am trying to do pg_cancel_backend(procid).Please suggest whether its safe
>to do ?
>
>Rgrds
>Suhas
>
>
I've killed autovac processes manually with no malign effects other than the consequences of not vacuuming the table involved.
This may depend some on what version of postgres -- that may make a difference. My experience is with 8.4 and 9.1 and both have been OK (although on one database the autovac is set to be very aggressive so it starts again almost immediately. You may need to tweak the config settings if that is the case).
But I have usually found that autovac solves far more problems than it causes.
You may want to post more details about version and config settings related to autovac that differ from defaults. That might help get a better answer, and might help with whatever problem you think autovac is causing.
HTH,
Greg Williamson
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 29+ messages in thread
* autovacuum
@ 2020-10-23 22:02 Henry Francisco Garcia Cortez <garcortez@gmail.com>
2020-10-23 22:32 ` Re: autovacuum David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 29+ messages in thread
From: Henry Francisco Garcia Cortez @ 2020-10-23 22:02 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Hi community I have a question about autovacuum I want to know if every
autovacuum executed to consume everything the memory of autovacuum_work_mem
for instance If I have in auto_vacuum_work_mem 1GB and I have three
autovacuum these consume 1gb of memory RAM or 3GB of memory ram also I want
to know if I increase the numbers of autovacuum, I have read that CPU
increases around of 10% or 20% this increases is for all the autovacuum or
every autovacuum
--
Ing. Henry G. Cortez
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: autovacuum
2020-10-23 22:02 autovacuum Henry Francisco Garcia Cortez <garcortez@gmail.com>
@ 2020-10-23 22:32 ` David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 29+ messages in thread
From: David G. Johnston @ 2020-10-23 22:32 UTC (permalink / raw)
To: Henry Francisco Garcia Cortez <garcortez@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
On Friday, October 23, 2020, Henry Francisco Garcia Cortez <
garcortez@gmail.com> wrote:
> Hi community I have a question about autovacuum I want to know if every
> autovacuum executed to consume everything the memory of autovacuum_work_mem
> for instance If I have in auto_vacuum_work_mem 1GB and I have three
> autovacuum these consume 1gb of memory RAM or 3GB of memory
>
^ permalink raw reply [nested|flat] 29+ messages in thread
* AUTOVACUUM
@ 2023-10-30 11:20 Siraj G <tosiraj.g@gmail.com>
2023-10-30 13:42 ` Re: AUTOVACUUM Ron <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 29+ messages in thread
From: Siraj G @ 2023-10-30 11:20 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Hello -
What are the dictionary objects/scripts we have to check if AUTOVACUUM
parameters are set correctly and need any tuning?
Regards
Siraj
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: AUTOVACUUM
2023-10-30 11:20 AUTOVACUUM Siraj G <tosiraj.g@gmail.com>
@ 2023-10-30 13:42 ` Ron <ronljohnsonjr@gmail.com>
2023-10-31 04:13 ` Re: AUTOVACUUM Siraj G <tosiraj.g@gmail.com>
0 siblings, 1 reply; 29+ messages in thread
From: Ron @ 2023-10-30 13:42 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 10/30/23 06:20, Siraj G wrote:
> Hello -
>
> What are the dictionary objects/scripts we have to check if AUTOVACUUM
> parameters are set correctly and need any tuning?
On Linux:
psql -c "SHOW ALL;" | grep "^ autovacuu"
Then exit $PGDATA/postgresql.conf
--
Born in Arizona, moved to Babylonia.
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: AUTOVACUUM
2023-10-30 11:20 AUTOVACUUM Siraj G <tosiraj.g@gmail.com>
2023-10-30 13:42 ` Re: AUTOVACUUM Ron <ronljohnsonjr@gmail.com>
@ 2023-10-31 04:13 ` Siraj G <tosiraj.g@gmail.com>
0 siblings, 0 replies; 29+ messages in thread
From: Siraj G @ 2023-10-31 04:13 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
Hi Ron
Thank you. I had this info already, but I am looking for
queries/scripts/reports for analysis purposes if these need to be tuned.
Regards
Siraj
On Mon, Oct 30, 2023 at 8:42 AM Ron <ronljohnsonjr@gmail.com> wrote:
> On 10/30/23 06:20, Siraj G wrote:
> > Hello -
> >
> > What are the dictionary objects/scripts we have to check if AUTOVACUUM
> > parameters are set correctly and need any tuning?
>
> On Linux:
> psql -c "SHOW ALL;" | grep "^ autovacuu"
>
>
> Then exit $PGDATA/postgresql.conf
>
> --
> Born in Arizona, moved to Babylonia.
>
>
>
^ permalink raw reply [nested|flat] 29+ messages in thread
end of thread, other threads:[~2023-10-31 04:13 UTC | newest]
Thread overview: 29+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-01-23 05:07 autovacuum Enzo Daddario <enzo@pienetworks.com>
2006-01-26 14:41 ` Matthew T. O'Connor <matthew@zeut.net>
2006-02-01 17:14 ` Chris Browne <cbbrowne@acm.org>
2006-02-01 17:29 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2006-02-01 17:43 ` Matthew T. O'Connor <matthew@zeut.net>
2006-02-01 17:54 ` Chris Browne <cbbrowne@acm.org>
2006-02-01 18:18 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-02-01 19:51 ` Chris Browne <cbbrowne@acm.org>
2006-02-01 20:50 ` Chris Browne <cbbrowne@acm.org>
2006-02-01 21:21 ` Jim C. Nasby <jnasby@pervasive.com>
2006-02-02 02:32 ` Christopher Browne <cbbrowne@acm.org>
2006-02-06 18:37 ` Scott Marlowe <smarlowe@g2switchworks.com>
2006-02-04 09:14 autovacuum Alp UYANIK <alp@intersan.com.tr>
2006-02-04 14:54 ` Christopher Browne <cbbrowne@acm.org>
2006-02-04 19:12 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-06-01 15:40 autovacuum Sriram Dandapani <sdandapani@counterpane.com>
2006-06-01 15:51 ` Chris Hoover <revoohc@gmail.com>
2006-06-14 16:52 autovacuum Sriram Dandapani <sdandapani@counterpane.com>
2006-06-14 20:06 ` Scott Marlowe <smarlowe@g2switchworks.com>
2006-06-15 11:20 ` Robin Iddon <robin@edesix.com>
2008-04-17 19:48 autovacuum? Jesper Krogh <jesper@krogh.cc>
2008-04-18 02:59 ` Re: autovacuum? Pavan Deolasee <pavan.deolasee@gmail.com>
2012-12-30 10:30 autovacuum suhas.basavaraj12 <suhas.b@verse.in>
2012-12-30 11:30 ` Greg Williamson <gwilliamson39@yahoo.com>
2020-10-23 22:02 autovacuum Henry Francisco Garcia Cortez <garcortez@gmail.com>
2020-10-23 22:32 ` David G. Johnston <david.g.johnston@gmail.com>
2023-10-30 11:20 AUTOVACUUM Siraj G <tosiraj.g@gmail.com>
2023-10-30 13:42 ` Re: AUTOVACUUM Ron <ronljohnsonjr@gmail.com>
2023-10-31 04:13 ` Re: AUTOVACUUM Siraj G <tosiraj.g@gmail.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