Re: patch review, please: Autovacuum/Vacuum times via stats.

Lists: pgsql-hackerspgsql-patches
From: "Larry Rosenman" <lrosenman(at)pervasive(dot)com>
To: <pgsql-hackers(at)postgresql(dot)org>
Subject: patch review, please: Autovacuum/Vacuum times via stats.
Date: 2006-05-02 18:18:03
Message-ID: F6616E0E81AC0841B1F9DD252F7C4B55041A52@ausmaildd.aus.pervasive.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers pgsql-patches

Greetings,
I've got a patch to be reviewed for having the stats system keep
track of the last
time a table was vacuumed or analyzed either by the user or via
AutoVacuum.

The patch is at:
http://www.lerctr.org/~ler/pg-dev/vacuum-autovacuum-times-stats.diff

I'd appreciate a full review, it includes docs as well.

Thanks!

LER

--
Larry Rosenman
Database Support Engineer

PERVASIVE SOFTWARE. INC.
12365B RIATA TRACE PKWY
3015
AUSTIN TX 78727-6531

Tel: 512.231.6173
Fax: 512.231.6597
Email: Larry(dot)Rosenman(at)pervasive(dot)com
Web: www.pervasive.com


From: "Larry Rosenman" <ler(at)lerctr(dot)org>
To: <pgsql-hackers(at)postgresql(dot)org>
Cc: <pgsql-patches(at)postgresql(dot)org>
Subject: Re: [HACKERS] patch review, please: Autovacuum/Vacuum times via stats.
Date: 2006-05-02 18:42:54
Message-ID: 007b01c66e18$39a51b30$0202fea9@aus.pervasive.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers pgsql-patches

Larry Rosenman wrote:
> Greetings,
> I've got a patch to be reviewed for having the stats system keep
> track of the last
> time a table was vacuumed or analyzed either by the user or via
> AutoVacuum.
>
> The patch is at:
> http://www.lerctr.org/~ler/pg-dev/vacuum-autovacuum-times-stats.diff
>
> I'd appreciate a full review, it includes docs as well.
>
> Thanks!
>
> LER

I just replaced this one with one that actually bumps catversion.

LER

pgsql-patches added as well.

I think this one is applyable if the powers that be want to.

Comments/criticism welcome.

LER

--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 512-248-2683 E-Mail: ler(at)lerctr(dot)org
US Mail: 430 Valona Loop, Round Rock, TX 78681-3893


From: Martijn van Oosterhout <kleptog(at)svana(dot)org>
To: Larry Rosenman <lrosenman(at)pervasive(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: patch review, please: Autovacuum/Vacuum times via stats.
Date: 2006-05-02 19:19:22
Message-ID: 20060502191922.GE13702@svana.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers pgsql-patches

Looks good to me. Short and sweet.

On Tue, May 02, 2006 at 01:18:03PM -0500, Larry Rosenman wrote:
> Greetings,
> I've got a patch to be reviewed for having the stats system keep
> track of the last
> time a table was vacuumed or analyzed either by the user or via
> AutoVacuum.
>
> The patch is at:
> http://www.lerctr.org/~ler/pg-dev/vacuum-autovacuum-times-stats.diff
>
> I'd appreciate a full review, it includes docs as well.

--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.


From: "Jim C(dot) Nasby" <jnasby(at)pervasive(dot)com>
To: Martijn van Oosterhout <kleptog(at)svana(dot)org>
Cc: Larry Rosenman <lrosenman(at)pervasive(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: patch review, please: Autovacuum/Vacuum times via stats.
Date: 2006-05-02 22:49:33
Message-ID: 20060502224933.GK97354@pervasive.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers pgsql-patches

Back in the discussion of this someone had mentioned capturing all the
info that you'd get from a vacuum verbose; dead tuples, etc. What do
people think about that? In particular I think it'd be handy to know how
many pages vacuum wanted in the FSM vs. how many it got; this would make
it much easier for people to ensure that the FSM is large enough. Using
the functions that let you query the FSM won't work because they can't
tell you if there are pages that should have been in the FSM but didn't
make it in.

On Tue, May 02, 2006 at 09:19:22PM +0200, Martijn van Oosterhout wrote:
> Looks good to me. Short and sweet.
>
> On Tue, May 02, 2006 at 01:18:03PM -0500, Larry Rosenman wrote:
> > Greetings,
> > I've got a patch to be reviewed for having the stats system keep
> > track of the last
> > time a table was vacuumed or analyzed either by the user or via
> > AutoVacuum.
> >
> > The patch is at:
> > http://www.lerctr.org/~ler/pg-dev/vacuum-autovacuum-times-stats.diff
> >
> > I'd appreciate a full review, it includes docs as well.
>
> --
> Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> > From each according to his ability. To each according to his ability to litigate.

--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461


From: Martijn van Oosterhout <kleptog(at)svana(dot)org>
To: "Jim C(dot) Nasby" <jnasby(at)pervasive(dot)com>
Cc: Larry Rosenman <lrosenman(at)pervasive(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: patch review, please: Autovacuum/Vacuum times via stats.
Date: 2006-05-03 13:54:57
Message-ID: 20060503135457.GB27354@svana.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers pgsql-patches

On Tue, May 02, 2006 at 05:49:33PM -0500, Jim C. Nasby wrote:
> Back in the discussion of this someone had mentioned capturing all the
> info that you'd get from a vacuum verbose; dead tuples, etc. What do
> people think about that? In particular I think it'd be handy to know how
> many pages vacuum wanted in the FSM vs. how many it got; this would make
> it much easier for people to ensure that the FSM is large enough. Using
> the functions that let you query the FSM won't work because they can't
> tell you if there are pages that should have been in the FSM but didn't
> make it in.

That's a good idea too, but in that case I'd vote for putting it into a
seperate table/view and not with the stats relating to number of seq
scans for example.
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.


From: "Jim C(dot) Nasby" <jnasby(at)pervasive(dot)com>
To: Martijn van Oosterhout <kleptog(at)svana(dot)org>
Cc: Larry Rosenman <lrosenman(at)pervasive(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: patch review, please: Autovacuum/Vacuum times via stats.
Date: 2006-05-03 17:47:55
Message-ID: 20060503174755.GV97354@pervasive.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers pgsql-patches

On Wed, May 03, 2006 at 03:54:57PM +0200, Martijn van Oosterhout wrote:
> On Tue, May 02, 2006 at 05:49:33PM -0500, Jim C. Nasby wrote:
> > Back in the discussion of this someone had mentioned capturing all the
> > info that you'd get from a vacuum verbose; dead tuples, etc. What do
> > people think about that? In particular I think it'd be handy to know how
> > many pages vacuum wanted in the FSM vs. how many it got; this would make
> > it much easier for people to ensure that the FSM is large enough. Using
> > the functions that let you query the FSM won't work because they can't
> > tell you if there are pages that should have been in the FSM but didn't
> > make it in.
>
> That's a good idea too, but in that case I'd vote for putting it into a
> seperate table/view and not with the stats relating to number of seq
> scans for example.

Agreed; it doesn't really make much sense to me to be putting info about
vacuum in the stat view anyway; ISTM it should be a stand-alone view.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461


From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Martijn van Oosterhout <kleptog(at)svana(dot)org>
Cc: "Jim C(dot) Nasby" <jnasby(at)pervasive(dot)com>, Larry Rosenman <lrosenman(at)pervasive(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: patch review, please: Autovacuum/Vacuum times via stats.
Date: 2006-05-03 18:25:54
Message-ID: 18124.1146680754@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers pgsql-patches

> On Tue, May 02, 2006 at 05:49:33PM -0500, Jim C. Nasby wrote:
>> Back in the discussion of this someone had mentioned capturing all the
>> info that you'd get from a vacuum verbose; dead tuples, etc. What do
>> people think about that? In particular I think it'd be handy to know how
>> many pages vacuum wanted in the FSM vs. how many it got; this would make
>> it much easier for people to ensure that the FSM is large enough.

Isn't this already dealt with by contrib/pg_freespacemap?

regards, tom lane


From: "Jim C(dot) Nasby" <jnasby(at)pervasive(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Martijn van Oosterhout <kleptog(at)svana(dot)org>, Larry Rosenman <lrosenman(at)pervasive(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: patch review, please: Autovacuum/Vacuum times via stats.
Date: 2006-05-04 17:05:13
Message-ID: 20060504170513.GH97354@pervasive.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers pgsql-patches

On Wed, May 03, 2006 at 02:25:54PM -0400, Tom Lane wrote:
> > On Tue, May 02, 2006 at 05:49:33PM -0500, Jim C. Nasby wrote:
> >> Back in the discussion of this someone had mentioned capturing all the
> >> info that you'd get from a vacuum verbose; dead tuples, etc. What do
> >> people think about that? In particular I think it'd be handy to know how
> >> many pages vacuum wanted in the FSM vs. how many it got; this would make
> >> it much easier for people to ensure that the FSM is large enough.
>
> Isn't this already dealt with by contrib/pg_freespacemap?

AFAIK that does nothing to tell you how much space is desired by
relations. It would tell you if the FSM is nearly full, but I'm not sure
that's very reliable, especially given how every relation that wants
space in the FSM is giving a sizeable minimum number of pages (16,
iirc). Even putting that aside, it'd be nice to have an exact amount of
space that was needed.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461


From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Jim C(dot) Nasby" <jnasby(at)pervasive(dot)com>
Cc: Martijn van Oosterhout <kleptog(at)svana(dot)org>, Larry Rosenman <lrosenman(at)pervasive(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: patch review, please: Autovacuum/Vacuum times via stats.
Date: 2006-05-04 17:20:32
Message-ID: 11325.1146763232@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers pgsql-patches

"Jim C. Nasby" <jnasby(at)pervasive(dot)com> writes:
> On Wed, May 03, 2006 at 02:25:54PM -0400, Tom Lane wrote:
>> Isn't this already dealt with by contrib/pg_freespacemap?

> AFAIK that does nothing to tell you how much space is desired by
> relations.

I thought the latest patch arranged to expose per-relation lastPageCount
values. If it doesn't then it certainly still needs work.

regards, tom lane


From: "Jim C(dot) Nasby" <jnasby(at)pervasive(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Martijn van Oosterhout <kleptog(at)svana(dot)org>, Larry Rosenman <lrosenman(at)pervasive(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: patch review, please: Autovacuum/Vacuum times via stats.
Date: 2006-05-04 17:38:23
Message-ID: 20060504173823.GL97354@pervasive.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers pgsql-patches

On Thu, May 04, 2006 at 01:20:32PM -0400, Tom Lane wrote:
> "Jim C. Nasby" <jnasby(at)pervasive(dot)com> writes:
> > On Wed, May 03, 2006 at 02:25:54PM -0400, Tom Lane wrote:
> >> Isn't this already dealt with by contrib/pg_freespacemap?
>
> > AFAIK that does nothing to tell you how much space is desired by
> > relations.
>
> I thought the latest patch arranged to expose per-relation lastPageCount
> values. If it doesn't then it certainly still needs work.

After CVS-upping... yes, both lastpagecount and nextpage are now
included. But unfortunately the README says next to nothing about what
they mean...

lastpagecount | | Count of pages examined for useful
| | free space.
nextpage | | page index (from 0) to start next
| | search at.

Perhaps if there was some discussion about how the backend used these
two values it would make more sense, but right now I'm not seeing how
lastpagecount relates to how many pages weren't included in the FSM that
should have been...
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461


From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Jim C(dot) Nasby" <jnasby(at)pervasive(dot)com>
Cc: Martijn van Oosterhout <kleptog(at)svana(dot)org>, Larry Rosenman <lrosenman(at)pervasive(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: patch review, please: Autovacuum/Vacuum times via stats.
Date: 2006-05-04 19:36:53
Message-ID: 2986.1146771413@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers pgsql-patches

"Jim C. Nasby" <jnasby(at)pervasive(dot)com> writes:
> After CVS-upping... yes, both lastpagecount and nextpage are now
> included. But unfortunately the README says next to nothing about what
> they mean...

Yeah, this needs a bit of work ... will have at it.

regards, tom lane


From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: Larry Rosenman <ler(at)lerctr(dot)org>
Cc: pgsql-hackers(at)postgresql(dot)org, pgsql-patches(at)postgresql(dot)org
Subject: Re: [HACKERS] patch review, please: Autovacuum/Vacuum times via stats.
Date: 2006-05-20 00:25:17
Message-ID: 200605200025.k4K0PHw14710@candle.pha.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers pgsql-patches


Applied by Alvaro. Thanks.

---------------------------------------------------------------------------

Larry Rosenman wrote:
> Larry Rosenman wrote:
> > Greetings,
> > I've got a patch to be reviewed for having the stats system keep
> > track of the last
> > time a table was vacuumed or analyzed either by the user or via
> > AutoVacuum.
> >
> > The patch is at:
> > http://www.lerctr.org/~ler/pg-dev/vacuum-autovacuum-times-stats.diff
> >
> > I'd appreciate a full review, it includes docs as well.
> >
> > Thanks!
> >
> > LER
>
> I just replaced this one with one that actually bumps catversion.
>
> LER
>
> pgsql-patches added as well.
>
> I think this one is applyable if the powers that be want to.
>
> Comments/criticism welcome.
>
> LER
>
>
> --
> Larry Rosenman http://www.lerctr.org/~ler
> Phone: +1 512-248-2683 E-Mail: ler(at)lerctr(dot)org
> US Mail: 430 Valona Loop, Round Rock, TX 78681-3893
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>

--
Bruce Momjian http://candle.pha.pa.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +