Thread: Some vacuum & tuning help

Some vacuum & tuning help

From
Jeff
Date:
I've been trying to search through the archives, but it hasn't been
successful.

We recently upgraded from pg7.0.2 to 7.3.4 and things were happy. I'm
trying to fine tune things to get it running a bit better and I'm trying
to figure out how vacuum output correlates to tuning parameters.

Here's the msot recent vacuum for the "active" table.  It gets a few
hundred updates/inserts a minute constantly throughout the day.

INFO:  Pages 27781: Changed 0, Empty 0; Tup 2451648: Vac 0, Keep 0, UnUsed
1003361.
        Total CPU 2.18s/0.61u sec elapsed 2.78 sec.

I see unused is quite high. This morning I bumped max_fsm_pages to 500000.
If I'm thinking right you want unused and max_fsm to be closish, right?
(Yesterday it was down around.. oh.. 600k?)

I'm thinking vacuum full's may be in order. Which stinks because I was
hoping to do away with the db essentially down for 10 minutes (includes
all the db's on that machine) while it vacuum'd.

The upside is: it is performing great.  During the vacuum analyze I do get
a few multi-second pauses while something occurs. I figured it was a
checkpoint, so I bumped checkpoint_timeout to 30 seconds and wal_buffers
to 128. (I'm just guessing on wal_buffers).

Machine is weenucks 2.2.17 on a dual p3 800, 2gb ram, 18gb drive (mirrored).
If you guys need other info (shared_buffers, etc) I'll be happy to funish
them. but the issue isn't query slowness.. just want to get this thing
oiled).

thanks

--
Jeff Trout <jeff@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/



Re: Some vacuum & tuning help

From
"Shridhar Daithankar"
Date:
On 5 Aug 2003 at 8:09, Jeff wrote:

> I've been trying to search through the archives, but it hasn't been
> successful.
>
> We recently upgraded from pg7.0.2 to 7.3.4 and things were happy. I'm
> trying to fine tune things to get it running a bit better and I'm trying
> to figure out how vacuum output correlates to tuning parameters.
>
> Here's the msot recent vacuum for the "active" table.  It gets a few
> hundred updates/inserts a minute constantly throughout the day.

I would suggest autovacuum daemon which is in CVS contrib  works for 7.3.x as
well.. Or schedule a vacuum analyze every 15 minutes or so..
>
> INFO:  Pages 27781: Changed 0, Empty 0; Tup 2451648: Vac 0, Keep 0, UnUsed
> 1003361.
>         Total CPU 2.18s/0.61u sec elapsed 2.78 sec.
>
> I see unused is quite high. This morning I bumped max_fsm_pages to 500000.
> If I'm thinking right you want unused and max_fsm to be closish, right?
> (Yesterday it was down around.. oh.. 600k?)
>
> I'm thinking vacuum full's may be in order. Which stinks because I was
> hoping to do away with the db essentially down for 10 minutes (includes
> all the db's on that machine) while it vacuum'd.

I think vacuum full is required.

> The upside is: it is performing great.  During the vacuum analyze I do get
> a few multi-second pauses while something occurs. I figured it was a
> checkpoint, so I bumped checkpoint_timeout to 30 seconds and wal_buffers
> to 128. (I'm just guessing on wal_buffers).

If it is couple of tables that are that heavily killed, I would suggest to a
pg_dump, drop table and reload table. That should take less time. Your downtime
might not be 10 minutes but more like 15 say. That's a rough estimate..

>
> Machine is weenucks 2.2.17 on a dual p3 800, 2gb ram, 18gb drive (mirrored).

You mean linux? I guess you need a kernel revision for a long time. How about
2.4.21?

> If you guys need other info (shared_buffers, etc) I'll be happy to funish
> them. but the issue isn't query slowness.. just want to get this thing
> oiled).

See if this helps..

Bye
 Shridhar

--
QOTD:    "I thought I saw a unicorn on the way over, but it was just a    horse with
one of the horns broken off."


Re: Some vacuum & tuning help

From
Peter Childs
Date:
On Tue, 5 Aug 2003, Shridhar Daithankar wrote:

> On 5 Aug 2003 at 8:09, Jeff wrote:
>
> I would suggest autovacuum daemon which is in CVS contrib  works for 7.3.x as
> well.. Or schedule a vacuum analyze every 15 minutes or so..

    I've just got autovacum up and Since we have had a lot of talk
about it recently..... I thought some feed back might be useful.
    It seams to work quite well. But can be rather zelous on its
analysing for the first few hours. Curretly its analysig static (ie
nothigs changed) tables every 10minites. Vacuums seam to be about right.
    I think that many vacuums may be slowing does my database....

Peter Childs


Re: Some vacuum & tuning help

From
Jeff
Date:
On Tue, 5 Aug 2003, Shridhar Daithankar wrote:
>
> I would suggest autovacuum daemon which is in CVS contrib  works for 7.3.x as
> well.. Or schedule a vacuum analyze every 15 minutes or so..
> >

Good Call. I'll give that a whirl and let you know.

> I think vacuum full is required.
>
D'oh.  Would this be a regular thing? I suppose we could do it weekly.

As for the pg_dumping of it. I suppose it would work on this table as it
is only a couple million rows and not terribly big data-wise.  The other
tables in this db are rather big and a load is not fast. (It is about
8GB).

thanks

> You mean linux? I guess you need a kernel revision for a long time. How about
> 2.4.21?
>
Yeah, linux. We're planning on upgrading when we relocate datacenters at
the end of August.  This machine has actually been up for 486 days (We're
hoping to reach linux's uptime wraparound of 496 days :) and the only
reason it went down then was because the power supply failed.  (That can
be read: pg7.0.2 had over a year of uptime. lets hope 7.3 works as good :)


--
Jeff Trout <jeff@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/



Re: Some vacuum & tuning help

From
"Shridhar Daithankar"
Date:
On 5 Aug 2003 at 9:18, Jeff wrote:
> As for the pg_dumping of it. I suppose it would work on this table as it
> is only a couple million rows and not terribly big data-wise.  The other
> tables in this db are rather big and a load is not fast. (It is about
> 8GB).

You need to dump only those table which has unusualy high unused stats. If that
is a small table, dump/reload it would be far faster than vacuum.. For others
vacuum analyse should do..

> > You mean linux? I guess you need a kernel revision for a long time. How about
> > 2.4.21?
> >
> Yeah, linux. We're planning on upgrading when we relocate datacenters at
> the end of August.  This machine has actually been up for 486 days (We're
> hoping to reach linux's uptime wraparound of 496 days :) and the only
> reason it went down then was because the power supply failed.  (That can
> be read: pg7.0.2 had over a year of uptime. lets hope 7.3 works as good :)

Good to know that. AFAIK, the 496 wraparound is fixed in 2.6. So that won't be
a complaint any longer..

Bye
 Shridhar

--
Gravity:    What you get when you eat too much and too fast.


Re: Some vacuum & tuning help

From
"Shridhar Daithankar"
Date:
On 5 Aug 2003 at 14:15, Peter Childs wrote:

> On Tue, 5 Aug 2003, Shridhar Daithankar wrote:
>
> > On 5 Aug 2003 at 8:09, Jeff wrote:
> >
> > I would suggest autovacuum daemon which is in CVS contrib  works for 7.3.x as
> > well.. Or schedule a vacuum analyze every 15 minutes or so..
>
>     I've just got autovacum up and Since we have had a lot of talk
> about it recently..... I thought some feed back might be useful.
>     It seams to work quite well. But can be rather zelous on its
> analysing for the first few hours. Curretly its analysig static (ie
> nothigs changed) tables every 10minites. Vacuums seam to be about right.
>     I think that many vacuums may be slowing does my database....

IIRC there is per operation threshold. If update threshold is 5% and table is
2% updatedit, then it should not look at it at all.

It's worth mentioning that you should start auto vacuum daemon on a clean
database. i.e. no pending vacuum. It is not supposed to start with a database
which has lots of clean up pending. The essence of auto vacuum daemon is to
maintain a clean database in clean state..

I agree, specifying per table thresholds would be good in autovacuum..


Bye
 Shridhar

--
WYSIWYG:    What You See Is What You Get.


Re: Some vacuum & tuning help

From
Peter Childs
Date:
On Tue, 5 Aug 2003, Shridhar Daithankar wrote:

> On 5 Aug 2003 at 14:15, Peter Childs wrote:
>
> > On Tue, 5 Aug 2003, Shridhar Daithankar wrote:
> >
> > > On 5 Aug 2003 at 8:09, Jeff wrote:
> > >
> > > I would suggest autovacuum daemon which is in CVS contrib  works for 7.3.x as
> > > well.. Or schedule a vacuum analyze every 15 minutes or so..
> >
> >     I've just got autovacum up and Since we have had a lot of talk
> > about it recently..... I thought some feed back might be useful.
> >     It seams to work quite well. But can be rather zelous on its
> > analysing for the first few hours. Curretly its analysig static (ie
> > nothigs changed) tables every 10minites. Vacuums seam to be about right.
> >     I think that many vacuums may be slowing does my database....

    Sorry typo big time

it should read

"I think that may analysing may may be slowing down my database.

>
> IIRC there is per operation threshold. If update threshold is 5% and table is
> 2% updatedit, then it should not look at it at all.

    I left it with debug over night and it improved to that after 5
hours. switch the debug down (to 1) this morning and it has not settled
down yet.

>
> It's worth mentioning that you should start auto vacuum daemon on a clean
> database. i.e. no pending vacuum. It is not supposed to start with a database
> which has lots of clean up pending. The essence of auto vacuum daemon is to
> maintain a clean database in clean state..
>
> I agree, specifying per table thresholds would be good in autovacuum..

Peter  Childs


Re: Some vacuum & tuning help

From
Christopher Browne
Date:
Shridhar Daithankar wrote:
> I agree, specifying per table thresholds would be good in autovacuum..

Which begs the question of what the future direction is for pg_autovacuum.

There would be some merit to having pg_autovacuum throw in some tables
in which to store persistent information, and at that point, it would
make sense to add some flags to support the respective notions that:

 -> Some tables should _never_ be touched;

 -> Some tables might get "reset" to indicate that they should be
    considered as having been recently vacuumed, or perhaps that they
    badly need vacuuming;

 -> As you suggest, per-table thresholds;

 -> pg_autovacuum would know when tables were last vacuumed by
    it...

 -> You could record vacuum times to tell pg_autovacuum that you
    vacuumed something "behind its back."

 -> If the system queued up proposed vacuums by having a "queue"
    table, you could request that pg_autovacuum do a vacuum on a
    particular table at the next opportunity.

All well and interesting stuff that could be worth implementing.

But the usual talk has been about ultimately integrating the
functionality into the backend, making it fairly futile to enhance
pg_autovacuum terribly much.

Unfortunately, the "integrate into the backend" thing has long seemed
"just around the corner."  I think we should either:
 a) Decide to enhance pg_autovacuum, or
 b) Not.

In view of how long the "better answers" seem to be taking to emerge,
I think it makes sense to add functionality to pg_autovacuum.
--
output = reverse("ofni.smrytrebil" "@" "enworbbc")
<http://dev6.int.libertyrms.com/>
Christopher Browne
(416) 646 3304 x124 (land)

Re: Some vacuum & tuning help

From
Tom Lane
Date:
Jeff <threshar@torgo.978.org> writes:
> Here's the msot recent vacuum for the "active" table.  It gets a few
> hundred updates/inserts a minute constantly throughout the day.

> INFO:  Pages 27781: Changed 0, Empty 0; Tup 2451648: Vac 0, Keep 0, UnUsed
> 1003361.
>         Total CPU 2.18s/0.61u sec elapsed 2.78 sec.

> I see unused is quite high. This morning I bumped max_fsm_pages to 500000.
> If I'm thinking right you want unused and max_fsm to be closish, right?

No, they're unrelated.  UnUsed is the number of currently-unused tuple
pointers in page headers, whereas the FSM parameters are measured in
pages.  30000 FSM slots would be more than enough for this table.

The above numbers don't seem terribly unreasonable to me, although
probably UnUsed would be smaller if you'd been vacuuming more often.
If you see UnUsed continuing to increase then you definitely ought to
shorten the intervacuum time.

VACUUM FULL does not reclaim unused tuple pointers AFAIR, except where
it is able to release entire pages at the end of the relation.  So if
you really wanted to get back down to nil UnUsed, you'd need to do a
dump and reload of the table (or near equivalent, such as CLUSTER).
Not sure it's worth the trouble.

            regards, tom lane

Re: Some vacuum & tuning help

From
"Shridhar Daithankar"
Date:
On 5 Aug 2003 at 10:29, Christopher Browne wrote:

> Shridhar Daithankar wrote:
> > I agree, specifying per table thresholds would be good in autovacuum..
>
> Which begs the question of what the future direction is for pg_autovacuum.
>
> There would be some merit to having pg_autovacuum throw in some tables
> in which to store persistent information, and at that point, it would
> make sense to add some flags to support the respective notions that:

Well, the C++ version I wrote quite a while back, which resides on gborg and
unmaintained, did that. It was considered as table pollution. However whenever
autovacuum stuff goes in backend as such, it is going to need a catalogue.

>  -> Some tables should _never_ be touched;

That can be determined runtime from stats. Not required as a special feature
IMHO..

>
>  -> Some tables might get "reset" to indicate that they should be
>     considered as having been recently vacuumed, or perhaps that they
>     badly need vacuuming;

Well, stats collector takes care of that. Autovacuum daemon reads that
statistics, maintain  a periodic snapshot of the same to determine whether or
not it needs to vacuum.

Why it crawls for a dirty database is as follows. Autovauum daemon starts, read
statistics, sets it as base level and let a cycle pass, which is typically few
minutes. When it goes again, it finds that lots of things are modified and need
vacuum and so it triggers vacuum.

Now vacuum goes on cleaning entire table which might be days job continously
postponed some one reason or another. Oops.. your database is on it's knees..


>  -> As you suggest, per-table thresholds;

I would rather put it in terms of pages. If any table wastes 100 pages each, it
deserves a vacuum..


>  -> pg_autovacuum would know when tables were last vacuumed by
>     it...

If you maintain a table in database, there are lot of things you can maintain.
And you need to connect to database anyway to fire vacuum..


>  -> You could record vacuum times to tell pg_autovacuum that you
>     vacuumed something "behind its back."

It should notice..

>  -> If the system queued up proposed vacuums by having a "queue"
>     table, you could request that pg_autovacuum do a vacuum on a
>     particular table at the next opportunity.

That won't ever happen if autovacuum is constantly running..

> Unfortunately, the "integrate into the backend" thing has long seemed
> "just around the corner."  I think we should either:
>  a) Decide to enhance pg_autovacuum, or
>  b) Not.

