vacuum and routine maintenance docs
After the most recent post on the general mailing list by someone who
was a bit confused by postgresql's vacuuming and use of the free space
map, I've been reviewing the page on routine maintenance.
It's pretty obvious that this page was originally written for an older
version of PostgreSQL and has had bits and pieces welded onto it over
time to cover newer features, like the autovacuum daemon.
I'd like to rewrite most of the page, or at least the parts referring to
vacuuming and analyzing. They seem a bit scattered about on the page,
and there's no mention at all of the free space map, something which is
integral to vacuuming the database.
So, before I go of slogging through the snow, a couple of questions.
1: Is there already a newer version in CVS? Should I check out the
docs/src directory from there to start? Or can I just go on what's in
the 8.1 release?
2: Any input on anyone as to what they feel they'd like to see in the
docs for this section?
The section on vacuuming and analysis seem kind of light on theory. I'm
thinking we should mention something about the default statistics or at
least a reference to a page that explains it.
This also leads me to believe that what we cover on the
runtime-config-resource page, because the coverage of the fsm there is
kind of light too.
So, any advice?
Scott Marlowe wrote:
1: Is there already a newer version in CVS? Should I check out the
docs/src directory from there to start? Or can I just go on what's in
the 8.1 release?
I suggest you look at
http://developer.postgresql.org/cvsweb.cgi/pgsql/doc/
Those are the latest versions of the doc source. The built versions are
here: http://developer.postgresql.org/docs/postgres/index.html
--
Alvaro Herrera http://www.amazon.com/gp/registry/DXLWNGRJD34J
"Cuando miro a alguien, m�s me atrae c�mo cambia que qui�n es" (J. Binoche)
Before you 'put pen to paper', I have two articles that you should look
at. One is on FSM, the other is more about MVCC from the standpoint of
why we use it and why it means needing to vacuum. I don't have the URL's
handy here, but I can post them tomorrow.
On Tue, Jan 17, 2006 at 11:14:09AM -0600, Scott Marlowe wrote:
After the most recent post on the general mailing list by someone who
was a bit confused by postgresql's vacuuming and use of the free space
map, I've been reviewing the page on routine maintenance.It's pretty obvious that this page was originally written for an older
version of PostgreSQL and has had bits and pieces welded onto it over
time to cover newer features, like the autovacuum daemon.I'd like to rewrite most of the page, or at least the parts referring to
vacuuming and analyzing. They seem a bit scattered about on the page,
and there's no mention at all of the free space map, something which is
integral to vacuuming the database.So, before I go of slogging through the snow, a couple of questions.
1: Is there already a newer version in CVS? Should I check out the
docs/src directory from there to start? Or can I just go on what's in
the 8.1 release?2: Any input on anyone as to what they feel they'd like to see in the
docs for this section?The section on vacuuming and analysis seem kind of light on theory. I'm
thinking we should mention something about the default statistics or at
least a reference to a page that explains it.This also leads me to believe that what we cover on the
runtime-config-resource page, because the coverage of the fsm there is
kind of light too.So, any advice?
---------------------------(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
--
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
Thanks. Yeah, I'm thinking more of a very brief mention of what the FSM
is, with a link to the other part of the docs that discuss it. And then
having a link (possibly in both places) or two to more in depth articles
on fsm and fsm tuning.
Show quoted text
On Tue, 2006-01-17 at 16:49, Jim C. Nasby wrote:
Before you 'put pen to paper', I have two articles that you should look
at. One is on FSM, the other is more about MVCC from the standpoint of
why we use it and why it means needing to vacuum. I don't have the URL's
handy here, but I can post them tomorrow.On Tue, Jan 17, 2006 at 11:14:09AM -0600, Scott Marlowe wrote:
After the most recent post on the general mailing list by someone who
was a bit confused by postgresql's vacuuming and use of the free space
map, I've been reviewing the page on routine maintenance.It's pretty obvious that this page was originally written for an older
version of PostgreSQL and has had bits and pieces welded onto it over
time to cover newer features, like the autovacuum daemon.I'd like to rewrite most of the page, or at least the parts referring to
vacuuming and analyzing. They seem a bit scattered about on the page,
and there's no mention at all of the free space map, something which is
integral to vacuuming the database.So, before I go of slogging through the snow, a couple of questions.
1: Is there already a newer version in CVS? Should I check out the
docs/src directory from there to start? Or can I just go on what's in
the 8.1 release?2: Any input on anyone as to what they feel they'd like to see in the
docs for this section?The section on vacuuming and analysis seem kind of light on theory. I'm
thinking we should mention something about the default statistics or at
least a reference to a page that explains it.This also leads me to believe that what we cover on the
runtime-config-resource page, because the coverage of the fsm there is
kind of light too.So, any advice?
---------------------------(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
Sounds good. The articles are part of a Pervasive newsletter, hosted on
pervasive-postgres.com, but they can get posted else where with proper
attribution.
On Tue, Jan 17, 2006 at 04:59:29PM -0600, Scott Marlowe wrote:
Thanks. Yeah, I'm thinking more of a very brief mention of what the FSM
is, with a link to the other part of the docs that discuss it. And then
having a link (possibly in both places) or two to more in depth articles
on fsm and fsm tuning.On Tue, 2006-01-17 at 16:49, Jim C. Nasby wrote:
Before you 'put pen to paper', I have two articles that you should look
at. One is on FSM, the other is more about MVCC from the standpoint of
why we use it and why it means needing to vacuum. I don't have the URL's
handy here, but I can post them tomorrow.
--
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, Jan 17, 2006 at 04:59:29PM -0600, Scott Marlowe wrote:
Thanks. Yeah, I'm thinking more of a very brief mention of what the FSM
is, with a link to the other part of the docs that discuss it. And then
having a link (possibly in both places) or two to more in depth articles
on fsm and fsm tuning.
Got the links...
http://www.pervasive-postgres.com/lp/newsletters/2005/Insights_opensource_Dec.asp#2
is general info about MVCC and why you need to vacuum.
http://www.pervasive-postgres.com/lp/newsletters/2005/Insights_opensource_Nov.asp#3
is all about the FSM (yeah, I kinda put the horse before the cart...)
I'm working on finding out if the community can use that text verbatim
without attribution, but if nothing else it might provide you with some
ideas.
--
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 Wed, 2006-01-18 at 16:19, Jim C. Nasby wrote:
On Tue, Jan 17, 2006 at 04:59:29PM -0600, Scott Marlowe wrote:
Thanks. Yeah, I'm thinking more of a very brief mention of what the FSM
is, with a link to the other part of the docs that discuss it. And then
having a link (possibly in both places) or two to more in depth articles
on fsm and fsm tuning.Got the links...
http://www.pervasive-postgres.com/lp/newsletters/2005/Insights_opensource_Dec.asp#2
is general info about MVCC and why you need to vacuum.
http://www.pervasive-postgres.com/lp/newsletters/2005/Insights_opensource_Nov.asp#3
is all about the FSM (yeah, I kinda put the horse before the cart...)I'm working on finding out if the community can use that text verbatim
without attribution, but if nothing else it might provide you with some
ideas.
Well, I'm kind of tempted to rewrite the whole section. I'd like to
come up with some objectives of what we're trying to cover there, and
basically start over with all the information that's there plus the
other stuff on fsm and mvcc as it applies to vacuum, or link to it as
needed.
To me, the docs feel kind of like they've evolved over time, so the
ordering and general layout feel klunky. No slam on the folks who wrote
them to start with, or updated them. Just makes my tech docs training
come out.
Thanks for the links!
On Wed, 18 Jan 2006, Scott Marlowe wrote:
Well, I'm kind of tempted to rewrite the whole section. I'd like to
come up with some objectives of what we're trying to cover there, and
basically start over with all the information that's there plus the
other stuff on fsm and mvcc as it applies to vacuum, or link to it as
needed.
A couple questions I find difficult to answer using the online docs are:
* Is my autovacuum doing a good enough job?
* What percentage of bloat is reasonable (and how can I tell what my bloat
level is)?
* What transaction loads would require specific autovacuum settings, or manual
vacuum fulls?
--
Jeff Frost, Owner <jeff@frostconsultingllc.com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954
Scott Marlowe <smarlowe@g2switchworks.com> writes:
Well, I'm kind of tempted to rewrite the whole section. I'd like to
come up with some objectives of what we're trying to cover there, and
basically start over with all the information that's there plus the
other stuff on fsm and mvcc as it applies to vacuum, or link to it as
needed.
To me, the docs feel kind of like they've evolved over time, so the
ordering and general layout feel klunky. No slam on the folks who wrote
them to start with, or updated them. Just makes my tech docs training
come out.
You're certainly right about the history of the docs, and I agree that a
ground-up rewrite is the only thing that would help much --- trying to
incorporate new separately-written material is just going to be "more of
the same", ISTM, no matter how good that material might be on its own.
Is it reasonable to try to make an outline of how you think the chapter
should go, or would that be premature without more discussion of
objectives?
regards, tom lane
On Wed, Jan 18, 2006 at 07:55:07PM -0500, Tom Lane wrote:
Scott Marlowe <smarlowe@g2switchworks.com> writes:
Well, I'm kind of tempted to rewrite the whole section. I'd like to
come up with some objectives of what we're trying to cover there, and
basically start over with all the information that's there plus the
other stuff on fsm and mvcc as it applies to vacuum, or link to it as
needed.To me, the docs feel kind of like they've evolved over time, so the
ordering and general layout feel klunky. No slam on the folks who wrote
them to start with, or updated them. Just makes my tech docs training
come out.You're certainly right about the history of the docs, and I agree that a
ground-up rewrite is the only thing that would help much --- trying to
incorporate new separately-written material is just going to be "more of
the same", ISTM, no matter how good that material might be on its own.Is it reasonable to try to make an outline of how you think the chapter
should go, or would that be premature without more discussion of
objectives?
I'm wondering if people feel this is an issue with the docs in general
and isn't limited to just the admin stuff?
I know I certainly have trouble finding things... I generally look for
stuff by going to the appropriate page in the SQL syntax section and
seeing what it links too, but of course there's a lot of things that
aren't (and shouldn't be) linked to from there...
--
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:
I'm wondering if people feel this is an issue with the docs in general
and isn't limited to just the admin stuff?
Probably, but the admin stuff seems to suffer worst. In any case, Scott
volunteered to look at redoing that part, and I'm not going to repay the
offer by asking him to redo the whole manual ;-)
regards, tom lane
I know I certainly have trouble finding things... I generally look for
stuff by going to the appropriate page in the SQL syntax section and
seeing what it links too, but of course there's a lot of things that
aren't (and shouldn't be) linked to from there...
I find that it is generally difficult to find things in the docs but
that once you find it the information is usually very good.
There are exceptions as have already been pointed out of course.
Example issues I see:
Why is Managing Database and Routine Database Management separate?
Server configuration is kind of vague... Perhaps PostgreSQL
configuration? But then why isn't that under Managing Databases :)
Sincerely,
Joshua D. Drake
--
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, plPerlNG - http://www.commandprompt.com/
On Wed, Jan 18, 2006 at 08:19:16PM -0500, Tom Lane wrote:
"Jim C. Nasby" <jnasby@pervasive.com> writes:
I'm wondering if people feel this is an issue with the docs in general
and isn't limited to just the admin stuff?Probably, but the admin stuff seems to suffer worst. In any case, Scott
volunteered to look at redoing that part, and I'm not going to repay the
offer by asking him to redo the whole manual ;-)
Certainly true. :) But if we're going to start at an outline level I
think it would be enlightening to do a short (as in only 2 levels deep)
re-outline of all the docs and see how it compares to what we have. It
would at least indicate things that should be wholesale moved out of the
admin section...
Plus hopefully we could get others to help. :) I'd certainly lend a
hand.
Is there an easy way to get a 2-level outline out of the sgml?
--
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 Wed, 2006-01-18 at 17:24 -0800, Joshua D. Drake wrote:
Why is Managing Database and Routine Database Management separate?
I assume you mean the "Managing Databases" and "Routine Database
Maintenance Tasks" chapters. I think these chapters are separate because
they address fairly different subject matter. You could call the latter
chapter "Routine Maintenance Tasks" without loss of meaning, as it
doesn't focus on maintaining individual databases per se.
Server configuration is kind of vague... Perhaps PostgreSQL
configuration?
Personally I don't think that's an improvement, although I'm not
completely satisfied with "Server Configuration" either.
But then why isn't that under Managing Databases :)
Because that chapter describes managing individual databases and
tablespaces, not an entire PostgreSQL instance.
-Neil
On Wed, 2006-01-18 at 19:31, Jim C. Nasby wrote:
On Wed, Jan 18, 2006 at 08:19:16PM -0500, Tom Lane wrote:
"Jim C. Nasby" <jnasby@pervasive.com> writes:
I'm wondering if people feel this is an issue with the docs in general
and isn't limited to just the admin stuff?Probably, but the admin stuff seems to suffer worst. In any case, Scott
volunteered to look at redoing that part, and I'm not going to repay the
offer by asking him to redo the whole manual ;-)Certainly true. :) But if we're going to start at an outline level I
think it would be enlightening to do a short (as in only 2 levels deep)
re-outline of all the docs and see how it compares to what we have. It
would at least indicate things that should be wholesale moved out of the
admin section...Plus hopefully we could get others to help. :) I'd certainly lend a
hand.Is there an easy way to get a 2-level outline out of the sgml?
I agree completely. We don't probably need it in sgml just yet. From a
40,000 ft perspective, we can break administration up into several large
chunks, and then decide what needs to go in each. The general "big
topics" we already have seem pretty serviceable. However, I tend to
think of OS env and server config as being sub topics under
installation. Under that, we can put the individual subjects
accordingly.
- Installation
-- Documentation scope specification
-- Preparation (setting objectives for the installation)
-- Hardware considerations (emphasizing things like fsyncing and all)
-- OS configuration (i.e. shared memory)
-- Software installation (source versus rpm versus pkg etc.)
-- Cluster initialization (localization issues, location, etc...)
-- Server configuration (i.e. pg_hba / postgresql.conf et. al.)
-- Running the server (start up scripts, by hand, etc.)
-- Verifying server operation (regression tests)
- Management
-- Databases
-- Users
-- Roles and Privileges
- Maintenance
-- Backup and Restore
-- PITR
-- Replication (where to look for it.)
-- Monitoring
-- Routine Maintenance (vacuum, analyze, etc...)
- Troubleshooting
(I'm not sure what to put under here right now... Will think on it.)
Please, feel free to rip this up and reassemble as necessary. But I do
think it's important to regroup our subjects under a few very broad
topics.
Am Donnerstag, 19. Januar 2006 02:31 schrieb Jim C. Nasby:
Is there an easy way to get a 2-level outline out of the sgml?
How would you define the TOC that we currently produce?
--
Peter Eisentraut
http://developer.postgresql.org/~petere/
On Thu, Jan 19, 2006 at 06:00:36PM +0100, Peter Eisentraut wrote:
Am Donnerstag, 19. Januar 2006 02:31 schrieb Jim C. Nasby:
Is there an easy way to get a 2-level outline out of the sgml?
How would you define the TOC that we currently produce?
Sorry, I guess what I was actually thinking of is a 3-level one. In any
case, I'm sure I can figure it out from whatever code generates
index.html...
--
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 Thu, Jan 19, 2006 at 10:15:19AM -0600, Scott Marlowe wrote:
I agree completely. We don't probably need it in sgml just yet. From a
Actually, I was looking for how to get the view out of the existing SGML
;) In any case, I think admin is probably broad enough that there won't
be much overlap with other sections.
40,000 ft perspective, we can break administration up into several large
chunks, and then decide what needs to go in each. The general "big
topics" we already have seem pretty serviceable. However, I tend to
think of OS env and server config as being sub topics under
installation. Under that, we can put the individual subjects
accordingly.- Installation
-- Documentation scope specification
-- Preparation (setting objectives for the installation)
-- Hardware considerations (emphasizing things like fsyncing and all)
-- OS configuration (i.e. shared memory)
-- Software installation (source versus rpm versus pkg etc.)
-- Cluster initialization (localization issues, location, etc...)
-- Server configuration (i.e. pg_hba / postgresql.conf et. al.)
-- Running the server (start up scripts, by hand, etc.)
-- Verifying server operation (regression tests)
Should probably mention contrib in here somewhere... and other
resources, like pgFoundry. Afterall, the install section is somewhat of
someone's introduction to PostgreSQL...
- Management
-- Databases
-- Users
-- Roles and Privileges
Somewhere in one or both of the above should probably be some discussion
on security practices... Also, pg_hba.conf is a bit of a stickler,
because it is closely related to users and authentication.
postgresql.conf is also a bit tricky, because many of it's settings
require knowledge from other areas. Maybe the best way to deal with
these is extensive cross-linking? IE: each postgresql.conf item (or set
of items) should have a link back to whatever section explains it in
detail.
--
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 Thu, 2006-01-19 at 16:00, Jim C. Nasby wrote:
On Thu, Jan 19, 2006 at 10:15:19AM -0600, Scott Marlowe wrote:
I agree completely. We don't probably need it in sgml just yet. From a
Actually, I was looking for how to get the view out of the existing SGML
;) In any case, I think admin is probably broad enough that there won't
be much overlap with other sections.40,000 ft perspective, we can break administration up into several large
chunks, and then decide what needs to go in each. The general "big
topics" we already have seem pretty serviceable. However, I tend to
think of OS env and server config as being sub topics under
installation. Under that, we can put the individual subjects
accordingly.- Installation
-- Documentation scope specification
-- Preparation (setting objectives for the installation)
-- Hardware considerations (emphasizing things like fsyncing and all)
-- OS configuration (i.e. shared memory)
-- Software installation (source versus rpm versus pkg etc.)
-- Cluster initialization (localization issues, location, etc...)
-- Server configuration (i.e. pg_hba / postgresql.conf et. al.)
-- Running the server (start up scripts, by hand, etc.)
-- Verifying server operation (regression tests)Should probably mention contrib in here somewhere... and other
resources, like pgFoundry. Afterall, the install section is somewhat of
someone's introduction to PostgreSQL...
Good point. I'll add that somewhere in there under software
installation.
- Management
-- Databases
-- Users
-- Roles and PrivilegesSomewhere in one or both of the above should probably be some discussion
on security practices... Also, pg_hba.conf is a bit of a stickler,
because it is closely related to users and authentication.
postgresql.conf is also a bit tricky, because many of it's settings
require knowledge from other areas. Maybe the best way to deal with
these is extensive cross-linking? IE: each postgresql.conf item (or set
of items) should have a link back to whatever section explains it in
detail.
Yeah, I keep thinking more and more we need a LOT of linkable resources
for this, much like the ones we have for all the standard command
reference stuff. I'm leaning towards having the main page of each of
these things be somewhat closer to an executive summary (not that light
on info, but you know what I mean) and have links to more info for each
subject. So that each section can have a much more in depth coverage
but not make it a huge slog to get through the documentation.
Scott Marlowe <smarlowe@g2switchworks.com> writes:
Yeah, I keep thinking more and more we need a LOT of linkable resources
for this, much like the ones we have for all the standard command
reference stuff. I'm leaning towards having the main page of each of
these things be somewhat closer to an executive summary (not that light
on info, but you know what I mean) and have links to more info for each
subject. So that each section can have a much more in depth coverage
but not make it a huge slog to get through the documentation.
The refrain that I keep hearing is that the info is in there but it's
not so easy to find. So this sounds like a plan to me: quick overviews
with links should make it easier to find the parts people need to read.
regards, tom lane
