Re: possible vacuum improvement?

>I do not think we need or want a control table for this; certainly I see
>no need for per-table manual control over this process. There should
>probably be a few knobs in the form of GUC parameters so that the admin
>can control how much overall work the auto-vacuumer does. For instance
>you'd probably like to turn it off when under peak interactive load.

If (auto)vacuum is clever to check that some tables do not need vacuum
there's really no need for that. That brings me to another point, can't the
statistics collector used for that?

For my database I wrote a statistic display program for web-access, and all
the info autovacuum would need is here.
http://mw.sime.com/pgsql.htm

That brings me to another point, is there interest for this
web-statistics-frontend, maybe for /contrib? I found it extremly useful
because it showed up the weak points in my applications.

Best regards,
Mario Weilguni

"Mario Weilguni" <mario(dot)weilguni(at)icomedias(dot)com> writes:
> That brings me to another point, can't the
> statistics collector used for that?

Hmm, that would be a different way of attacking the problem. Not sure
offhand which is better, but it'd surely be worth considering both.

Note that collecting of dead-tuple counts requires input from aborted
transactions as well as successful ones. I don't recall whether the
stats collector currently collects anything from aborted xacts; that
might or might not be a sticky point.

regards, tom lane

> That brings me to another point, is there interest for this
> web-statistics-frontend, maybe for /contrib? I found it extremly useful
> because it showed up the weak points in my applications.

Why not create a project here for it: http://gborg.postgresql.org/

Chris

On Tuesday 03 September 2002 16:24, Tom Lane wrote:
> "Mario Weilguni" <mario(dot)weilguni(at)icomedias(dot)com> writes:
> > That brings me to another point, can't the
> > statistics collector used for that?
>
> Hmm, that would be a different way of attacking the problem. Not sure
> offhand which is better, but it'd surely be worth considering both.
>
> Note that collecting of dead-tuple counts requires input from aborted
> transactions as well as successful ones. I don't recall whether the
> stats collector currently collects anything from aborted xacts; that
> might or might not be a sticky point.

I have been doing some poking around with this item, and I was planning on
using the stats collector to do "intelligent" auto-vacuuming. I was planning
on adding some new columns that account for activity that has taken place
since the last vacuum. The current stats collector shows n_tup_ins,
n_tup_upd and n_tup_del for any given rel, but those numbers have nothing to
do with what has happened since the last vacuum, hence nothing to do with
current status or need for vacuum.

I hope to have something worth showing soon (a week or two). I know that is a
bit slow, but I am new at pg internals and since we are in beta I know this
is a 7.4 item.

FYI, the current stats collector does keep track of inserts, updates and
deletes that are part of a rolled back transaction, as shown in the example
below:

matthew=# create TABLE foo (id serial, name text);
NOTICE: CREATE TABLE will create implicit sequence 'foo_id_seq' for SERIAL
column 'foo.id'
CREATE TABLE
matthew=# select relname,n_tup_ins, n_tup_upd, n_tup_del from
pg_stat_all_tables where relname = 'foo';
relname | n_tup_ins | n_tup_upd | n_tup_del
---------+-----------+-----------+-----------
foo | 0 | 0 | 0
(1 row)

matthew=# INSERT INTO foo (name) VALUES ('asdf');
INSERT 17075 1
matthew=# UPDATE foo SET name='qwert';
UPDATE 1
matthew=# DELETE FROM foo;
DELETE 1
matthew=# select relname,n_tup_ins, n_tup_upd, n_tup_del from
pg_stat_all_tables where relname = 'foo';
relname | n_tup_ins | n_tup_upd | n_tup_del
---------+-----------+-----------+-----------
foo | 1 | 1 | 1
(1 row)

matthew=# begin;
BEGIN
matthew=# INSERT INTO foo (name) VALUES ('asdf');
INSERT 17076 1
matthew=# UPDATE foo SET name='qwert';
UPDATE 1
matthew=# DELETE FROM foo;
DELETE 1
matthew=# rollback;
ROLLBACK
matthew=# select relname,n_tup_ins, n_tup_upd, n_tup_del from
pg_stat_all_tables where relname = 'foo';
relname | n_tup_ins | n_tup_upd | n_tup_del
---------+-----------+-----------+-----------
foo | 2 | 2 | 2
(1 row)

> I have been doing some poking around with this item, and I was
> planning on
> using the stats collector to do "intelligent" auto-vacuuming. I
> was planning
> on adding some new columns that account for activity that has taken place
> since the last vacuum. The current stats collector shows n_tup_ins,
> n_tup_upd and n_tup_del for any given rel, but those numbers have
> nothing to
> do with what has happened since the last vacuum, hence nothing to do with
> current status or need for vacuum.

Postgres 7.3-beta has a new function 'pg_stat_reset()' that you can call to reset the stats collector after a vacuum...

Chris

On Tuesday 03 September 2002 23:47, Christopher Kings-Lynne wrote:
> > I have been doing some poking around with this item, and I was
> > planning on
> > using the stats collector to do "intelligent" auto-vacuuming. I
> > was planning
> > on adding some new columns that account for activity that has taken place
> > since the last vacuum. The current stats collector shows n_tup_ins,
> > n_tup_upd and n_tup_del for any given rel, but those numbers have
> > nothing to
> > do with what has happened since the last vacuum, hence nothing to do with
> > current status or need for vacuum.
>
> Postgres 7.3-beta has a new function 'pg_stat_reset()' that you can call to
> reset the stats collector after a vacuum...

Just my opinion here, but I don't think having autovac constantly resetting
the stats is a good idea, it means that you lose the current stat
functionality when using autovacuum, and also implies that the stats mean
differnet things if autovac is turned on or off.

Am Mittwoch, 4. September 2002 05:44 schrieb Matthew T. OConnor:
> I have been doing some poking around with this item, and I was planning on
> using the stats collector to do "intelligent" auto-vacuuming. I was
> planning on adding some new columns that account for activity that has
> taken place since the last vacuum. The current stats collector shows
> n_tup_ins, n_tup_upd and n_tup_del for any given rel, but those numbers
> have nothing to do with what has happened since the last vacuum, hence
> nothing to do with current status or need for vacuum.

This should be no real problem, extending the table pg_stat_all_tables with 3 fields
"av_n_tup_ins", "av_n_tup_upd", "av_n_tup_del" should do it IMO.

How about counting the number of dead tuples examined and the number of live
tuples returned. As the ratio of dead tuples over live tuples visited
increases the table becomes a candidate for vacuuming.
-regards
richt

> -----Original Message-----
> From: pgsql-hackers-owner(at)postgresql(dot)org
> [mailto:pgsql-hackers-owner(at)postgresql(dot)org]On Behalf Of Tom Lane
> Sent: Tuesday, September 03, 2002 4:25 PM
> To: Mario Weilguni
> Cc: pgsql-hackers(at)postgresql(dot)org
> Subject: Re: [HACKERS] possible vacuum improvement?
>
>
> "Mario Weilguni" <mario(dot)weilguni(at)icomedias(dot)com> writes:
> > That brings me to another point, can't the
> > statistics collector used for that?
>
> Hmm, that would be a different way of attacking the problem. Not sure
> offhand which is better, but it'd surely be worth considering both.
>
> Note that collecting of dead-tuple counts requires input from aborted
> transactions as well as successful ones. I don't recall whether the
> stats collector currently collects anything from aborted xacts; that
> might or might not be a sticky point.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>