Thread: postgresql.conf

postgresql.conf

From
"scott.marlowe"
Date:
I'm looking at doing the example postgresql.conf files for the 7.4
release. So far, the catagories we have would be a matrix of:

-------------- Large Machine -- Small Machine
Webserver
OLAP
OLTP
Workstation

But likely only one entry for workstation.

anyone have any advice on what they use in which situations and what we
should include in the examples?

I'm guessing OLTP needs things like FSM cranked up,
OLAP (a for analytical) needs more shared buffers and sort memory
Webserver might be better served just slightly higher values than default
but well under those of either OLTP or OLAP...





Re: postgresql.conf

From
Josh Berkus
Date:
Scott,

> I'm guessing OLTP needs things like FSM cranked up,
> OLAP (a for analytical) needs more shared buffers and sort memory
> Webserver might be better served just slightly higher values than default
> but well under those of either OLTP or OLAP...

Yes. Take sort_mem for example:
OLTP_SM    1024
OLTP_LM    2048
OLAP_SM    4096
OLAP_LM    16384
WWW_SM    512
WWW_LM    1024
Workstation    1024

The basic idea is:
More RAM => more sort_mem
More concurrent queries => less sort_mem
Larger data sets => more sort_mem
Lots of grouped aggregates => more sort_mem

--
-Josh Berkus
 Aglio Database Solutions
 San Francisco


Re: postgresql.conf

From
Ron Johnson
Date:
On Wed, 2003-07-30 at 11:59, scott.marlowe wrote:
> I'm looking at doing the example postgresql.conf files for the 7.4
> release. So far, the catagories we have would be a matrix of:
>
> -------------- Large Machine -- Small Machine
> Webserver
> OLAP
> OLTP
> Workstation
>
> But likely only one entry for workstation.

How about "General Purpose", for DBs that don't fit into any one
category?

> anyone have any advice on what they use in which situations and what we
> should include in the examples?
>
> I'm guessing OLTP needs things like FSM cranked up,
> OLAP (a for analytical) needs more shared buffers and sort memory
> Webserver might be better served just slightly higher values than default
> but well under those of either OLTP or OLAP...



--
+-----------------------------------------------------------------+
| Ron Johnson, Jr.        Home: ron.l.johnson@cox.net             |
| Jefferson, LA  USA                                              |
|                                                                 |
| "I'm not a vegetarian because I love animals, I'm a vegetarian  |
|  because I hate vegetables!"                                    |
|    unknown                                                      |
+-----------------------------------------------------------------+



Re: postgresql.conf

From
"Robert Treat"
Date:
On Wed, 30 Jul 2003 10:59:23 -0600 (MDT), "scott.marlowe" wrote:
>
> I'm looking at doing the example postgresql.conf files for the 7.4
> release. So far, the catagories we have would be a matrix of:
>
> -------------- Large Machine -- Small Machine
> Webserver
> OLAP
> OLTP
> Workstation
>
> But likely only one entry for workstation.
>
> anyone have any advice on what they use in which situations and what we
> should include in the examples?
>
> I'm guessing OLTP needs things like FSM cranked up,
> OLAP (a for analytical) needs more shared buffers and sort memory
> Webserver might be better served just slightly higher values than default
> but well under those of either OLTP or OLAP...
>

Are you planning on differentiating between dedicated machines and multi-server
machines? For example, a dedicated database for a webserver would be tuned
differently from a server that was running both the webserver and the database on
the same machine.

Robert Treat

--
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL

Re: postgresql.conf

From
Josh Berkus
Date:
Robert,

> Are you planning on differentiating between dedicated machines and
multi-server
> machines? For example, a dedicated database for a webserver would be tuned
> differently from a server that was running both the webserver and the
database on
> the same machine.

My thought is when we define "Small Machine" in at the top of the file we
define it as "Small Machine or Multi-Purpose machine".  The settings should
be nearly the same for a machine that has only 64MB of *available* RAM as one
that has only 96MB of RAM at all.

--
-Josh Berkus
 Aglio Database Solutions
 San Francisco


Re: postgresql.conf

From
Ron
Date:
Josh Berkus wrote:

