Thread
-
Re: Tuning for mid-size server
Anjan Dave <adave@vantage.com> — 2003-10-21T17:02:08Z
Josh, The 6650 can have upto 32GB of RAM. There are 5 drives. In future, they will be replaced by a fiber array - hopefully. I read an article that suggests you 'start' with 25% of memory for shared_buffers. Sort memory was suggested to be at 2-4%. Here's the link: http://www.ca.postgresql.org/docs/momjian/hw_performance/node8.html Maybe, I misinterpreted it. I read the document on http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html and the suggested values are much lower than what I have mentioned here. It won't hurt to start with lower numbers and increase lateron if needed. Thanks, Anjan -----Original Message----- From: Josh Berkus [mailto:josh@agliodbs.com] Sent: Tuesday, October 21, 2003 12:21 PM To: Anjan Dave; pgsql-performance@postgresql.org Subject: Re: [PERFORM] Tuning for mid-size server Anjan, > Pretty soon, a PowerEdge 6650 with 4 x 2Ghz XEONs, and 8GB Memory, > with internal drives on RAID5 will be delivered. Postgres will be from > RH8.0. How many drives? RAID5 sucks for heavy read-write databases, unless you have 5+ drives. Or a large battery-backed cache. Also, last I checked, you can't address 8GB of RAM without a 64-bit processor. Since when are the Xeons 64-bit? > Shared_buffers (25% of RAM / 8KB)) = 8589934592 * .25 / 8192 = 262144 That's too high. Cut it in half at least. Probably down to 5% of available RAM. > Sort_mem (4% of RAM / 1KB) = 335544. We'll take about half of that - > 167772 Fine if you're running a few-user-large-operation database. If this is a webserver, you want a much, much lower value. > Effective_cache_size = 262144 (same as shared_buffers - 25%) Much too low. Where did you get these calculations, anyway? > In a generic sense, these are recommended values I found in some > documents. Where? We need to contact the author of the "documents" and tell them to correct things. > joins, orderby, groupby clauses. The web application is based on > Apache/Resin and hotspot JVM 1.4.0. You'll need to estimate the memory consumed by Java & Apache to have realistic figures to work with. > Are the above settings ok to begin with? Are there any other > parameters that I should configure now, or monitor lateron? No, they're not. See: http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html to tune these parameters. -- Josh Berkus Aglio Database Solutions San Francisco
-
Re: Tuning for mid-size server
Josh Berkus <josh@agliodbs.com> — 2003-10-21T17:15:57Z
Anjan, > I read an article that suggests you 'start' with 25% of memory for > shared_buffers. Sort memory was suggested to be at 2-4%. Here's the > link: > http://www.ca.postgresql.org/docs/momjian/hw_performance/node8.html > Maybe, I misinterpreted it. No, I can see how you arrived at that conclusion, and Bruce is an authority. I'll contact him. -- Josh Berkus Aglio Database Solutions San Francisco
-
Re: Tuning for mid-size server
Andrew Sullivan <andrew@libertyrms.info> — 2003-10-21T17:50:17Z
On Tue, Oct 21, 2003 at 10:15:57AM -0700, Josh Berkus wrote: > Anjan, > > > I read an article that suggests you 'start' with 25% of memory for > > shared_buffers. Sort memory was suggested to be at 2-4%. Here's the > > link: > > http://www.ca.postgresql.org/docs/momjian/hw_performance/node8.html > > Maybe, I misinterpreted it. > > No, I can see how you arrived at that conclusion, and Bruce is an authority. > I'll contact him. I think the "25%" rule of thumb is slightly stale: above some threshold, it just falls apart, and lots of people now have machines well within that threshold. Heck, I'll bet Bruce's 2-way machine is within that threshold. A -- ---- Andrew Sullivan 204-4141 Yonge Street Afilias Canada Toronto, Ontario Canada <andrew@libertyrms.info> M2P 2A8 +1 416 646 3304 x110 -
Re: Tuning for mid-size server
Tom Lane <tgl@sss.pgh.pa.us> — 2003-10-21T18:43:47Z
Andrew Sullivan <andrew@libertyrms.info> writes: > I think the "25%" rule of thumb is slightly stale: above some > threshold, it just falls apart, and lots of people now have machines > well within that threshold. Heck, I'll bet Bruce's 2-way machine is > within that threshold. IIRC, we've not seen much evidence that increasing shared_buffers above about 10000 delivers any performance boost. That's 80Mb, so the "25%" rule doesn't get seriously out of whack until you get to a gig or so of RAM. Which was definitely not common at the time the rule was put forward, but is now. Probably we should modify the rule-of-thumb to something like "25%, but not more than 10000 buffers". regards, tom lane
-
Re: Tuning for mid-size server
Josh Berkus <josh@agliodbs.com> — 2003-10-21T18:51:02Z
Andrew, > I think the "25%" rule of thumb is slightly stale: above some > threshold, it just falls apart, and lots of people now have machines > well within that threshold. Heck, I'll bet Bruce's 2-way machine is > within that threshold. Sure. But we had a few people on this list do tests (including me) and the anecdotal evidence was lower than 25%, substantially. The falloff is subtle until you hit 50% of RAM, like: % query throughput 1 ---- 5 --------- 10 ----------- 15 ---------- 20 ---------- 25 --------- 30 -------- 35 -------- 40 ------- ... so it's often not immediately apparent when you've set stuff a little too high. However, in the folks that tested, the ideal was never anywhere near 25%, usually more in the realm of 5-10%. I've been using 6% as my starting figure for the last year for a variety of servers with good results. Of course, if you have anecdotal evidence to the contrary, then the only way to work this would be to have OSDL help us sort it out. -- -Josh Berkus Aglio Database Solutions San Francisco
-
Re: Tuning for mid-size server
Andrew Sullivan <andrew@libertyrms.info> — 2003-10-21T20:55:04Z
On Tue, Oct 21, 2003 at 11:51:02AM -0700, Josh Berkus wrote: > Of course, if you have anecdotal evidence to the contrary, then the > only way to work this would be to have OSDL help us sort it out. Nope. I too have such anecdotal evidence that 25% is way too high. It also seems to depend pretty heavily on what you're trying to optimise for and what platform you have. But I'm glad to hear (again) that people seem to think the 25% too high for most cases. I don't feel so much like I'm tilting against windmills. A -- ---- Andrew Sullivan 204-4141 Yonge Street Afilias Canada Toronto, Ontario Canada <andrew@libertyrms.info> M2P 2A8 +1 416 646 3304 x110 -
Re: Tuning for mid-size server
scott.marlowe <scott.marlowe@ihs.com> — 2003-10-21T21:11:17Z
On Tue, 21 Oct 2003, Andrew Sullivan wrote: > On Tue, Oct 21, 2003 at 11:51:02AM -0700, Josh Berkus wrote: > > > Of course, if you have anecdotal evidence to the contrary, then the > > only way to work this would be to have OSDL help us sort it out. > > Nope. I too have such anecdotal evidence that 25% is way too high. > It also seems to depend pretty heavily on what you're trying to > optimise for and what platform you have. But I'm glad to hear > (again) that people seem to think the 25% too high for most cases. I > don't feel so much like I'm tilting against windmills. I think where it makes sense is when you have something like a report server where the result sets may be huge, but the parellel load is load, i.e. 5 or 10 users tossing around 100 Meg or more at time. If you've got 5,000 users running queries that are indexed and won't be using that much memory each, then there's usually no advantage to going over a certain number of buffers, and that certain number may be as low as 1000 for some applications.
-
Re: Tuning for mid-size server
Josh Berkus <josh@agliodbs.com> — 2003-10-21T21:32:16Z
Scott, > I think where it makes sense is when you have something like a report > server where the result sets may be huge, but the parellel load is load, > i.e. 5 or 10 users tossing around 100 Meg or more at time. I've found that that question makes the difference between using 6% & 12% ... particularly large data transformations ... but not higher than that. And I've had ample opportunity to test on 2 reporting servers. For one thing, with very large reports one tends to have a lot of I/O binding, which is handled by the kernel. -- -Josh Berkus Aglio Database Solutions San Francisco
-
Re: Tuning for mid-size server
Andrew Sullivan <andrew@libertyrms.info> — 2003-10-21T21:34:08Z
On Tue, Oct 21, 2003 at 03:11:17PM -0600, scott.marlowe wrote: > I think where it makes sense is when you have something like a report > server where the result sets may be huge, but the parellel load is load, > i.e. 5 or 10 users tossing around 100 Meg or more at time. In our case, we were noticing that truss showed an unbelievable amount of time spent by the postmaster doing open() calls to the OS (this was on Solaris 7). So we thought, "Let's try a 2G buffer size." 2G was more than enough to hold the entire data set under question. Once the buffer started to fill, even plain SELECTs started taking a long time. The buffer algorithm is just not that clever, was my conclusion. (Standard disclaimer: not a long, controlled test. It's just a bit of gossip.) A -- ---- Andrew Sullivan 204-4141 Yonge Street Afilias Canada Toronto, Ontario Canada <andrew@libertyrms.info> M2P 2A8 +1 416 646 3304 x110 -
Re: Tuning for mid-size server
Bruce Momjian <pgman@candle.pha.pa.us> — 2003-12-14T05:42:21Z
Andrew Sullivan wrote: > On Tue, Oct 21, 2003 at 03:11:17PM -0600, scott.marlowe wrote: > > I think where it makes sense is when you have something like a report > > server where the result sets may be huge, but the parellel load is load, > > i.e. 5 or 10 users tossing around 100 Meg or more at time. > > In our case, we were noticing that truss showed an unbelievable > amount of time spent by the postmaster doing open() calls to the OS > (this was on Solaris 7). So we thought, "Let's try a 2G buffer > size." 2G was more than enough to hold the entire data set under > question. Once the buffer started to fill, even plain SELECTs > started taking a long time. The buffer algorithm is just not that > clever, was my conclusion. > > (Standard disclaimer: not a long, controlled test. It's just a bit > of gossip.) I know this is an old email, but have you tested larger shared buffers in CVS HEAD with Jan's new cache replacement policy? -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
-
Re: Tuning for mid-size server
Andrew Sullivan <andrew@libertyrms.info> — 2003-12-14T17:55:48Z
On Sun, Dec 14, 2003 at 12:42:21AM -0500, Bruce Momjian wrote: > > I know this is an old email, but have you tested larger shared buffers > in CVS HEAD with Jan's new cache replacement policy? Not yet. It's on our TODO list, for sure, because the consequences of relying too much on the filesystem buffers under certain perverse loads is lousy database performance _precisely_ when we need it. I expect some testing of this type some time in January. A -- ---- Andrew Sullivan 204-4141 Yonge Street Afilias Canada Toronto, Ontario Canada <andrew@libertyrms.info> M2P 2A8 +1 416 646 3304 x110