pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
postgresql.conf
11+ messages / 7 participants
[nested] [flat]

* postgresql.conf
@ 2003-07-30 16:59  scott.marlowe <scott.marlowe@ihs.com>
  0 siblings, 2 replies; 11+ messages in thread

From: scott.marlowe @ 2003-07-30 16:59 UTC (permalink / raw)
  To: pgsql-performance

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







^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: postgresql.conf
@ 2003-07-30 17:19  Josh Berkus <josh@agliodbs.com>
  parent: scott.marlowe <scott.marlowe@ihs.com>
  1 sibling, 0 replies; 11+ messages in thread

From: Josh Berkus @ 2003-07-30 17:19 UTC (permalink / raw)
  To: scott.marlowe <scott.marlowe@ihs.com>; pgsql-performance

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




^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: postgresql.conf
@ 2003-07-30 18:32  Ron Johnson <ron.l.johnson@cox.net>
  parent: scott.marlowe <scott.marlowe@ihs.com>
  1 sibling, 0 replies; 11+ messages in thread

From: Ron Johnson @ 2003-07-30 18:32 UTC (permalink / raw)
  To: pgsql-performance

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





^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: postgresql.conf
@ 2003-07-30 18:34  Robert Treat <xzilla@users.sourceforge.net>
  0 siblings, 1 reply; 11+ messages in thread

From: Robert Treat @ 2003-07-30 18:34 UTC (permalink / raw)
  To: scott.marlowe@ihs.com; +Cc: pgsql-performance

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



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: postgresql.conf
@ 2003-07-30 18:43  Josh Berkus <josh@agliodbs.com>
  parent: Robert Treat <xzilla@users.sourceforge.net>
  0 siblings, 1 reply; 11+ messages in thread

From: Josh Berkus @ 2003-07-30 18:43 UTC (permalink / raw)
  To: Robert Treat <xzilla@users.sourceforge.net>; scott.marlowe@ihs.com; +Cc: pgsql-performance

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




^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: postgresql.conf
@ 2003-07-30 18:59  Ron <rstpierre@syscor.com>
  parent: Josh Berkus <josh@agliodbs.com>
  0 siblings, 1 reply; 11+ messages in thread

From: Ron @ 2003-07-30 18:59 UTC (permalink / raw)
  To: pgsql-performance

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




^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: postgresql.conf
@ 2003-07-30 19:10  Josh Berkus <josh@agliodbs.com>
  parent: Ron <rstpierre@syscor.com>
  0 siblings, 1 reply; 11+ messages in thread

From: Josh Berkus @ 2003-07-30 19:10 UTC (permalink / raw)
  To: Ron <rstpierre@syscor.com>; pgsql-performance

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




^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: postgresql.conf
@ 2003-07-30 20:09  scott.marlowe <scott.marlowe@ihs.com>
  parent: Josh Berkus <josh@agliodbs.com>
  0 siblings, 2 replies; 11+ messages in thread

From: scott.marlowe @ 2003-07-30 20:09 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; +Cc: Ron <rstpierre@syscor.com>; pgsql-performance

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?




^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: postgresql.conf
@ 2003-07-30 20:42  Josh Berkus <josh@agliodbs.com>
  parent: scott.marlowe <scott.marlowe@ihs.com>
  1 sibling, 0 replies; 11+ messages in thread

From: Josh Berkus @ 2003-07-30 20:42 UTC (permalink / raw)
  To: scott.marlowe <scott.marlowe@ihs.com>; +Cc: Ron <rstpierre@syscor.com>; pgsql-performance

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




^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: postgresql.conf
@ 2003-07-31 14:43  cafweb <cafweb@lulu.com>
  parent: scott.marlowe <scott.marlowe@ihs.com>
  1 sibling, 0 replies; 11+ messages in thread

From: cafweb @ 2003-07-31 14:43 UTC (permalink / raw)
  To: pgsql-performance


>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

^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Fwd: Re: postgresql.conf
@ 2003-08-05 13:20  Hilary Forbes <hforbes@dmr.co.uk>
  0 siblings, 0 replies; 11+ messages in thread

From: Hilary Forbes @ 2003-08-05 13:20 UTC (permalink / raw)
  To: scott.marlowe@ihs.com; +Cc: Robert Treat <xzilla@users.sourceforge.net>; pgsql-performance

Scott

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

This is the situation I'm having fun and games with so I'd be very interested.  (Client has made the mistake of putting Mr Gates firewall in between the two to add to my woes!!) 

Looking forward to seeing the outcome of your documentation immensely.
Many thanks

Hilary



Hilary Forbes
-------------
DMR Computer Limited:   http://www.dmr.co.uk/
Direct line:  01689 889950
Switchboard:  (44) 1689 860000  Fax: (44) 1689 860330
E-mail:  hforbes@dmr.co.uk

**********************************************************




^ permalink  raw  reply  [nested|flat] 11+ messages in thread


end of thread, other threads:[~2003-08-05 13:20 UTC | newest]

Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-07-30 16:59 postgresql.conf scott.marlowe <scott.marlowe@ihs.com>
2003-07-30 17:19 ` Josh Berkus <josh@agliodbs.com>
2003-07-30 18:32 ` Ron Johnson <ron.l.johnson@cox.net>
2003-07-30 18:34 Re: postgresql.conf Robert Treat <xzilla@users.sourceforge.net>
2003-07-30 18:43 ` Josh Berkus <josh@agliodbs.com>
2003-07-30 18:59   ` Ron <rstpierre@syscor.com>
2003-07-30 19:10     ` Josh Berkus <josh@agliodbs.com>
2003-07-30 20:09       ` scott.marlowe <scott.marlowe@ihs.com>
2003-07-30 20:42         ` Josh Berkus <josh@agliodbs.com>
2003-07-31 14:43         ` cafweb <cafweb@lulu.com>
2003-08-05 13:20 Fwd: Re: postgresql.conf Hilary Forbes <hforbes@dmr.co.uk>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox