Thread: patch review, please: Autovacuum/Vacuum times via stats.
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.Rosenman@pervasive.com Web: www.pervasive.com
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@lerctr.org US Mail: 430 Valona Loop, Round Rock, TX 78681-3893
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@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
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@svana.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@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
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@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
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@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
> 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
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@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
"Jim C. Nasby" <jnasby@pervasive.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
On Thu, May 04, 2006 at 01:20:32PM -0400, Tom Lane wrote:
> "Jim C. Nasby" <jnasby@pervasive.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 | |
freespace. 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@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
"Jim C. Nasby" <jnasby@pervasive.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
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@lerctr.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. +