Thread: postgresql.conf
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...
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
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 | +-----------------------------------------------------------------+
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
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
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
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
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?
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
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...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. 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