pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: Autovacuum / full vacuum (off-topic?)
15+ messages / 7 participants
[nested] [flat]
* Re: Autovacuum / full vacuum (off-topic?)
@ 2006-01-18 19:15 Michael Crozier <crozierm@conducivetech.com>
0 siblings, 2 replies; 15+ messages in thread
From: Michael Crozier @ 2006-01-18 19:15 UTC (permalink / raw)
To: pgsql-performance
On Wednesday 18 January 2006 08:54 am, Chris Browne wrote:
> To the contrary, there is a whole section on what functionality to
> *ADD* to VACUUM.
Near but not quite off the topic of VACUUM and new features...
I've been thinking about parsing the vacuum output and storing it in
Postgresql. All the tuple, page, cpu time, etc... information would be
inserted into a reasonably flat set of tables.
The benefits I would expect from this are:
* monitoring ability - I could routinely monitor the values in the table to
warn when vacuum's are failing or reclaimed space has risen dramatically. I
find it easier to write and maintain monitoring agents that perform SQL
queries than ones that need to routinely parse log files and coordinate with
cron.
* historical perspective on tuple use - which a relatively small amount of
storage, I could use the vacuum output to get an idea of usage levels over
time, which is beneficial for planning additional capacity
* historical information could theoretically inform the autovacuum, though I
assume there are better alternatives planned.
* it could cut down on traffic on this list if admin could see routine
maintenance in a historical context.
Assuming this isn't a fundamentally horrible idea, it would be nice if there
were ways to do this without parsing the pretty-printed vacuum text (ie,
callbacks, triggers, guc variable).
I'd like to know if anybody does this already, thinks its a bad idea, or can
knock me on the noggin with the pg manual and say, "it's already there!".
Regards,
Michael
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Autovacuum / full vacuum (off-topic?)
@ 2006-01-18 22:04 Chris Browne <cbbrowne@acm.org>
parent: Michael Crozier <crozierm@conducivetech.com>
1 sibling, 1 reply; 15+ messages in thread
From: Chris Browne @ 2006-01-18 22:04 UTC (permalink / raw)
To: pgsql-performance
crozierm@conducivetech.com (Michael Crozier) writes:
> On Wednesday 18 January 2006 08:54 am, Chris Browne wrote:
>> To the contrary, there is a whole section on what functionality to
>> *ADD* to VACUUM.
>
> Near but not quite off the topic of VACUUM and new features...
>
> I've been thinking about parsing the vacuum output and storing it in
> Postgresql. All the tuple, page, cpu time, etc... information would
> be inserted into a reasonably flat set of tables.
>
> The benefits I would expect from this are:
>
> * monitoring ability - I could routinely monitor the values in the
> table to warn when vacuum's are failing or reclaimed space has risen
> dramatically. I find it easier to write and maintain monitoring
> agents that perform SQL queries than ones that need to routinely
> parse log files and coordinate with cron.
>
> * historical perspective on tuple use - which a relatively small
> amount of storage, I could use the vacuum output to get an idea of
> usage levels over time, which is beneficial for planning additional
> capacity
>
> * historical information could theoretically inform the autovacuum,
> though I assume there are better alternatives planned.
>
> * it could cut down on traffic on this list if admin could see
> routine maintenance in a historical context.
>
> Assuming this isn't a fundamentally horrible idea, it would be nice
> if there were ways to do this without parsing the pretty-printed
> vacuum text (ie, callbacks, triggers, guc variable).
>
> I'd like to know if anybody does this already, thinks its a bad
> idea, or can knock me on the noggin with the pg manual and say,
> "it's already there!".
We had someone working on that for a while; I don't think it got to
the point of being something ready to unleash on the world.
I certainly agree that it would be plenty useful to have this sort of
information available. Having a body of historical information could
lead to having some "more informed" suggestions for heuristics.
--
(reverse (concatenate 'string "gro.mca" "@" "enworbbc"))
http://cbbrowne.com/info/unix.html
Bad command. Bad, bad command! Sit! Stay! Staaay...
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Autovacuum / full vacuum (off-topic?)
@ 2006-01-18 22:52 Jim C. Nasby <jnasby@pervasive.com>
parent: Michael Crozier <crozierm@conducivetech.com>
1 sibling, 2 replies; 15+ messages in thread
From: Jim C. Nasby @ 2006-01-18 22:52 UTC (permalink / raw)
To: Michael Crozier <crozierm@conducivetech.com>; +Cc: pgsql-performance
On Wed, Jan 18, 2006 at 11:15:51AM -0800, Michael Crozier wrote:
> I've been thinking about parsing the vacuum output and storing it in
> Postgresql. All the tuple, page, cpu time, etc... information would be
> inserted into a reasonably flat set of tables.
<snip>
> Assuming this isn't a fundamentally horrible idea, it would be nice if there
> were ways to do this without parsing the pretty-printed vacuum text (ie,
> callbacks, triggers, guc variable).
The best way to do this would be to modify the vacuum code itself, but
the issue is that vacuum (or at least lazyvacuum) doesn't handle
transactions like the rest of the backend does, so I suspect that there
would be some issues with trying to log the information from the same
backend that was running the vacuum.
> I'd like to know if anybody does this already, thinks its a bad idea, or can
> knock me on the noggin with the pg manual and say, "it's already there!".
I think it's a good idea, but you should take a look at the recently
added functionality that allows you to investigate the contests of the
FSM via a user function (this is either in 8.1 or in HEAD; I can't
remember which).
--
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] 15+ messages in thread
* Re: Autovacuum / full vacuum (off-topic?)
@ 2006-01-18 23:36 Michael Crozier <crozierm@conducivetech.com>
parent: Jim C. Nasby <jnasby@pervasive.com>
1 sibling, 1 reply; 15+ messages in thread
From: Michael Crozier @ 2006-01-18 23:36 UTC (permalink / raw)
To: pgsql-performance
On Wednesday 18 January 2006 14:52 pm, Jim C. Nasby wrote:
> I think it's a good idea, but you should take a look at the recently
> added functionality that allows you to investigate the contests of the
> FSM via a user function (this is either in 8.1 or in HEAD; I can't
> remember which).
I will look at this when time allows. Perhaps there is a combination of
triggers on stat tables and asynchronous notifications that would provide
this functionality without getting too deep into the vacuum's transaction
logic?
Were it too integrated with the vacuum, it would likely be too much for
contrib/, I assume.
thanks,
Michael
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Autovacuum / full vacuum (off-topic?)
@ 2006-01-18 23:41 Michael Crozier <crozierm@conducivetech.com>
parent: Chris Browne <cbbrowne@acm.org>
0 siblings, 0 replies; 15+ messages in thread
From: Michael Crozier @ 2006-01-18 23:41 UTC (permalink / raw)
To: pgsql-performance
> We had someone working on that for a while; I don't think it got to
> the point of being something ready to unleash on the world.
Interesting. I will dig around the mailing list archives too see how they
went about it... for my own curiosity if nothing else. If you happen to
know offhand, I'd appreciate a link.
Regards,
Michael
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Autovacuum / full vacuum (off-topic?)
@ 2006-01-18 23:44 Jim C. Nasby <jnasby@pervasive.com>
parent: Michael Crozier <crozierm@conducivetech.com>
0 siblings, 0 replies; 15+ messages in thread
From: Jim C. Nasby @ 2006-01-18 23:44 UTC (permalink / raw)
To: Michael Crozier <crozierm@conducivetech.com>; +Cc: pgsql-performance
On Wed, Jan 18, 2006 at 03:36:04PM -0800, Michael Crozier wrote:
>
> On Wednesday 18 January 2006 14:52 pm, Jim C. Nasby wrote:
> > I think it's a good idea, but you should take a look at the recently
> > added functionality that allows you to investigate the contests of the
> > FSM via a user function (this is either in 8.1 or in HEAD; I can't
> > remember which).
>
> I will look at this when time allows. Perhaps there is a combination of
> triggers on stat tables and asynchronous notifications that would provide
> this functionality without getting too deep into the vacuum's transaction
> logic?
You can't put triggers on system tables, at least not ones that will be
triggered by system operations themselves, because the backend bypasses
normal access methods. Also, most of the really interesting info isn't
logged anywhere in a system table; stuff like the amount of dead space,
tuples removed, etc.
> Were it too integrated with the vacuum, it would likely be too much for
> contrib/, I assume.
Probably.
A good alternative might be allowing vacuum to output some
machine-friendly information (maybe into a backend-writable file?) and
then have code that could load that into a table (though presumably that
could should be as simple as just a COPY).
--
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] 15+ messages in thread
* Re: Autovacuum / full vacuum (off-topic?)
@ 2006-01-19 23:10 Mark Kirkwood <markir@paradise.net.nz>
parent: Jim C. Nasby <jnasby@pervasive.com>
1 sibling, 2 replies; 15+ messages in thread
From: Mark Kirkwood @ 2006-01-19 23:10 UTC (permalink / raw)
To: Jim C. Nasby <jnasby@pervasive.com>; +Cc: Michael Crozier <crozierm@conducivetech.com>; pgsql-performance
Jim C. Nasby wrote:
>
> I think it's a good idea, but you should take a look at the recently
> added functionality that allows you to investigate the contests of the
> FSM via a user function (this is either in 8.1 or in HEAD; I can't
> remember which).
AFAICS it is still in the patch queue for 8.2.
It's called 'pg_freespacemap' and is available for 8.1/8.0 from the
Pgfoundry 'backports' project:
http://pgfoundry.org/projects/backports
Cheers
Mark
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Autovacuum / full vacuum (off-topic?)
@ 2006-01-19 23:12 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Mark Kirkwood <markir@paradise.net.nz>
1 sibling, 0 replies; 15+ messages in thread
From: Bruce Momjian @ 2006-01-19 23:12 UTC (permalink / raw)
To: Mark Kirkwood <markir@paradise.net.nz>; +Cc: Jim C. Nasby <jnasby@pervasive.com>; Michael Crozier <crozierm@conducivetech.com>; pgsql-performance
Verified. I am working toward getting all those patches applied.
---------------------------------------------------------------------------
Mark Kirkwood wrote:
> Jim C. Nasby wrote:
>
> >
> > I think it's a good idea, but you should take a look at the recently
> > added functionality that allows you to investigate the contests of the
> > FSM via a user function (this is either in 8.1 or in HEAD; I can't
> > remember which).
>
> AFAICS it is still in the patch queue for 8.2.
>
> It's called 'pg_freespacemap' and is available for 8.1/8.0 from the
> Pgfoundry 'backports' project:
>
> http://pgfoundry.org/projects/backports
>
> Cheers
>
> Mark
>
>
> ---------------------------(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
>
--
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Autovacuum / full vacuum (off-topic?)
@ 2006-01-20 17:12 Jim C. Nasby <jnasby@pervasive.com>
parent: Mark Kirkwood <markir@paradise.net.nz>
1 sibling, 1 reply; 15+ messages in thread
From: Jim C. Nasby @ 2006-01-20 17:12 UTC (permalink / raw)
To: Mark Kirkwood <markir@paradise.net.nz>; +Cc: Michael Crozier <crozierm@conducivetech.com>; pgsql-performance
BTW, given all the recent discussion about vacuuming and our MVCC,
http://www.pervasive-postgres.com/lp/newsletters/2006/Insights_Postgres_Jan.asp#3
should prove interesting. :)
--
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] 15+ messages in thread
* Re: Autovacuum / full vacuum (off-topic?)
@ 2006-01-20 17:31 Joshua D. Drake <jd@commandprompt.com>
parent: Jim C. Nasby <jnasby@pervasive.com>
0 siblings, 1 reply; 15+ messages in thread
From: Joshua D. Drake @ 2006-01-20 17:31 UTC (permalink / raw)
To: Jim C. Nasby <jnasby@pervasive.com>; +Cc: Mark Kirkwood <markir@paradise.net.nz>; Michael Crozier <crozierm@conducivetech.com>; pgsql-performance
Jim C. Nasby wrote:
> BTW, given all the recent discussion about vacuuming and our MVCC,
> http://www.pervasive-postgres.com/lp/newsletters/2006/Insights_Postgres_Jan.asp#3
> should prove interesting. :)
>
Please explain... what is the .asp extension. I have yet to see it
reliable in production ;)
--
The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: PLphp, PLperl - http://www.commandprompt.com/
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Autovacuum / full vacuum (off-topic?)
@ 2006-01-20 17:34 Jim C. Nasby <jnasby@pervasive.com>
parent: Joshua D. Drake <jd@commandprompt.com>
0 siblings, 1 reply; 15+ messages in thread
From: Jim C. Nasby @ 2006-01-20 17:34 UTC (permalink / raw)
To: Joshua D. Drake <jd@commandprompt.com>; +Cc: Mark Kirkwood <markir@paradise.net.nz>; Michael Crozier <crozierm@conducivetech.com>; pgsql-performance
On Fri, Jan 20, 2006 at 09:31:14AM -0800, Joshua D. Drake wrote:
> Jim C. Nasby wrote:
> >BTW, given all the recent discussion about vacuuming and our MVCC,
> >http://www.pervasive-postgres.com/lp/newsletters/2006/Insights_Postgres_Jan.asp#3
> >should prove interesting. :)
> >
> Please explain... what is the .asp extension. I have yet to see it
> reliable in production ;)
I lay no claim to our infrastructure. :)
--
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] 15+ messages in thread
* Re: Autovacuum / full vacuum (off-topic?)
@ 2006-01-20 17:37 Joshua D. Drake <jd@commandprompt.com>
parent: Jim C. Nasby <jnasby@pervasive.com>
0 siblings, 1 reply; 15+ messages in thread
From: Joshua D. Drake @ 2006-01-20 17:37 UTC (permalink / raw)
To: Jim C. Nasby <jnasby@pervasive.com>; +Cc: Mark Kirkwood <markir@paradise.net.nz>; Michael Crozier <crozierm@conducivetech.com>; pgsql-performance
> I lay no claim to our infrastructure. :)
>
Can I quote the: Pervasive Senior Engineering Consultant on that?
--
The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: PLphp, PLperl - http://www.commandprompt.com/
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Autovacuum / full vacuum (off-topic?)
@ 2006-01-20 17:44 Jim C. Nasby <jnasby@pervasive.com>
parent: Joshua D. Drake <jd@commandprompt.com>
0 siblings, 1 reply; 15+ messages in thread
From: Jim C. Nasby @ 2006-01-20 17:44 UTC (permalink / raw)
To: Joshua D. Drake <jd@commandprompt.com>; +Cc: Mark Kirkwood <markir@paradise.net.nz>; Michael Crozier <crozierm@conducivetech.com>; pgsql-performance
On Fri, Jan 20, 2006 at 09:37:50AM -0800, Joshua D. Drake wrote:
>
> >I lay no claim to our infrastructure. :)
> >
> Can I quote the: Pervasive Senior Engineering Consultant on that?
Sure... I've never been asked to consult on our stuff, and in any case,
I don't do web front-ends (one of the nice things about working with a
team of other consultants). AFAIK IIS will happily talk to PostgreSQL
(though maybe I'm wrong there...)
I *have* asked what database is being used on the backend though, and
depending on the answer to that some folks might have some explaining to
do. :)
*grabs big can of dog food*
--
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] 15+ messages in thread
* Re: Autovacuum / full vacuum (off-topic?)
@ 2006-01-20 17:46 me@alternize.com
parent: Jim C. Nasby <jnasby@pervasive.com>
0 siblings, 1 reply; 15+ messages in thread
From: me@alternize.com @ 2006-01-20 17:46 UTC (permalink / raw)
To: Jim C. Nasby <jnasby@pervasive.com>; Joshua D. Drake <jd@commandprompt.com>; +Cc: Mark Kirkwood <markir@paradise.net.nz>; Michael Crozier <crozierm@conducivetech.com>; pgsql-performance
> Sure... I've never been asked to consult on our stuff, and in any case,
> I don't do web front-ends (one of the nice things about working with a
> team of other consultants). AFAIK IIS will happily talk to PostgreSQL
> (though maybe I'm wrong there...)
iis (yeah, asp in a successfull productive environement hehe) & postgresql
works even better for us than iis & mssql :-)
- thomas
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Autovacuum / full vacuum (off-topic?)
@ 2006-01-20 19:02 Jim C. Nasby <jnasby@pervasive.com>
parent: me@alternize.com
0 siblings, 0 replies; 15+ messages in thread
From: Jim C. Nasby @ 2006-01-20 19:02 UTC (permalink / raw)
To: me@alternize.com; +Cc: Joshua D. Drake <jd@commandprompt.com>; Mark Kirkwood <markir@paradise.net.nz>; Michael Crozier <crozierm@conducivetech.com>; pgsql-performance
On Fri, Jan 20, 2006 at 06:46:45PM +0100, me@alternize.com wrote:
> >Sure... I've never been asked to consult on our stuff, and in any case,
> >I don't do web front-ends (one of the nice things about working with a
> >team of other consultants). AFAIK IIS will happily talk to PostgreSQL
> >(though maybe I'm wrong there...)
>
> iis (yeah, asp in a successfull productive environement hehe) & postgresql
> works even better for us than iis & mssql :-)
Just last night I was talking to someone about different databases and
what-not (he's stuck in a windows shop using MSSQL and I mentioned I'd
heard some bad things about it's stability). I realized at some point
that asking about what large installs of something exist is pretty
pointless... given enough effort you can make almost anything scale. As
an example, there's a cable company with a MySQL database that's nearly
1TB... if that's not proof you can make anything scale, I don't know
what is. ;)
What people really need to ask about is how hard it is to make something
work, and how many problems you're likely to keep encountering.
--
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] 15+ messages in thread
end of thread, other threads:[~2006-01-20 19:02 UTC | newest]
Thread overview: 15+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-01-18 19:15 Re: Autovacuum / full vacuum (off-topic?) Michael Crozier <crozierm@conducivetech.com>
2006-01-18 22:04 ` Chris Browne <cbbrowne@acm.org>
2006-01-18 23:41 ` Michael Crozier <crozierm@conducivetech.com>
2006-01-18 22:52 ` Jim C. Nasby <jnasby@pervasive.com>
2006-01-18 23:36 ` Michael Crozier <crozierm@conducivetech.com>
2006-01-18 23:44 ` Jim C. Nasby <jnasby@pervasive.com>
2006-01-19 23:10 ` Mark Kirkwood <markir@paradise.net.nz>
2006-01-19 23:12 ` Bruce Momjian <pgman@candle.pha.pa.us>
2006-01-20 17:12 ` Jim C. Nasby <jnasby@pervasive.com>
2006-01-20 17:31 ` Joshua D. Drake <jd@commandprompt.com>
2006-01-20 17:34 ` Jim C. Nasby <jnasby@pervasive.com>
2006-01-20 17:37 ` Joshua D. Drake <jd@commandprompt.com>
2006-01-20 17:44 ` Jim C. Nasby <jnasby@pervasive.com>
2006-01-20 17:46 ` me@alternize.com
2006-01-20 19:02 ` Jim C. Nasby <jnasby@pervasive.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