Thread: postgresql.conf.*
I just told yet another person to watch out that PgSQL ships with a conservative .conf file and would require tuning for best performance... Is there any reason we cannot ship with 3 .conf files? For each one, note the minimum system configuration required to support it. postgresql.conf.standard (current) postgresql.conf.medium (1Gb of RAM, IDE RAID 1 (7200RPM)) postgresql.conf.performance (4Gb of RAM, SCSI RAID 10 (10000RPM)) I guess this gets back to auto-tuning scripts, and the difficulty of that, but it seems silly that every single person who installs postgresql has to independently learn that no, their shared_buffers need to be turned up. Assuming they don't give up early and assume that PgSQL performance sucks (as popular myth would have them believe to start with). P.
On Wed, 2006-01-04 at 01:09, Paul Ramsey wrote: > I just told yet another person to watch out that PgSQL ships with a > conservative .conf file and would require tuning for best performance... > > Is there any reason we cannot ship with 3 .conf files? For each one, > note the minimum system configuration required to support it. > > postgresql.conf.standard (current) > postgresql.conf.medium (1Gb of RAM, IDE RAID 1 (7200RPM)) > postgresql.conf.performance (4Gb of RAM, SCSI RAID 10 (10000RPM)) > The problem here is that these numbers are all completely arbitrary, so that your attempt at a "performance" conf could actually cause bad performance for some people. > I guess this gets back to auto-tuning scripts, and the difficulty of > that, but it seems silly that every single person who installs > postgresql has to independently learn that no, their shared_buffers > need to be turned up. Assuming they don't give up early and assume > that PgSQL performance sucks (as popular myth would have them believe > to start with). > Just FYI Andrew Dunstan did a little bit of work in this area, see http://archives.postgresql.org/pgsql-committers/2005-12/msg00486.php for more details. Robert Treat -- Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
Paul, > Is there any reason we cannot ship with 3 .conf files? For each one, > note the minimum system configuration required to support it. > > postgresql.conf.standard (current) > postgresql.conf.medium (1Gb of RAM, IDE RAID 1 (7200RPM)) > postgresql.conf.performance (4Gb of RAM, SCSI RAID 10 (10000RPM)) Because too many of the conf parameters are dependant on *exactly* the amount of RAM that you have. So there isn't even a general file we can ship. Also medium/performance isn't adequate, we'd need: .mixed.small_machine .mixed.large_machine .web.small_machine .web.large_machine .warehouse.large_machine .... etc. > I guess this gets back to auto-tuning scripts, and the difficulty of > that, but it seems silly that every single person who installs > postgresql has to independently learn that no, their shared_buffers > need to be turned up. Assuming they don't give up early and assume > that PgSQL performance sucks (as popular myth would have them believe > to start with). Your help with our project is welcome: http://pgfoundry.org/projects/configurator/ -- --Josh Josh Berkus Aglio Database Solutions San Francisco
On Wed, 4 Jan 2006, Josh Berkus wrote: > Paul, > >> Is there any reason we cannot ship with 3 .conf files? For each one, >> note the minimum system configuration required to support it. >> >> postgresql.conf.standard (current) >> postgresql.conf.medium (1Gb of RAM, IDE RAID 1 (7200RPM)) >> postgresql.conf.performance (4Gb of RAM, SCSI RAID 10 (10000RPM)) > > Because too many of the conf parameters are dependant on *exactly* the > amount of RAM that you have. When we initdb, we try and come up with a suitable # for shared_buffers ... is there anything we can do to install "recommended values" for some of the other values that are memory dependent? At least put them into the ball park? ---- Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664
On Wed, Jan 04, 2006 at 12:10:28PM -0800, Josh Berkus wrote: > Paul, > > > Is there any reason we cannot ship with 3 .conf files? For each one, > > note the minimum system configuration required to support it. > > > > postgresql.conf.standard (current) > > postgresql.conf.medium (1Gb of RAM, IDE RAID 1 (7200RPM)) > > postgresql.conf.performance (4Gb of RAM, SCSI RAID 10 (10000RPM)) > > Because too many of the conf parameters are dependant on *exactly* the > amount of RAM that you have. So there isn't even a general file we can Such as? I can't really think of any, except maybe effective_cache_size, which isn't that critical. > ship. Also medium/performance isn't adequate, we'd need: > > .mixed.small_machine > .mixed.large_machine > .web.small_machine > .web.large_machine > .warehouse.large_machine Why are so many options needed? Every time this idea is brought up, people seem to think that the idea is to produce some ideal configuration out-of-the-box, which of course is exceptionally difficult. But *anything* would be far superior to what we have now. Clearly there's no replacement for actually going in and tuning things by hand, and anyone with any kind of database knowledge will know that. I know that there's efforts underway to improve upon the current default config, and that's great. But those efforts have been underway for at least 2 releases now, and I haven't seen any commitment that they'll be in 8.2. In the meantime, shipping a few extra config files is something we can do right now, and should have done back in 7.x, when the idea first came up. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 > Every time this idea is brought up, people seem to think that the idea > is to produce some ideal configuration out-of-the-box, which of course > is exceptionally difficult. But *anything* would be far superior to what > we have now. Clearly there's no replacement for actually going in and > tuning things by hand, and anyone with any kind of database knowledge > will know that. On a sort of related note, I recall lobbying for dropping the random_page_cost down from the current default of 4, but getting shot down because random_page_cost was part of a bigger picture and adjusting that alone was the Wrong Way To Do It. Nothing ever came of it however, so I invariably end up adjusting random_page_cost on every install I do. The bigger problem is obviously that we are targeting people who are running very old / very underpowered hardware. While it's nice to say that PG will run on almost anything "out of the box", I think it would be worth re-evaluating a more modern standard target machine every major release and/or annually. Perhaps something as simple as an average Dell desktop PC from two years ago. Then we could include another conf file for lesser machines (e.g. with lower memory settings). Plus another conf for top of the line machines, plus whatever other sample ones people want to write. Of course, the more expensive the box, the more likely is it that PG will be administered by someone who knows what they are doing, and will tweak things themselves anyway, and thus will not need any sample configuration files. Especially if a good tuning script becomes available. - -- Greg Sabino Mullane greg@turnstep.com PGP Key: 0x14964AC8 200601061114 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iD8DBQFDvph3vJuQZxSWSsgRAs+GAJ9w7syxr+R6+aWnFV8oh4qxPNcUPgCeJhXv hdhaYJbfwQitH/zqyQ+tgKw= =zRgX -----END PGP SIGNATURE-----
On Fri, Jan 06, 2006 at 04:20:15PM -0000, Greg Sabino Mullane wrote: > > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > > > Every time this idea is brought up, people seem to think that the idea > > is to produce some ideal configuration out-of-the-box, which of course > > is exceptionally difficult. But *anything* would be far superior to what > > we have now. Clearly there's no replacement for actually going in and > > tuning things by hand, and anyone with any kind of database knowledge > > will know that. > > On a sort of related note, I recall lobbying for dropping the > random_page_cost down from the current default of 4, but getting > shot down because random_page_cost was part of a bigger picture and > adjusting that alone was the Wrong Way To Do It. Nothing ever came of > it however, so I invariably end up adjusting random_page_cost on every > install I do. The issue is that testing of the cost of random vs sequential access on modern hard drives still shows that 4 is the correct value. The reason people end up decreasing it is because of shortcommings in the various estimator functions; decreasing random_page_cost brings the estimates closer to reality, but it's the wrong way to go about it. So it's another case of year after year of shipping a bad default configuration because "some day we'll get around to fixing the problem the right way". -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461