DB Tuning Notes - Where To?

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, Pennsylvania 19073

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 too high
- 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, Pennsylvania 19073

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 |/