pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedpostgresql.conf
11+ messages / 7 participants
[nested] [flat]
* postgresql.conf
@ 2003-07-30 16:59 scott.marlowe <scott.marlowe@ihs.com>
2003-07-30 17:19 ` Re: postgresql.conf Josh Berkus <josh@agliodbs.com>
2003-07-30 18:32 ` Re: postgresql.conf Ron Johnson <ron.l.johnson@cox.net>
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 16:59 postgresql.conf scott.marlowe <scott.marlowe@ihs.com>
@ 2003-07-30 17:19 ` Josh Berkus <josh@agliodbs.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 16:59 postgresql.conf scott.marlowe <scott.marlowe@ihs.com>
@ 2003-07-30 18:32 ` Ron Johnson <ron.l.johnson@cox.net>
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>
2003-07-30 18:43 ` Re: postgresql.conf Josh Berkus <josh@agliodbs.com>
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: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 ` Re: postgresql.conf Ron <rstpierre@syscor.com>
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:34 Re: postgresql.conf Robert Treat <xzilla@users.sourceforge.net>
2003-07-30 18:43 ` Re: postgresql.conf Josh Berkus <josh@agliodbs.com>
@ 2003-07-30 18:59 ` Ron <rstpierre@syscor.com>
2003-07-30 19:10 ` Re: postgresql.conf 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 18:34 Re: postgresql.conf Robert Treat <xzilla@users.sourceforge.net>
2003-07-30 18:43 ` Re: postgresql.conf Josh Berkus <josh@agliodbs.com>
2003-07-30 18:59 ` Re: postgresql.conf Ron <rstpierre@syscor.com>
@ 2003-07-30 19:10 ` Josh Berkus <josh@agliodbs.com>
2003-07-30 20:09 ` Re: postgresql.conf scott.marlowe <scott.marlowe@ihs.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 18:34 Re: postgresql.conf Robert Treat <xzilla@users.sourceforge.net>
2003-07-30 18:43 ` Re: postgresql.conf Josh Berkus <josh@agliodbs.com>
2003-07-30 18:59 ` Re: postgresql.conf Ron <rstpierre@syscor.com>
2003-07-30 19:10 ` Re: postgresql.conf Josh Berkus <josh@agliodbs.com>
@ 2003-07-30 20:09 ` scott.marlowe <scott.marlowe@ihs.com>
2003-07-30 20:42 ` Re: postgresql.conf Josh Berkus <josh@agliodbs.com>
2003-07-31 14:43 ` Re: postgresql.conf cafweb <cafweb@lulu.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 18:34 Re: postgresql.conf Robert Treat <xzilla@users.sourceforge.net>
2003-07-30 18:43 ` Re: postgresql.conf Josh Berkus <josh@agliodbs.com>
2003-07-30 18:59 ` Re: postgresql.conf Ron <rstpierre@syscor.com>
2003-07-30 19:10 ` Re: postgresql.conf Josh Berkus <josh@agliodbs.com>
2003-07-30 20:09 ` Re: postgresql.conf scott.marlowe <scott.marlowe@ihs.com>
@ 2003-07-30 20:42 ` Josh Berkus <josh@agliodbs.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-30 18:34 Re: postgresql.conf Robert Treat <xzilla@users.sourceforge.net>
2003-07-30 18:43 ` Re: postgresql.conf Josh Berkus <josh@agliodbs.com>
2003-07-30 18:59 ` Re: postgresql.conf Ron <rstpierre@syscor.com>
2003-07-30 19:10 ` Re: postgresql.conf Josh Berkus <josh@agliodbs.com>
2003-07-30 20:09 ` Re: postgresql.conf scott.marlowe <scott.marlowe@ihs.com>
@ 2003-07-31 14:43 ` cafweb <cafweb@lulu.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