In fact, I would say that after we have autovacuum, we should not integrate it.
It is a very handy tool of tighting a database. Other database go other way
round. They develop maintance functionality built in and then create tool on
top of it. Here we have it already done.

It's just that it should be triggered by default. That would rock..

Bye
 Shridhar

--
Bubble Memory, n.:    A derogatory term, usually referring to a person's intelligence.    See also "vacuum tube".


Re: Some vacuum & tuning help

From
"Matthew T. O'Connor"
Date:
From: "Christopher Browne" <cbbrowne@libertyrms.info>

> Shridhar Daithankar wrote:
> > I agree, specifying per table thresholds would be good in autovacuum..
>
> Which begs the question of what the future direction is for pg_autovacuum.

This is a good question.

> There would be some merit to having pg_autovacuum throw in some tables
> in which to store persistent information

As long as pg_autovacuum is either a contrib module, or not integrated into
the backend, we can't do this.  I don't think we should require that tables
are added to your database in order to run pg_autovacuum, I have thought
that a "helper table" could be used, this table, if found by pg_autovacuum
would use it for per table defaults, exclusion list etc....  That way
pg_autovacuum can run without a polluted database, or can be tuned.

If pg_autovacuum in made official, moves out of contrib and becomes a core
tool, then we can either add columns to some system catalogs to track this
information or add a new system table.

> All well and interesting stuff that could be worth implementing.
>
> But the usual talk has been about ultimately integrating the
> functionality into the backend, making it fairly futile to enhance
> pg_autovacuum terribly much.
>
> Unfortunately, the "integrate into the backend" thing has long seemed
> "just around the corner."  I think we should either:
>  a) Decide to enhance pg_autovacuum, or
>  b) Not.

I have been talking about "integraging it into the backend" for a while, and
I used to think it was "just around the corner"  unfortunately, work
schedule and my C skills have prevented me from getting anything useful
working.  If you would like to work on it, I would help as much as possible.

I chose to leave pg_autovacuum simple and not add too many features because
the core team has said that it needs to be integrated into the backend
before it can be considered a core tool.

ps, please cc me as I'm not subscribed to the list.


Re: Some vacuum & tuning help

From
"Matthew T. O'Connor"
Date:
From: "Shridhar Daithankar" <shridhar_daithankar@persistent.co.in>
> On 5 Aug 2003 at 10:29, Christopher Browne wrote:
>
> > Shridhar Daithankar wrote:
> > There would be some merit to having pg_autovacuum throw in some tables
> > in which to store persistent information,
>
> Well, the C++ version I wrote quite a while back, which resides on gborg
and
> unmaintained, did that. It was considered as table pollution. However
whenever
> autovacuum stuff goes in backend as such, it is going to need a catalogue.

