Thread: Performance and Generic Config after install
As an aside to the "[GENERAL] Advantages of PostgreSQL" thread going on today, I have wondered why the initial on install config of PostgreSQL is (according to most posts) very conservative. I can see how this would be a plus when people may be getting PostgreSQL as part of an OS in the Linux world who may never/rarely use it. I know that in reality tuning and sizing all of the parameters is a very database specific thing, but it would seem that if some default choices would be available it would give those testing/evaluation and trying to get started a shot at quicker progress. Obviously they would still need to tune to your own application. Some dreadfully named, possibly pointless options? Unoptimized / low performance - Low load on Server (The current out of the box) Production Non Dedicated - PostgreSQL is one of the apps sharing server but is important. Production Dedicated Server - The only purpose of this box is to run PostgreSQL Perhaps this has already been suggested and or shot down? Oisin
Attachment
On Mon, Oct 02, 2006 at 02:40:03PM -0400, Oisin Glynn wrote: > As an aside to the "[GENERAL] Advantages of PostgreSQL" thread going on > today, I have wondered why the initial on install config of PostgreSQL > is (according to most posts) very conservative. I can see how this Actually, that's a bit of a hangover in much the way the reputation of MySQL as having no support for ACID is: the last couple of releases of Postgres try to make at least some effort at estimating sane but safe basic configuration for the system when it's installed. That said, there is something of a problem in auto-configuring a cost-based planner and optimiser: some of the tuning is likely to be extremely sensitive to other things you're doing on the box, which means that you need to do a good, careful job for optimal performance. I often hear people complaining about this feature of Postgres in comparison to MySQL. But it's not a reasonable comparison, because MySQL basically uses a rule-based optimiser. And systems like DB2 and Oracle, that use a cost-based optimiser, are often far from perfect after a fresh install, too. A -- Andrew Sullivan | ajs@crankycanuck.ca The plural of anecdote is not data. --Roger Brinner
On Mon, 2006-10-02 at 14:40 -0400, Oisin Glynn wrote: > As an aside to the "[GENERAL] Advantages of PostgreSQL" thread going on > today, I have wondered why the initial on install config of PostgreSQL > is (according to most posts) very conservative. I can see how this > would be a plus when people may be getting PostgreSQL as part of an OS > in the Linux world who may never/rarely use it. > > I know that in reality tuning and sizing all of the parameters is a very > database specific thing, but it would seem that if some default choices > would be available it would give those testing/evaluation and trying to > get started a shot at quicker progress. Obviously they would still need > to tune to your own application. > > Some dreadfully named, possibly pointless options? > > Unoptimized / low performance - Low load on Server (The current out > of the box) Keep in mind that PostgreSQL doesn't really restrict itself as a whole. If you set the settings too low, and throw costly queries at it, the load on the server will be very high. We don't want to imply that PostgreSQL's settings restrict it's cpu, memory, or disk usage as a whole. > Production Non Dedicated - PostgreSQL is one of the apps sharing > server but is important. > Production Dedicated Server - The only purpose of this box is to run > PostgreSQL > In my opinion, this is more the job of distributions packaging it. Distributions have these advantages when they are choosing the settings: (1) They have more information about the target computer (2) They have more information about the intended use of the system as a whole (3) They have more ability to ask questions of the user PostgreSQL itself can't easily do those things in a portable way. If someone is compiling from source, it is more reasonable to expect them to know what settings to use. However, that said, I think that distributions certainly do take a cue from the default settings in the source distribution. That's why lately the default settings have been growing more aggressive with each release. Regards, Jeff Davis
I think the problem would be partly mitigated be better or more obvious documentation that makes it clear that a) PostgreSQL is probably not configured optimally, and b) where exactly to go to get server optimization information. Even basic docs on postgresql.conf seem lacking. The fact that something like these exist: http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html http://www.varlena.com/GeneralBits/Tidbits/perf.html#conf Should be a giant red flag that documentation is a tad sparse for the config file. Those docs would take hours of work, they're the only thing I've really found, *and they're still 3 years out of date*. It took me a lot of digging to find the docs on optimizing PostgreSQL from postgresql.org. It's in the documentation area, but it's not documented in the manual at all that I could find (which is highly counter-intuitive). Instead, it's listed under 'Technical Documentation' (making the manual casual documentation? I thought all Postgre docs were technical.) then under 'Community Guides and Docs', and finally under the subheading Optimizing (note that the above links are listed here): http://www.postgresql.org/docs/techdocs.2 Either the server installer or the (preferably) the manual needs to make it very clear about this documentation. If nobody can find it nobody will use it, and it's very well hidden at the moment. The manual gets updated with every release, but more and more I realize that the manual isn't comprehensive. The manual explains the SQL syntax and how PostgreSQL interprets the relational model, but it has very little information on how to really *use* PostgreSQL as a server. The manual is all app dev and no sysadmin. For example, *what* compile time options are available? I know they exist, but I've never seen them listed. For another example, take a look at this so-called detailed guide to installing PG on Fedora, which is linked from the 'Technical Documentation' area of postgresql.org: http://dancameron.org/pages/how-to-install-and-setup-postgresql-for-fedo ralinux/ Now, really, this 'guide' is little more than what yum command to run and which config lines to edit to limit remote TCP connections. Now take a look at the first comment: "Thanks for the advice. For an Oracle DBA this really helped me in comming up to speed on Postgres administration." There should be an Administration Guide companion to the Manual. -- Brandon Aiken CS/IT Systems Engineer -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Jeff Davis Sent: Monday, October 02, 2006 2:58 PM To: Oisin Glynn Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Performance and Generic Config after install On Mon, 2006-10-02 at 14:40 -0400, Oisin Glynn wrote: > As an aside to the "[GENERAL] Advantages of PostgreSQL" thread going on > today, I have wondered why the initial on install config of PostgreSQL > is (according to most posts) very conservative. I can see how this > would be a plus when people may be getting PostgreSQL as part of an OS > in the Linux world who may never/rarely use it. > > I know that in reality tuning and sizing all of the parameters is a very > database specific thing, but it would seem that if some default choices > would be available it would give those testing/evaluation and trying to > get started a shot at quicker progress. Obviously they would still need > to tune to your own application. > > Some dreadfully named, possibly pointless options? > > Unoptimized / low performance - Low load on Server (The current out > of the box) Keep in mind that PostgreSQL doesn't really restrict itself as a whole. If you set the settings too low, and throw costly queries at it, the load on the server will be very high. We don't want to imply that PostgreSQL's settings restrict it's cpu, memory, or disk usage as a whole. > Production Non Dedicated - PostgreSQL is one of the apps sharing > server but is important. > Production Dedicated Server - The only purpose of this box is to run > PostgreSQL > In my opinion, this is more the job of distributions packaging it. Distributions have these advantages when they are choosing the settings: (1) They have more information about the target computer (2) They have more information about the intended use of the system as a whole (3) They have more ability to ask questions of the user PostgreSQL itself can't easily do those things in a portable way. If someone is compiling from source, it is more reasonable to expect them to know what settings to use. However, that said, I think that distributions certainly do take a cue from the default settings in the source distribution. That's why lately the default settings have been growing more aggressive with each release. Regards, Jeff Davis ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
Patches welcome. :) BTW, -docs or -www might be a better place to discuss this. On Mon, Oct 02, 2006 at 05:11:20PM -0400, Brandon Aiken wrote: > I think the problem would be partly mitigated be better or more obvious > documentation that makes it clear that a) PostgreSQL is probably not > configured optimally, and b) where exactly to go to get server > optimization information. Even basic docs on postgresql.conf seem > lacking. The fact that something like these exist: > > http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html > > http://www.varlena.com/GeneralBits/Tidbits/perf.html#conf > > Should be a giant red flag that documentation is a tad sparse for the > config file. Those docs would take hours of work, they're the only > thing I've really found, *and they're still 3 years out of date*. > > It took me a lot of digging to find the docs on optimizing PostgreSQL > from postgresql.org. It's in the documentation area, but it's not > documented in the manual at all that I could find (which is highly > counter-intuitive). Instead, it's listed under 'Technical > Documentation' (making the manual casual documentation? I thought all > Postgre docs were technical.) then under 'Community Guides and Docs', > and finally under the subheading Optimizing (note that the above links > are listed here): > > http://www.postgresql.org/docs/techdocs.2 > > Either the server installer or the (preferably) the manual needs to make > it very clear about this documentation. If nobody can find it nobody > will use it, and it's very well hidden at the moment. > > The manual gets updated with every release, but more and more I realize > that the manual isn't comprehensive. The manual explains the SQL syntax > and how PostgreSQL interprets the relational model, but it has very > little information on how to really *use* PostgreSQL as a server. The > manual is all app dev and no sysadmin. For example, *what* compile time > options are available? I know they exist, but I've never seen them > listed. > > For another example, take a look at this so-called detailed guide to > installing PG on Fedora, which is linked from the 'Technical > Documentation' area of postgresql.org: > > http://dancameron.org/pages/how-to-install-and-setup-postgresql-for-fedo > ralinux/ > > Now, really, this 'guide' is little more than what yum command to run > and which config lines to edit to limit remote TCP connections. > > Now take a look at the first comment: > "Thanks for the advice. For an Oracle DBA this really helped me in > comming up to speed on Postgres administration." > > There should be an Administration Guide companion to the Manual. > > -- > Brandon Aiken > CS/IT Systems Engineer > -----Original Message----- > From: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Jeff Davis > Sent: Monday, October 02, 2006 2:58 PM > To: Oisin Glynn > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Performance and Generic Config after install > > On Mon, 2006-10-02 at 14:40 -0400, Oisin Glynn wrote: > > As an aside to the "[GENERAL] Advantages of PostgreSQL" thread going > on > > today, I have wondered why the initial on install config of PostgreSQL > > > is (according to most posts) very conservative. I can see how this > > would be a plus when people may be getting PostgreSQL as part of an OS > > > in the Linux world who may never/rarely use it. > > > > I know that in reality tuning and sizing all of the parameters is a > very > > database specific thing, but it would seem that if some default > choices > > would be available it would give those testing/evaluation and trying > to > > get started a shot at quicker progress. Obviously they would still > need > > to tune to your own application. > > > > Some dreadfully named, possibly pointless options? > > > > Unoptimized / low performance - Low load on Server (The current out > > > of the box) > > Keep in mind that PostgreSQL doesn't really restrict itself as a whole. > If you set the settings too low, and throw costly queries at it, the > load on the server will be very high. We don't want to imply that > PostgreSQL's settings restrict it's cpu, memory, or disk usage as a > whole. > > > Production Non Dedicated - PostgreSQL is one of the apps sharing > > server but is important. > > Production Dedicated Server - The only purpose of this box is to run > > > PostgreSQL > > > > In my opinion, this is more the job of distributions packaging it. > Distributions have these advantages when they are choosing the settings: > > (1) They have more information about the target computer > (2) They have more information about the intended use of the system as a > whole > (3) They have more ability to ask questions of the user > > PostgreSQL itself can't easily do those things in a portable way. If > someone is compiling from source, it is more reasonable to expect them > to know what settings to use. > > However, that said, I think that distributions certainly do take a cue > from the default settings in the source distribution. That's why lately > the default settings have been growing more aggressive with each > release. > > Regards, > Jeff Davis > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match > -- Jim Nasby jim@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
Yes, patches! Seriously, someone from the performance list and/or hackers should be actively collecting data from discussions regarding changes to the postgresql.conf file. We really need someone on top of this to write the annotated postgresql.conf for 8.2. Josh seems to have no time to do it again and that is a big loss. (No I cannot volunteer since I have not finished my other project I am volunteering on yet.) --elein elein@varlena.com On Mon, Oct 02, 2006 at 04:14:40PM -0500, Jim C. Nasby wrote: > Patches welcome. :) > > BTW, -docs or -www might be a better place to discuss this. > > On Mon, Oct 02, 2006 at 05:11:20PM -0400, Brandon Aiken wrote: > > I think the problem would be partly mitigated be better or more obvious > > documentation that makes it clear that a) PostgreSQL is probably not > > configured optimally, and b) where exactly to go to get server > > optimization information. Even basic docs on postgresql.conf seem > > lacking. The fact that something like these exist: > > > > http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html > > > > http://www.varlena.com/GeneralBits/Tidbits/perf.html#conf > > > > Should be a giant red flag that documentation is a tad sparse for the > > config file. Those docs would take hours of work, they're the only > > thing I've really found, *and they're still 3 years out of date*. > > > > It took me a lot of digging to find the docs on optimizing PostgreSQL > > from postgresql.org. It's in the documentation area, but it's not > > documented in the manual at all that I could find (which is highly > > counter-intuitive). Instead, it's listed under 'Technical > > Documentation' (making the manual casual documentation? I thought all > > Postgre docs were technical.) then under 'Community Guides and Docs', > > and finally under the subheading Optimizing (note that the above links > > are listed here): > > > > http://www.postgresql.org/docs/techdocs.2 > > > > Either the server installer or the (preferably) the manual needs to make > > it very clear about this documentation. If nobody can find it nobody > > will use it, and it's very well hidden at the moment. > > > > The manual gets updated with every release, but more and more I realize > > that the manual isn't comprehensive. The manual explains the SQL syntax > > and how PostgreSQL interprets the relational model, but it has very > > little information on how to really *use* PostgreSQL as a server. The > > manual is all app dev and no sysadmin. For example, *what* compile time > > options are available? I know they exist, but I've never seen them > > listed. > > > > For another example, take a look at this so-called detailed guide to > > installing PG on Fedora, which is linked from the 'Technical > > Documentation' area of postgresql.org: > > > > http://dancameron.org/pages/how-to-install-and-setup-postgresql-for-fedo > > ralinux/ > > > > Now, really, this 'guide' is little more than what yum command to run > > and which config lines to edit to limit remote TCP connections. > > > > Now take a look at the first comment: > > "Thanks for the advice. For an Oracle DBA this really helped me in > > comming up to speed on Postgres administration." > > > > There should be an Administration Guide companion to the Manual. > > > > -- > > Brandon Aiken > > CS/IT Systems Engineer > > -----Original Message----- > > From: pgsql-general-owner@postgresql.org > > [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Jeff Davis > > Sent: Monday, October 02, 2006 2:58 PM > > To: Oisin Glynn > > Cc: pgsql-general@postgresql.org > > Subject: Re: [GENERAL] Performance and Generic Config after install > > > > On Mon, 2006-10-02 at 14:40 -0400, Oisin Glynn wrote: > > > As an aside to the "[GENERAL] Advantages of PostgreSQL" thread going > > on > > > today, I have wondered why the initial on install config of PostgreSQL > > > > > is (according to most posts) very conservative. I can see how this > > > would be a plus when people may be getting PostgreSQL as part of an OS > > > > > in the Linux world who may never/rarely use it. > > > > > > I know that in reality tuning and sizing all of the parameters is a > > very > > > database specific thing, but it would seem that if some default > > choices > > > would be available it would give those testing/evaluation and trying > > to > > > get started a shot at quicker progress. Obviously they would still > > need > > > to tune to your own application. > > > > > > Some dreadfully named, possibly pointless options? > > > > > > Unoptimized / low performance - Low load on Server (The current out > > > > > of the box) > > > > Keep in mind that PostgreSQL doesn't really restrict itself as a whole. > > If you set the settings too low, and throw costly queries at it, the > > load on the server will be very high. We don't want to imply that > > PostgreSQL's settings restrict it's cpu, memory, or disk usage as a > > whole. > > > > > Production Non Dedicated - PostgreSQL is one of the apps sharing > > > server but is important. > > > Production Dedicated Server - The only purpose of this box is to run > > > > > PostgreSQL > > > > > > > In my opinion, this is more the job of distributions packaging it. > > Distributions have these advantages when they are choosing the settings: > > > > (1) They have more information about the target computer > > (2) They have more information about the intended use of the system as a > > whole > > (3) They have more ability to ask questions of the user > > > > PostgreSQL itself can't easily do those things in a portable way. If > > someone is compiling from source, it is more reasonable to expect them > > to know what settings to use. > > > > However, that said, I think that distributions certainly do take a cue > > from the default settings in the source distribution. That's why lately > > the default settings have been growing more aggressive with each > > release. > > > > Regards, > > Jeff Davis > > > > > > > > > > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 2: Don't 'kill -9' the postmaster > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 9: In versions below 8.0, the planner will ignore your desire to > > choose an index scan if your joining column's datatypes do not > > match > > > > -- > Jim Nasby jim@nasby.net > EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster >