Обсуждение: optimum settings for dedicated box
Wondering what the optimum settings are for an dedicated postgresql database box? The box is an 2.8ghz processor, 1gig ram (soon will be 4) and raid 1 (mirroring) across two 10k rpm SCSI disks. I only have a single database on it running linux of course. Thanks. __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Mine in similar, and the only thing I have changed from defaults is work_mem. It made certain complex queries go from taking forever to taking seconds. I have a database connection pool limited to 10 connections, so I set it to 10MB. That means (to me, anyway) that work_mem will never gobble more then 100MB. Seems OK since I have 1GB. Free space map should probably be tweaked too, if you have lots of updates or deletes. I think. - Ian On 8/30/05, Matt A. <survivedsushi@yahoo.com> wrote: > Wondering what the optimum settings are for an > dedicated postgresql database box? The box is an > 2.8ghz processor, 1gig ram (soon will be 4) and raid 1 > (mirroring) across two 10k rpm SCSI disks. I only have > a single database on it running linux of course. Thanks. > > __________________________________________________ > Do You Yahoo!? > Tired of spam? Yahoo! Mail has the best spam protection around > http://mail.yahoo.com > > ---------------------------(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 >
On Tue, Aug 30, 2005 at 09:43:19PM -0700, Ian Harding wrote: > Mine in similar, and the only thing I have changed from defaults is > work_mem. It made certain complex queries go from taking forever to > taking seconds. I have a database connection pool limited to 10 > connections, so I set it to 10MB. That means (to me, anyway) that > work_mem will never gobble more then 100MB. Seems OK since I have > 1GB. That's not totally true. A single query can use work_mem for multiple steps, so if work_mem is 10MB a single query could end up using 20MB, 30MB, or even more. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com 512-569-9461
Hmmm. I was thinking of a more comprehensive solution or document resource. I would like to know what does what. Why tweak that or why not to ya know? Searching gets me such fragmented results I chose to ask the ones whom are more familiar with this fabulous piece of software and used it in real world situations. Does anyone know of a some good docs on the subject of dedicated db optimization for postgresql 8.0.3? Is 8.1 to early to use in a production environment? With just the regular old 8.0.3 stuff? Thanks for the tips too. I always appreciate tips. :) Thanks again, Matthew A. Peter --- "Jim C. Nasby" <jnasby@pervasive.com> wrote: > On Tue, Aug 30, 2005 at 09:43:19PM -0700, Ian > Harding wrote: > > Mine in similar, and the only thing I have changed > from defaults is > > work_mem. It made certain complex queries go from > taking forever to > > taking seconds. I have a database connection pool > limited to 10 > > connections, so I set it to 10MB. That means (to > me, anyway) that > > work_mem will never gobble more then 100MB. Seems > OK since I have > > 1GB. > > That's not totally true. A single query can use > work_mem for multiple > steps, so if work_mem is 10MB a single query could > end up using 20MB, > 30MB, or even more. > -- > Jim C. Nasby, Sr. Engineering Consultant > jnasby@pervasive.com > Pervasive Software http://pervasive.com > 512-569-9461 > > ---------------------------(end of > broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please > send an appropriate > subscribe-nomail command to > majordomo@postgresql.org so that your > message can get through to the mailing list > cleanly > __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
On Wed, Aug 31, 2005 at 00:50:20 -0700, Matthew Peter <survivedsushi@yahoo.com> wrote: > > Is 8.1 to early to use in a production environment? > With just the regular old 8.0.3 stuff? 8.1 is still in early beta and you definitely don't want to use it in production. It has some nice improvements, so you at least want to look at the tentative release notes to see if you might want to upgrade to it sooner rather than later. 8.0.4 will be being released shortly (probably in a few days) and you will want to use that in preference to 8.0.3.
On Wed, 2005-08-31 at 00:50 -0700, Matthew Peter wrote: > Hmmm. I was thinking of a more comprehensive solution > or document resource. I would like to know what does > what. Why tweak that or why not to ya know? Matt, I've found the annotated postgresql.conf references on this page (as well as rest of the site) to be helpful. <http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php> -K