right, I think there is a distinction between adding a system catalogue
needed for core functionality, and requiring a user to put a table in the
users name space of their database just to run a utility.  As I mentioned in
my other email, I do think that a non-required helper table could be a good
idea, the question is should we do this considering autovacuum should be
integrated into the backend at which point pg_autovacuum will be scrapped.

> >  -> Some tables should _never_ be touched;
>
> That can be determined runtime from stats. Not required as a special
feature
> IMHO..
>
> >
> >  -> Some tables might get "reset" to indicate that they should be
> >     considered as having been recently vacuumed, or perhaps that they
> >     badly need vacuuming;
>
> Well, stats collector takes care of that. Autovacuum daemon reads that
> statistics, maintain  a periodic snapshot of the same to determine whether
or
> not it needs to vacuum.

Actually I don't think that pg_autovacuum will notice.  The stats that it
watches no nothing about when a table is vacuumed outside of pg_autovacuum.
I agree this is a deficiency, but I don't know how to get that information
without being part of the backend.

> Why it crawls for a dirty database is as follows. Autovauum daemon starts,
read
> statistics, sets it as base level and let a cycle pass, which is typically
few
> minutes. When it goes again, it finds that lots of things are modified and
need
> vacuum and so it triggers vacuum.
>
> Now vacuum goes on cleaning entire table which might be days job
continously
> postponed some one reason or another. Oops.. your database is on it's
knees..

If one table takes days (or even hours) to vacuum, then most probably it
requires *a lot* of activity before pg_autovacuum will try to vacuum the
table.  The thresholds are based on two factors, a base value (default =
1,000), and a multiplier (default = 2) of the total number of tuples.  So
using the default pg_autovacuum settings, a table with 1,000,000 rows will
not be vacuumed until the number of rows updated or deleted = 2,001,000.
So, a table shouldn't be vacuumed until it really needs it.  This setup
works well since a small table of say 100 rows, will be updated every 1,200
(updates or deletes).
>
> >  -> As you suggest, per-table thresholds;
>
> I would rather put it in terms of pages. If any table wastes 100 pages
each, it
> deserves a vacuum..

unfortunately I don't know of an efficient method of looking at how many
pages have free space without running vacuum or without using the
pgstattuple contrib module which in my testing took about 90% as long to run
as vacuum.

> >  -> pg_autovacuum would know when tables were last vacuumed by
> >     it...

pg_autovacuum already does this, but the data does not persist through
pg_autovacuum restarts.

> If you maintain a table in database, there are lot of things you can
maintain.
> And you need to connect to database anyway to fire vacuum..
>
> >  -> You could record vacuum times to tell pg_autovacuum that you
> >     vacuumed something "behind its back."
>
> It should notice..

I don't think it does.

> >  -> If the system queued up proposed vacuums by having a "queue"
> >     table, you could request that pg_autovacuum do a vacuum on a
> >     particular table at the next opportunity.

That would be a design changes as right now pg_autovacuum doesn't keep a
list of tables to vacuum at all, it just decides to vacuum or not vacuum a
table as it loops through the database.

> > Unfortunately, the "integrate into the backend" thing has long seemed
> > "just around the corner."  I think we should either:
> >  a) Decide to enhance pg_autovacuum, or
> >  b) Not.

I have been of the opinion to not enhance pg_autovacuum because it needs to
be intgrated, and enhancing it will only put that off.  Also, I think many
of the real enhancements can only come from being integrated (using the FSM
to make decisions, keepting track of external vacuums, modifying system
catalogs to keep autovacuum information etc...)

> In fact, I would say that after we have autovacuum, we should not
integrate it.
> It is a very handy tool of tighting a database. Other database go other
way
> round. They develop maintance functionality built in and then create tool
on
> top of it. Here we have it already done.

I'm not sure I understand your point.

> It's just that it should be triggered by default. That would rock..


I agree that if pg_autovacuum becomes a core tool (not contrib and not
integrated into backend) then pg_ctl should fire it up and kill it
automatically.


Re: Some vacuum & tuning help

From
Tom Lane
Date:
"Matthew T. O'Connor" <matthew@zeut.net> writes:
> I chose to leave pg_autovacuum simple and not add too many features because
> the core team has said that it needs to be integrated into the backend
> before it can be considered a core tool.

I think actually it makes plenty of sense to enhance pg_autovacuum while
it's still contrib stuff.  My guess is it'll be much less painful to
whack it around in minor or major ways while it's standalone code.
Once it's integrated in the backend, making significant changes will be
harder and more ticklish.  So, now is precisely the time to be
experimenting to find out what works well and what features are needed.

            regards, tom lane

