Thread: DB Tuning Notes - Where To?
Just wondering where I should put my modified tuning notes. I was planning
on making them section 3.7 in the Admin guide. Does that sound reasonable?
The current version can be seen at:
http://www.rhyme.com.au/manuals/pgsql-7.3/postmaster-tuning-software.html
I think it's important we get something on tuning into the manual - I'm not
particularly attached to where.
----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 03 5330 3172 | ___________ |
Http://www.rhyme.com.au | / \| | --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/
On Wed, 2002-12-11 at 09:40, Philip Warner wrote: > > Just wondering where I should put my modified tuning notes. I was planning > on making them section 3.7 in the Admin guide. Does that sound reasonable? > > The current version can be seen at: > > http://www.rhyme.com.au/manuals/pgsql-7.3/postmaster-tuning-software.html > > I think it's important we get something on tuning into the manual - I'm not > particularly attached to where. > I had thought the information would be tied to the relevant sections of 3.4 Run-time Configuration. I'm not sure where the vacuum/analyze information would go in this scenario though, so a general software tuning section does seem appropriate. Do you see a 3.8 Tuning the Server (Hardware) section as well? Robert Treat
At 10:25 AM 11/12/2002 -0500, Robert Treat wrote: >Do you see a 3.8 Tuning the Server >(Hardware) section as well? Hardware and/or OS. I think Bruce's tuning docs tend to address the hardware and environmental issues, so I was not planning to write anything myself. ---------------------------------------------------------------- Philip Warner | __---_____ Albatross Consulting Pty. Ltd. |----/ - \ (A.B.N. 75 008 659 498) | /(@) ______---_ Tel: (+61) 0500 83 82 81 | _________ \ Fax: (+61) 03 5330 3172 | ___________ | Http://www.rhyme.com.au | / \| | --________-- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
Philip Warner wrote: > At 10:25 AM 11/12/2002 -0500, Robert Treat wrote: > >Do you see a 3.8 Tuning the Server > >(Hardware) section as well? > > Hardware and/or OS. I think Bruce's tuning docs tend to address the > hardware and environmental issues, so I was not planning to write anything > myself. I was unsure how _definiative_ the discussion was. -- 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, Pennsylvania19073
At 07:12 PM 11/12/2002 -0500, Bruce Momjian wrote:
>I was unsure how _definiative_ the discussion was.
I am sure that the statements in the document are *not* totally accurate;
but my belief is that they are better than nothing and a good starting
point for tuning.
For example, most numbers are upper limits: the 'Changed' and 'Removed'
figures do not reflect actual free pages, they just reflect pages on which
one or more tuple was added or removed etc. The page may still be 90% full,
and may never make it into the FSM depending on the setting of MAX_FSM_PAGES.
The choice of thresholds is arbitrary (as noted in the document), but also
reflects a good starting point IMO.
I think it is also important to put tuning notes in the main shipping
manuals; do people think it would be a good idea to add a disclaimer that:
tuning is inherently database-specific, you may need to set thresholds much lower, or may find that these are
toohigh - experiment with your database
?
IMO no tuning document can ever be definitive; it can only provide the user
with tools to understand the problem and manipulate the outcomes.
----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 03 5330 3172 | ___________ |
Http://www.rhyme.com.au | / \| | --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/
At 12:10 PM 12/12/2002 +1100, Philip Warner wrote: >good starting point for tuning I think this probably sums it up. IMO it is grandiose to call it a tuning document; at best it is a 'Misbehaviour Avoidance' document. We probably need something about the usual database-side tuning options: indexes, WAL, page sizes etc, and something else about environmental options (moving files, RAID etc). Should I change the section name to 'Routine Maintenance'? ---------------------------------------------------------------- Philip Warner | __---_____ Albatross Consulting Pty. Ltd. |----/ - \ (A.B.N. 75 008 659 498) | /(@) ______---_ Tel: (+61) 0500 83 82 81 | _________ \ Fax: (+61) 03 5330 3172 | ___________ | Http://www.rhyme.com.au | / \| | --________-- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
Philip Warner wrote: > At 12:10 PM 12/12/2002 +1100, Philip Warner wrote: > >good starting point for tuning > > I think this probably sums it up. > > IMO it is grandiose to call it a tuning document; at best it is a > 'Misbehaviour Avoidance' document. We probably need something about the > usual database-side tuning options: indexes, WAL, page sizes etc, and > something else about environmental options (moving files, RAID etc). Yep, that sounds like it. We should have that right in the docs next to that tuning parameter, or somewhere in a separate section on freespace map and point there. Also, this may improve over releases so we need to track the changes in the official release. If we can convey how the free space map works, people will be able to understand how their workload affects it. > Should I change the section name to 'Routine Maintenance'? Well, it isn't something you would play with regularly, like backups. It is more like the disk space analysis section I added in 7.3. -- 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, Pennsylvania19073
At 08:43 PM 11/12/2002 -0500, Bruce Momjian wrote: >Well, it isn't something you would play with regularly, like backups. How about I call it 'Managing Server Resources' and put it between 'Runtime Configuration' and 'Managing Kernel Resources'? ie. it becomes 3.5. ---------------------------------------------------------------- Philip Warner | __---_____ Albatross Consulting Pty. Ltd. |----/ - \ (A.B.N. 75 008 659 498) | /(@) ______---_ Tel: (+61) 0500 83 82 81 | _________ \ Fax: (+61) 03 5330 3172 | ___________ | Http://www.rhyme.com.au | / \| | --________-- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
Philip Warner <pjw@rhyme.com.au> writes:
> Should I change the section name to 'Routine Maintenance'?
No, because we already have an admin-guide section titled more or less
that way. Also, "tuning" is not "what you'd better do every week";
in my mind tuning activities will hold good till your database usage
changes.
regards, tom lane
At 01:22 AM 12/12/2002 -0500, Tom Lane wrote: >in my mind tuning activities will hold good till your database usage >changes. What about my later suggestion of 'Managing Server Resources', going before 'Managing Kernel Resources'. Or perhaps, 'Tuning Server Resources'... The document describes how to set the config items and vacuum/analyze frequencies...so it should not be regularly performed. ---------------------------------------------------------------- Philip Warner | __---_____ Albatross Consulting Pty. Ltd. |----/ - \ (A.B.N. 75 008 659 498) | /(@) ______---_ Tel: (+61) 0500 83 82 81 | _________ \ Fax: (+61) 03 5330 3172 | ___________ | Http://www.rhyme.com.au | / \| | --________-- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
Philip Warner writes: > Just wondering where I should put my modified tuning notes. I was planning > on making them section 3.7 in the Admin guide. Does that sound reasonable? The frequency of vacuum and analyze would seem to belong under Routine Vacuuming in the Maintenance chapter. Setting max_fsm_relations belongs under the entry in the big list of configuration parameters. Setting max_fsm_pages also belongs in that list, but the parts that refer to the VACUUM output should be put near the Routine Vacuuming section. Create loads of clickable cross-references. Btw., please don't enshrine promises about future versions in the documentation. Either create a patch that makes the indicated changes (recommended, since the problem appears to be analyzed) or just document the status quo. -- Peter Eisentraut peter_e@gmx.net
At 08:55 PM 13/12/2002 +0100, Peter Eisentraut wrote: >The frequency of vacuum and analyze would seem to belong.. >...max_fsm_relations belongs... >...Setting max_fsm_pages also belongs in that list... >...parts that refer to the VACUUM output should be put... near the Routine >Vacuuming Not sure I like this idea at all. The stuff I have written is in a practical order and makes a complete whole - each section refers to information obtained in previous sections. I have no objection to putting links in the sections you listed, indicating 'this is also discussed in XXX (Managing Server Resources)'... >---------------------------------------------------------------- Philip Warner | __---_____ Albatross Consulting Pty. Ltd. |----/ - \ (A.B.N. 75 008 659 498) | /(@) ______---_ Tel: (+61) 0500 83 82 81 | _________ \ Fax: (+61) 03 5330 3172 | ___________ | Http://www.rhyme.com.au | / \| | --________-- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/