>Robert,
>
>
>
>>Are you planning on differentiating between dedicated machines and
>>
>>
>multi-server
>
>
>>machines? For example, a dedicated database for a webserver would be tuned
>>differently from a server that was running both the webserver and the
>>
>>
>database on
>
>
>>the same machine.
>>
>>
>
>My thought is when we define "Small Machine" in at the top of the file we
>define it as "Small Machine or Multi-Purpose machine".  The settings should
>be nearly the same for a machine that has only 64MB of *available* RAM as one
>that has only 96MB of RAM at all.
>
>
>
We are using postgres 7.3.2 for one of our clients with a smallish db,
on a p4 with 4G ram box which also servers as a web server and a
web-based file repository (~ 80G). We are starting another project for
another customer, on a p4 with 2G ram, and the db will be larger, approx
7-15G when finished. This box will also be used as a web server, and as
it won't go 'live' until the fall, will run 7.4.

I don't know if this is representative of other postgresql installs, but
I would also put in my vote for the differentiation added, as these are
not small machines but are multi-server boxes.

my 2 cents worth
Ron

PS   the new postgresql.conf performance tuning docs are extremely
helpful, thanks


Re: postgresql.conf

From
Josh Berkus
Date:
Ron,

> I don't know if this is representative of other postgresql installs, but
> I would also put in my vote for the differentiation added, as these are
> not small machines but are multi-server boxes.

But how is the Multi-purpose configuration different from the Small Machine
configuration?    If the actual settings are the same, we just need to
explain somewhere what it means.

I'll argue pretty strongly against including a seperate MP configuration
because it would raise our number of suggested sets to 10 from 7.

--
-Josh Berkus
 Aglio Database Solutions
 San Francisco


Re: postgresql.conf

From
"scott.marlowe"
Date:
On Wed, 30 Jul 2003, Josh Berkus wrote:

> Ron,
>
> > I don't know if this is representative of other postgresql installs, but
> > I would also put in my vote for the differentiation added, as these are
> > not small machines but are multi-server boxes.
>
> But how is the Multi-purpose configuration different from the Small Machine
> configuration?    If the actual settings are the same, we just need to
> explain somewhere what it means.
>
> I'll argue pretty strongly against including a seperate MP configuration
> because it would raise our number of suggested sets to 10 from 7.

Maybe we should look at it more from the point of view of how much
horsepower (I/O bandwidth, memory, memory bandwidth, cpu bandwidth) is
left over for postgresql.  After all, a Dual 2.8GHz Opteron with 32 gigs
of ram is gonna be faster, even if it has apache/LDAP/etc  on it than a
dedicated P100 with 64 meg of ram.

I think the default postgresql.conf should be the one for the 64 Meg free
PII-300 and below class, and our first step up should assume say, 256 Meg
ram and simple RAID1, approximately 1GHz CPU or less.  The high end should
assume Dual CPUs of 1Ghz or better, 1Gig of ram (or more).

Once someone is getting into the 8 way Itanium II with 32 Gigs of RAM,
the fact that they are doing something that big means that by looking at
the default, the workgroup, and the large server configs, they can
extrapolate and experiment to determine the best settings, and are going
to need to anyway to get it right.

So, maybe just a note on which parameters to increase if you have more
RAM/CPU/I/O bandwidth in the big server example?


Re: postgresql.conf

From
Josh Berkus
Date:
Scott,

> Once someone is getting into the 8 way Itanium II with 32 Gigs of RAM,
> the fact that they are doing something that big means that by looking at
> the default, the workgroup, and the large server configs, they can
> extrapolate and experiment to determine the best settings, and are going
> to need to anyway to get it right.
>
> So, maybe just a note on which parameters to increase if you have more
> RAM/CPU/I/O bandwidth in the big server example?

Also, lets not get away from our goal here, which is NOT to provide
comprehensive documenation (which is available elsewhere) but to give new
DBAs a *sample* config that will perform better than the default.

Plus, I would assume that anybody who spent $50,000 on hardware would be smart
enough to pay a consultant $1000 to tune their database correctly.

--
-Josh Berkus
 Aglio Database Solutions
 San Francisco


Re: postgresql.conf

From
cafweb
Date:

So, maybe just a note on which parameters to increase if you have more 
RAM/CPU/I/O bandwidth in the big server example? 
Yes, that would be great. Actually I prefer rules of thumb and examples for each extreme. If possible a little note WHY the parameter should be tweaked, and what effects it will have. For example, more note's like Josh's below would be a big help...

Yes. Take sort_mem for example:
OLTP_SM	1024
OLTP_LM	2048
OLAP_SM	4096
OLAP_LM	16384
WWW_SM	512
WWW_LM	1024
Workstation	1024

The basic idea is:
More RAM => more sort_mem
More concurrent queries => less sort_mem
Larger data sets => more sort_mem
Lots of grouped aggregates => more sort_mem
 

-- 
[ Christian Fowler
[ cafweb@lulu.com
[ http://www.lulu.com