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


Thu, 12 Dec 2002 01:40:41 +1100
Philip Warner <pjw@rhyme.com.au>
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



11 Dec 2002 10:25:11 -0500
Robert Treat <xzilla@users.sourceforge.net>
Re: DB Tuning Notes - Where To?
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   |/


Thu, 12 Dec 2002 02:30:26 +1100
Philip Warner <pjw@rhyme.com.au>
Re: DB Tuning Notes - Where To?
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

Wed, 11 Dec 2002 19:12:40 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: DB Tuning Notes - Where To?
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   |/


Thu, 12 Dec 2002 12:10:22 +1100
Philip Warner <pjw@rhyme.com.au>
Re: DB Tuning Notes - Where To?
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   |/


Thu, 12 Dec 2002 12:28:10 +1100
Philip Warner <pjw@rhyme.com.au>
Re: DB Tuning Notes - Where To?
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

Wed, 11 Dec 2002 20:43:22 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: DB Tuning Notes - Where To?
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   |/


Thu, 12 Dec 2002 12:57:27 +1100
Philip Warner <pjw@rhyme.com.au>
Re: DB Tuning Notes - Where To?
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

Thu, 12 Dec 2002 01:22:16 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: DB Tuning Notes - Where To? 
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   |/


Thu, 12 Dec 2002 17:33:45 +1100
Philip Warner <pjw@rhyme.com.au>
Re: DB Tuning Notes - Where To? 
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


Fri, 13 Dec 2002 20:55:33 +0100 (CET)
Peter Eisentraut <peter_e@gmx.net>
Re: DB Tuning Notes - Where To?
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   |/


Sat, 14 Dec 2002 11:28:27 +1100
Philip Warner <pjw@rhyme.com.au>
Re: DB Tuning Notes - Where To?