Re: [HACKERS] Offering tuned config files
No, not really - I can do some more testing with pgbench to see what happens though...I'll do it on monday Chris On Fri, 14 Feb 2003, Tom Lane wrote: Manfred Koizar [EMAIL PROTECTED] writes: In postgresql.conf.sample-writeheavy you have: commit_delay = 1 Is this still needed with ganged WAL writes? Tom? I doubt that the current options for grouped commits are worth anything at the moment. Chris, do you have any evidence backing up using commit_delay with 7.3? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Offering tuned config files
On Fri, 14 Feb 2003 14:12:50 +0800, Christopher Kings-Lynne [EMAIL PROTECTED] wrote: Here's a stab at some extra conf files. Feel free to shoot them down. No intent to shoot anything down, just random thoughts: effective_cache_size = 2 (~ 160 MB) should be more adequate for a 256 MB machine than the extremely conservative default of 1000. I admit that the effect of this change is hard to benchmark. A way too low (or too high) setting may lead the planner to wrong conclusions. More parameters affecting the planner: #cpu_tuple_cost = 0.01 #cpu_index_tuple_cost = 0.001 #cpu_operator_cost = 0.0025 Are these still good defaults? I have no hard facts, but ISTM that CPU speed is increasing more rapidly than disk access speed. In postgresql.conf.sample-writeheavy you have: commit_delay = 1 Is this still needed with ganged WAL writes? Tom? Servus Manfred ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Offering tuned config files
Manfred Koizar said: effective_cache_size = 2 (~ 160 MB) should be more adequate for a 256 MB machine than the extremely conservative default of 1000. I admit that the effect of this change is hard to benchmark. A way too low (or too high) setting may lead the planner to wrong conclusions. The default on BSD systems is 10% of the total RAM, so on a 256MB machine this would be ~26MB or effective_cache_size = 32000. One could always modify the kernel to support much larger value, but I doubt this is done in many cases and the usefulness of larger buffer cache is not obvious in the presence of many fsync calls (which might be typicall). I could be wrong, of course :) In any case, the default is indeed low and would prevent using indexes on larger tables, where they are most useful. Daniel ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Offering tuned config files
On Fri, 14 Feb 2003 14:24:23 +0200, Daniel Kalchev [EMAIL PROTECTED] wrote: The default [cache] on BSD systems is 10% of the total RAM, so on a 256MB machine this would be ~26MB or effective_cache_size = 32000. I was a bit too Linux-minded, where every peace of memory not needed for anything else can be used as cache. Thanks for the clarification. And sorry for my ignorance. BTW 26MB ~ effective_cache_size = 3200. Servus Manfred ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Offering tuned config files
Manfred Koizar [EMAIL PROTECTED] writes: In postgresql.conf.sample-writeheavy you have: commit_delay = 1 Is this still needed with ganged WAL writes? Tom? I doubt that the current options for grouped commits are worth anything at the moment. Chris, do you have any evidence backing up using commit_delay with 7.3? regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Offering tuned config files
On Fri, 2003-02-14 at 07:41, Manfred Koizar wrote: On Fri, 14 Feb 2003 14:24:23 +0200, Daniel Kalchev [EMAIL PROTECTED] wrote: The default [cache] on BSD systems is 10% of the total RAM, so on a 256MB machine this would be ~26MB or effective_cache_size = 32000. I was a bit too Linux-minded, where every peace of memory not needed for anything else can be used as cache. Thanks for the clarification. And sorry for my ignorance. I think you're getting the two confused. I'm not sure about linux, but on BSD (FreeBSD) the cache and buffer are mostly unrelated. Cache: number of pages used for VM-level disk caching Buf: number of pages used for BIO-level disk caching -- Rod Taylor [EMAIL PROTECTED] PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
[HACKERS] Offering tuned config files
OK, Here's a stab at some extra conf files. Feel free to shoot them down. If we can come up with at least _some_ alternative files that we can put somewhere for them to see when postgres is installed, then at least people can see what variables will affect what... I didn't see the point of a 'workstation' option, the default is fine for that. Chris -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Kevin Brown Sent: Friday, 14 February 2003 11:26 AM To: PostgresSQL Hackers Mailing List; [EMAIL PROTECTED] Subject: Re: [HACKERS] Changing the default configuration (was Re: [pgsql-advocacy] Josh Berkus wrote: Uh ... do we have a basis for recommending any particular sets of parameters for these different scenarios? This could be a good idea in the abstract, but I'm not sure I know enough to fill in the details. Sure. Mostly-Read database, few users, good hardware, complex queries: = High shared buffers and sort mem, high geqo and join collapse thresholds, moderate fsm settings, defaults for WAL. Same as above with many users and simple queries (webserver) = same as above, except lower sort mem and higher connection limit High-Transaction Database = Moderate shared buffers and sort mem, high FSM settings, increase WAL files and buffers. Workstation = Moderate to low shared buffers and sort mem, moderate FSM, defaults for WAL, etc. Low-Impact server = current defaults, more or less. Okay, but there should probably be one more, called Benchmark. The real problem is what values to use for it. :-) -- Kevin Brown [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] postgresql.conf.sample-olap Description: Binary data postgresql.conf.sample-web Description: Binary data postgresql.conf.sample-writeheavy Description: Binary data ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html