Re: Some vacuum & tuning help

From
"Matthew T. O'Connor"
Date:
From: "Tom Lane" <tgl@sss.pgh.pa.us>
> "Matthew T. O'Connor" <matthew@zeut.net> writes:
> > I chose to leave pg_autovacuum simple and not add too many features
because
> > the core team has said that it needs to be integrated into the backend
> > before it can be considered a core tool.
>
> I think actually it makes plenty of sense to enhance pg_autovacuum while
> it's still contrib stuff.  My guess is it'll be much less painful to
> whack it around in minor or major ways while it's standalone code.
> Once it's integrated in the backend, making significant changes will be
> harder and more ticklish.  So, now is precisely the time to be
> experimenting to find out what works well and what features are needed.

Fair point, my only concern is that a backend integrated pg_autovacuum would
be radically different from the current libpq based client application.
When integrated into the backend you have access to a lot of information
that you don't have access to as a client.  I know one goal I have for the
backend version is to be based on the FSM and not require the stats
collector since it has a measurable negative effect on performance.

But in the more general sense of learning what features people want
(exclusion lists, per table defaults etc) I agree the current version is a
sufficient testing ground.


Re: Some vacuum & tuning help

From
"Matthew T. O'Connor"
Date:
From: "Tom Lane" <tgl@sss.pgh.pa.us>
> "Matthew T. O'Connor" <matthew@zeut.net> writes:
> So, now is precisely the time to be experimenting to find out what works
well and what features are needed.

Another quick question while I have your attention :-)

Since pg_autovaccum is a contrib module does that mean I can make functional
changes that will be included in point release of 7.4?


Re: Some vacuum & tuning help

From
Tom Lane
Date:
"Matthew T. O'Connor" <matthew@zeut.net> writes:
> Since pg_autovaccum is a contrib module does that mean I can make functional
> changes that will be included in point release of 7.4?

Well, the bar is lower for contrib stuff than for core, but you'd better
get such changes in PDQ, I'd say ...

            regards, tom lane

Re: Some vacuum & tuning help

From
Bruce Momjian
Date:
Tom Lane wrote:
> "Matthew T. O'Connor" <matthew@zeut.net> writes:
> > Since pg_autovaccum is a contrib module does that mean I can make functional
> > changes that will be included in point release of 7.4?
>
> Well, the bar is lower for contrib stuff than for core, but you'd better
> get such changes in PDQ, I'd say ...

The contrib stuff is usually at the control of the author, so you can
make changes relatively late.  However, the later the changes, the less
testing they get, but the decision is mostly yours, rather than core.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Some vacuum & tuning help

From
Christopher Browne
Date:
Matthew T. O'Connor wrote:
> Fair point, my only concern is that a backend integrated
> pg_autovacuum would be radically different from the current libpq
> based client application.

Unfortunately, a "configurable-via-tables" pg_autovacuum is also going
to be quite different from the current "unconfigurable" version.

If we were to make it configurable, I would suggest doing so via
specifying a database and schema into which it would then insert a set
of tables to provide whatever information was considered worth
'fiddling' with.

