Thread: DB Tuning Notes - Where To?

DB Tuning Notes - Where To?

From
Philip Warner
Date:
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   |/



Re: DB Tuning Notes - Where To?

From
Robert Treat
Date:
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




Re: DB Tuning Notes - Where To?

From
Philip Warner
Date:
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   |/



Re: DB Tuning Notes - Where To?

From
Bruce Momjian
Date:
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
 


Re: DB Tuning Notes - Where To?

From
Philip Warner
Date:
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   |/



Re: DB Tuning Notes - Where To?

From
Philip Warner
Date:
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   |/



Re: DB Tuning Notes - Where To?

From
Bruce Momjian
Date:
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
 


Re: DB Tuning Notes - Where To?

From
Philip Warner
Date:
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   |/



Re: DB Tuning Notes - Where To?

From
Tom Lane
Date:
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


Re: DB Tuning Notes - Where To?

From
Philip Warner
Date:
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   |/



Re: DB Tuning Notes - Where To?

From
Peter Eisentraut
Date:
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



Re: DB Tuning Notes - Where To?

From
Philip Warner
Date:
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   |/