But at that point, it makes sense to add in quite a bit of
"configurable" behaviour, such as:

 -> Specifying that certain tables should _never_ be automatically
    vacuumed.

 -> Establishing a "queue" of tables that pg_autovacuum plans to
    vacuum, so that users could add in desired vacuums ("after the
    other stuff being handled, force in a vacuum of app_table_foo").
    That way, vacuums can be 'forced in' without introducing the
    possibility that multiple vacuums might be done at once...

 -> Making information about what vacuums have been done/planned
    persistent across runs of pg_autovacuum, and even across
    shutdowns of the DBMS.

This changes behaviour enough that I'm not sure it's the same
"program" as the unconfigurable version.  Almost every option would be
substantially affected by the logic:

 if (CONFIG_DATA_IN_DB) {
   /* Logic path that uses data in Vacuum Schema */
 } else {
   /* More banal logic */
 }

If I can store configuration in the database, then I'd like to also
make up a view or two, and possibly even base the logic used on views
that combine configuration tables with system views.  In effect, that
makes for a _third_ radically different option.
--
output = reverse("ofni.smrytrebil" "@" "enworbbc")
<http://dev6.int.libertyrms.com/>
Christopher Browne
(416) 646 3304 x124 (land)

Re: Some vacuum & tuning help

From
"Christopher Kings-Lynne"
Date:
Try the pg_autovacuum daemon in CVS contrib dir.  It works fine with 7.3.

Chris

----- Original Message -----
From: "Jeff" <threshar@torgo.978.org>
To: <pgsql-performance@postgresql.org>
Sent: Tuesday, August 05, 2003 8:09 PM
Subject: [PERFORM] Some vacuum & tuning help


> I've been trying to search through the archives, but it hasn't been
> successful.
>
> We recently upgraded from pg7.0.2 to 7.3.4 and things were happy. I'm
> trying to fine tune things to get it running a bit better and I'm trying
> to figure out how vacuum output correlates to tuning parameters.
>
> Here's the msot recent vacuum for the "active" table.  It gets a few
> hundred updates/inserts a minute constantly throughout the day.
>
> INFO:  Pages 27781: Changed 0, Empty 0; Tup 2451648: Vac 0, Keep 0, UnUsed
> 1003361.
>         Total CPU 2.18s/0.61u sec elapsed 2.78 sec.
>
> I see unused is quite high. This morning I bumped max_fsm_pages to 500000.
> If I'm thinking right you want unused and max_fsm to be closish, right?
> (Yesterday it was down around.. oh.. 600k?)
>
> I'm thinking vacuum full's may be in order. Which stinks because I was
> hoping to do away with the db essentially down for 10 minutes (includes
> all the db's on that machine) while it vacuum'd.
>
> The upside is: it is performing great.  During the vacuum analyze I do get
> a few multi-second pauses while something occurs. I figured it was a
> checkpoint, so I bumped checkpoint_timeout to 30 seconds and wal_buffers
> to 128. (I'm just guessing on wal_buffers).
>
> Machine is weenucks 2.2.17 on a dual p3 800, 2gb ram, 18gb drive
(mirrored).
> If you guys need other info (shared_buffers, etc) I'll be happy to funish
> them. but the issue isn't query slowness.. just want to get this thing
> oiled).
>
> thanks
>
> --
> Jeff Trout <jeff@jefftrout.com>
> http://www.jefftrout.com/
> http://www.stuarthamm.net/
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org
>


Re: Some vacuum & tuning help

From
"Matthew T. O'Connor"
Date:
On Tue, 2003-08-05 at 12:49, Bruce Momjian wrote:
> > Well, the bar is lower for contrib stuff than for core, but you'd better
> > get such changes in PDQ, I'd say ...
>
> The contrib stuff is usually at the control of the author, so you can
> make changes relatively late.  However, the later the changes, the less
> testing they get, but the decision is mostly yours, rather than core.

Well I don't have anything in the hopper right now, so there is little
chance anything would be ready before the release.  My really question
was can I make large changes to a contrib module to a point release,
meaning, 7.4.0 will have what is in beta, but 7.4.1 would have a much
improved version.  Does that sound possible? Or too radical for a point
release?


Re: Some vacuum & tuning help

From
Bruce Momjian
Date:
Matthew T. O'Connor wrote:
> On Tue, 2003-08-05 at 12:49, Bruce Momjian wrote:
> > > Well, the bar is lower for contrib stuff than for core, but you'd better
> > > get such changes in PDQ, I'd say ...
> >
> > The contrib stuff is usually at the control of the author, so you can
> > make changes relatively late.  However, the later the changes, the less
> > testing they get, but the decision is mostly yours, rather than core.
>
> Well I don't have anything in the hopper right now, so there is little
> chance anything would be ready before the release.  My really question
> was can I make large changes to a contrib module to a point release,
> meaning, 7.4.0 will have what is in beta, but 7.4.1 would have a much
> improved version.  Does that sound possible? Or too radical for a point
> release?

Yes, that is possible, but you should try to get lots of testers because
there is little testing in minor releases.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Some vacuum & tuning help

From
"Matthew T. O'Connor"
Date:
On Tue, 2003-08-05 at 17:40, Christopher Browne wrote:
> Unfortunately, a "configurable-via-tables" pg_autovacuum is also going
> to be quite different from the current "unconfigurable" version.

true, however I would like to preserve the "unconfigured" functionality
so that it can be run against a totally unmodified database cluster.  If
it finds configuration information on the server then it uses it,
otherwise it just acts as it does now.

> But at that point, it makes sense to add in quite a bit of
> "configurable" behaviour, such as:
>
>  -> Specifying that certain tables should _never_ be automatically
>     vacuumed.

agreed

>  -> Establishing a "queue" of tables that pg_autovacuum plans to
>     vacuum, so that users could add in desired vacuums ("after the
>     other stuff being handled, force in a vacuum of app_table_foo").
>     That way, vacuums can be 'forced in' without introducing the
>     possibility that multiple vacuums might be done at once...

makes sense.

>  -> Making information about what vacuums have been done/planned
>     persistent across runs of pg_autovacuum, and even across
>     shutdowns of the DBMS.

good.

> This changes behaviour enough that I'm not sure it's the same
> "program" as the unconfigurable version.  Almost every option would be
> substantially affected by the logic:
>
>  if (CONFIG_DATA_IN_DB) {
>    /* Logic path that uses data in Vacuum Schema */
>  } else {
>    /* More banal logic */
>  }

I'm not so sure it's that different.  In either case we are going to
have a threshold and decide to vacuum based on that threshold.  The
change is only that the data would be persistent, and could be
customized on a per table basis.  The logic only really changes if
running unconfigured uses different data than the configured version,
which I don't see as being proposed.

> If I can store configuration in the database, then I'd like to also
> make up a view or two, and possibly even base the logic used on views
> that combine configuration tables with system views.  In effect, that
> makes for a _third_ radically different option.

Not sure I see what all you are implying here.  Please expand on this if
you deem it worthy.


I guess I'll start coding again.  I'll send an email to the hackers list
tomorrow evening with as much info / design as I can think of.

Matthew


Re: Some vacuum & tuning help

From
"Christopher Kings-Lynne"
Date:
> On Tue, 2003-08-05 at 17:40, Christopher Browne wrote:
> > Unfortunately, a "configurable-via-tables" pg_autovacuum is also going
> > to be quite different from the current "unconfigurable" version.

You don't need to create actual tables - just use 'virtual' tables, like the
pg_settings one.  That's all based off a set-returning-function.  You can
use updates and inserts to manipulate internal data structures or
something...

Chris


Re: Some vacuum & tuning help

From
Tom Lane
Date:
"Matthew T. O'Connor" <matthew@zeut.net> writes:
> ... My really question
> was can I make large changes to a contrib module to a point release,
> meaning, 7.4.0 will have what is in beta, but 7.4.1 would have a much
> improved version.  Does that sound possible?

For core code, the answer would be a big NYET.  We do not do feature
additions in point releases, only bug fixes.  While contrib code is more
under the author's control than the core committee's control, I'd still
say that you'd be making a big mistake to not follow that basic
guideline.  People expect release x.y.z+1 to be the same as x.y.z except
for bug fixes.  Introducing any new bugs into x.y.z+1 would cause a
large loss in your credibility.

(speaking as one who's introduced new bugs into a point-release
recently, and is still embarrassed about it, even though the intent
was only to fix older bugs...)

            regards, tom lane

Re: Some vacuum & tuning help

From
Neil Conway
Date:
On Wed, Aug 06, 2003 at 12:45:34AM -0400, Tom Lane wrote:
> For core code, the answer would be a big NYET.  We do not do feature
> additions in point releases, only bug fixes.  While contrib code is more
> under the author's control than the core committee's control, I'd still
> say that you'd be making a big mistake to not follow that basic
> guideline.  People expect release x.y.z+1 to be the same as x.y.z except
> for bug fixes.  Introducing any new bugs into x.y.z+1 would cause a
> large loss in your credibility.

... and since contrib packages are distributed along with PG, it would
also be a loss to PG's credibility. IMHO, core should disallow feature
additions in point releases for contrib modules, as well as the core
code, except for very unusual situations. If contrib authors don't like
this facet of our release engineering process, they can always
distribute their code via some other outlet (gborg, SF, etc.).

-Neil


Re: Some vacuum & tuning help

From
"Matthew T. O'Connor"
Date:
On Wed, 2003-08-06 at 00:45, Tom Lane wrote:
> For core code, the answer would be a big NYET.  We do not do feature
> additions in point releases, only bug fixes.  While contrib code is more
> under the author's control than the core committee's control, I'd still
> say that you'd be making a big mistake to not follow that basic
> guideline.  People expect release x.y.z+1 to be the same as x.y.z except
> for bug fixes.  Introducing any new bugs into x.y.z+1 would cause a
> large loss in your credibility.
>
> (speaking as one who's introduced new bugs into a point-release
> recently, and is still embarrassed about it, even though the intent
> was only to fix older bugs...)

Right, OK, that is basically the answer I was expecting, but thought I
would ask.

Matthew