Thread: Tuning for mid-size server
Hi,
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.
I am planning for these values for the postgres configuration - to begin with:
Shared_buffers (25% of RAM / 8KB)) = 8589934592 * .25 / 8192 = 262144
Sort_mem (4% of RAM / 1KB) = 335544. We'll take about half of that - 167772
Effective_cache_size = 262144 (same as shared_buffers - 25%)
In the /etc/sysctl file:
=================
kernel.shmall = 536870912 (512MB) SHMALL Total amount of shared memory available (bytes or pages)
kernel.shmmax = 536870912 (512MB) SHMMAX Maximum size of shared memory segment (bytes)
In a generic sense, these are recommended values I found in some documents. The database will be small in size and will gradually grow over time from few thousands to a few million records, or more. The activity will be mostly of select statements from a few tables with joins, orderby, groupby clauses. The web application is based on Apache/Resin and hotspot JVM 1.4.0.
Are the above settings ok to begin with? Are there any other parameters that I should configure now, or monitor lateron?
In other words, am I missing anything here to take full advantage of 4 CPUs and 8Gigs of RAM?
Appreciate any help.
Thanks,
Anjan
**************************************************************************
This e-mail and any files transmitted with it are intended for the use of the addressee(s) only and may be confidential and covered by the attorney/client and other privileges. If you received this e-mail in error, please notify the sender; do not disclose, copy, distribute, or take any action in reliance on the contents of this information; and delete it from your system. Any other use of this e-mail is prohibited.
On Tuesday 21 October 2003 15:28, Anjan Dave wrote: > Hi, > > 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. You'll want to upgrade PG to v7.3.4 > I am planning for these values for the postgres configuration - to begin > with: > > Shared_buffers (25% of RAM / 8KB)) = 8589934592 * .25 / 8192 = 262144 > > Sort_mem (4% of RAM / 1KB) = 335544. We'll take about half of that - > 167772 > > Effective_cache_size = 262144 (same as shared_buffers - 25%) My instincts would be to lower the first two substantially, and increase the effective cache once you know load levels. I'd probably start with something like the values below and work up: shared_buffers = 8,000 - 10,000 (PG is happier letting the OS do the cacheing) sort_mem = 4,000 - 8,000 (don't forget this is for each sort) You'll find the annotated postgresql.conf and performance tuning articles useful: http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php > In a generic sense, these are recommended values I found in some > documents. The database will be small in size and will gradually grow > over time from few thousands to a few million records, or more. The > activity will be mostly of select statements from a few tables with > joins, orderby, groupby clauses. The web application is based on > Apache/Resin and hotspot JVM 1.4.0. You'll need to figure out how many concurrent users you'll have and how much memory will be required by apache/java. If your database grows radically, you'll probably want to re-tune as it grows. -- Richard Huxton Archonet Ltd
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
On Tue, 21 Oct 2003, Josh Berkus wrote: > 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. You don't need a large cache, so much as a cache. The size isn't usually an issue now that 64 to 256 megs caches are the nominal cache sizes. Back when it was a choice of 4 or 8 megs it made a much bigger difference than 64 versus 256 meg make today. Also, if it's a read only environment, RAID5 with n drives equals the performance of RAID0 with n-1 drives. > Also, last I checked, you can't address 8GB of RAM without a 64-bit processor. > Since when are the Xeons 64-bit? Josh, you gotta get out more. IA32 has supported >4 gig ram for a long time now, and so has the linux kernel. It uses a paging method to do it. Individual processes are still limited to ~3 gig on Linux on 32 bit hardware though, so the extra mem will almost certainly spend it's time as kernel cache.
From what I know, there is a cache-row-set functionality that doesn't exist with the newer postgres... Concurrent users will start from 1 to a high of 5000 or more, and could ramp up rapidly. So far, with increased users, we have gone up to starting the JVM (resin startup) with 1024megs min and max (recommended by Sun) - on the app side. Thanks, Anjan -----Original Message----- From: Richard Huxton [mailto:dev@archonet.com] Sent: Tuesday, October 21, 2003 11:57 AM To: Anjan Dave; pgsql-performance@postgresql.org Subject: Re: [PERFORM] Tuning for mid-size server On Tuesday 21 October 2003 15:28, Anjan Dave wrote: > Hi, > > 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. You'll want to upgrade PG to v7.3.4 > I am planning for these values for the postgres configuration - to > begin > with: > > Shared_buffers (25% of RAM / 8KB)) = 8589934592 * .25 / 8192 = 262144 > > Sort_mem (4% of RAM / 1KB) = 335544. We'll take about half of that - > 167772 > > Effective_cache_size = 262144 (same as shared_buffers - 25%) My instincts would be to lower the first two substantially, and increase the effective cache once you know load levels. I'd probably start with something like the values below and work up: shared_buffers = 8,000 - 10,000 (PG is happier letting the OS do the cacheing) sort_mem = 4,000 - 8,000 (don't forget this is for each sort) You'll find the annotated postgresql.conf and performance tuning articles useful: http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php > In a generic sense, these are recommended values I found in some > documents. The database will be small in size and will gradually grow > over time from few thousands to a few million records, or more. The > activity will be mostly of select statements from a few tables with > joins, orderby, groupby clauses. The web application is based on > Apache/Resin and hotspot JVM 1.4.0. You'll need to figure out how many concurrent users you'll have and how much memory will be required by apache/java. If your database grows radically, you'll probably want to re-tune as it grows. -- Richard Huxton Archonet Ltd
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
Scott, > Also, if it's a read only environment, RAID5 with n drives equals the > performance of RAID0 with n-1 drives. True. > Josh, you gotta get out more. IA32 has supported >4 gig ram for a long > time now, and so has the linux kernel. It uses a paging method to do it. > Individual processes are still limited to ~3 gig on Linux on 32 bit > hardware though, so the extra mem will almost certainly spend it's time as > kernel cache. Not that you'd want a sigle process to grow that large anyway. So what is the ceiling on 32-bit processors for RAM? Most of the 64-bit vendors are pushing Athalon64 and G5 as "breaking the 4GB barrier", and even I can do the math on 2^32. All these 64-bit vendors, then, are talking about the limit on ram *per application* and not per machine? This has all been academic to me to date, as the only very-high-ram systems I've worked with were Sparc or micros. -- Josh Berkus Aglio Database Solutions San Francisco
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
Anjan, > From what I know, there is a cache-row-set functionality that doesn't > exist with the newer postgres... What? PostgreSQL has always used the kernel cache for queries. > Concurrent users will start from 1 to a high of 5000 or more, and could > ramp up rapidly. So far, with increased users, we have gone up to > starting the JVM (resin startup) with 1024megs min and max (recommended > by Sun) - on the app side. Well, just keep in mind when tuning that your calculations should be based on *available* RAM, meaning RAM not used by Apache or the JVM. With that many concurrent requests, you'll want to be *very* conservative with sort_mem; I might stick to the default of 1024 if I were you, or even lower it to 512k. -- Josh Berkus Aglio Database Solutions San Francisco
On Tue, 21 Oct 2003, Josh Berkus wrote: > Scott, > > > Also, if it's a read only environment, RAID5 with n drives equals the > > performance of RAID0 with n-1 drives. > > True. > > > Josh, you gotta get out more. IA32 has supported >4 gig ram for a long > > time now, and so has the linux kernel. It uses a paging method to do it. > > Individual processes are still limited to ~3 gig on Linux on 32 bit > > hardware though, so the extra mem will almost certainly spend it's time as > > kernel cache. > > Not that you'd want a sigle process to grow that large anyway. True :-) Especially a pgsql backend. > So what is the ceiling on 32-bit processors for RAM? Most of the 64-bit > vendors are pushing Athalon64 and G5 as "breaking the 4GB barrier", and even > I can do the math on 2^32. All these 64-bit vendors, then, are talking > about the limit on ram *per application* and not per machine? I think it's 64 gigs in the current implementation, but that could just be a chip set thing, i.e. the theoretical limit is probably 2^63 or 2^64, but the realistic limitation is that the current mobo chipsets are gonna have a much lower limit, and I seem to recall that being 64 gig last I looked.
On Tue, 21 Oct 2003, Josh Berkus wrote: > Anjan, > > > From what I know, there is a cache-row-set functionality that doesn't > > exist with the newer postgres... > > What? PostgreSQL has always used the kernel cache for queries. > > > Concurrent users will start from 1 to a high of 5000 or more, and could > > ramp up rapidly. So far, with increased users, we have gone up to > > starting the JVM (resin startup) with 1024megs min and max (recommended > > by Sun) - on the app side. > > Well, just keep in mind when tuning that your calculations should be based on > *available* RAM, meaning RAM not used by Apache or the JVM. > > With that many concurrent requests, you'll want to be *very* conservative with > sort_mem; I might stick to the default of 1024 if I were you, or even lower > it to 512k. Exactly. Remember, Anjan, that that if you have a single sort that can't fit in RAM, it will use the hard drive for temp space, effectively "swapping" on its own. If the concurrent sorts run the server out of memory, the server will start swapping process, quite possibly the sorts, in a sort of hideous round robin death spiral that will bring your machine to its knees as the worst possible time, midday under load. sort_mem is one of the small "foot guns" in the postgresql.conf file that people tend to pick up and go "huh, what's this do?" right before cranking it up.
On Tue, Oct 21, 2003 at 10:12:15AM -0700, Josh Berkus wrote:
>
> So what is the ceiling on 32-bit processors for RAM? Most of the 64-bit
> vendors are pushing Athalon64 and G5 as "breaking the 4GB barrier", and even
> I can do the math on 2^32. All these 64-bit vendors, then, are talking
> about the limit on ram *per application* and not per machine?
Or per same-time access. Remember that, back in the old days on the
pre-386s, accessing the extended or expanded memory (anyone remember
which was which?) involved some fairly serious work, and not
everything was seamless. I expect something similar is at work here.
Not that I've had a reason to play with 4G ix86 machines, anyway.
A
--
----
Andrew Sullivan 204-4141 Yonge Street
Afilias Canada Toronto, Ontario Canada
<andrew@libertyrms.info> M2P 2A8
+1 416 646 3304 x110
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
On Tue, 21 Oct 2003 10:12:15 -0700 Josh Berkus <josh@agliodbs.com> wrote: > So what is the ceiling on 32-bit processors for RAM? Most of the > 64-bit vendors are pushing Athalon64 and G5 as "breaking the 4GB > barrier", and even I can do the math on 2^32. All these 64-bit > vendors, then, are talking about the limit on ram *per application* > and not per machine? You can have > 4GB per app, but also you get a big performance boost as you don't have to deal with all the silly paging - think of it from when we switched from real mode to protected mode. If you check out hte linux-kernel archives you'll see one of the things often recommended when things go odd is to turn off HIMEM support. -- Jeff Trout <jeff@jefftrout.com> http://www.jefftrout.com/ http://www.stuarthamm.net/
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
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
Andrew Sullivan <andrew@libertyrms.info> writes:
> On Tue, Oct 21, 2003 at 10:12:15AM -0700, Josh Berkus wrote:
>> So what is the ceiling on 32-bit processors for RAM?
> ... Remember that, back in the old days on the
> pre-386s, accessing the extended or expanded memory (anyone remember
> which was which?) involved some fairly serious work, and not
> everything was seamless. I expect something similar is at work here.
Right. A 32-bit processor can only (conveniently) allow any individual
process to access 4G worth of address space. However the total RAM in
the system can be more --- the kernel can set up the hardware address
mappings to let different user processes use different up-to-4G segments
of that RAM. And the kernel can also use excess RAM for disk buffer
cache. So there's plenty of value in more-than-4G RAM, as long as
you're not expecting any single user process to need more than 4G.
This is no problem at all for Postgres, in which individual backend
processes don't usually get very large, and we'd just as soon let most
of the RAM go to kernel disk buffers anyway.
I think that some hardware configurations have problems with using RAM
above the first 4G for disk buffers, because of disk controller hardware
that can't cope with physical DMA addresses wider than 32 bits. The
solution here is to buy a better disk controller. If you google for
"bounce buffers" you can learn more about this.
What goes around comes around I guess --- I remember playing these same
kinds of games to use more than 64K RAM in 16-bit machines, 25-odd years
ago...
regards, tom lane
In the last exciting episode, josh@agliodbs.com (Josh Berkus) wrote:
> So what is the ceiling on 32-bit processors for RAM? Most of the
> 64-bit vendors are pushing Athalon64 and G5 as "breaking the 4GB
> barrier", and even I can do the math on 2^32. All these 64-bit
> vendors, then, are talking about the limit on ram *per application*
> and not per machine?
I have been seeing ia-32 servers with 8GB of RAM; it looks as though
there are ways of having them support ("physically, in theory, if you
could get a suitable motherboard") as much as 64GB.
But that certainly doesn't get you past 2^32 bytes per process, and
possibly not past 2^31 bytes/process.
From Linux kernel help:
CONFIG_NOHIGHMEM:
Linux can use up to 64 Gigabytes of physical memory on x86
systems. However, the address space of 32-bit x86 processors is
only 4 Gigabytes large. That means that, if you have a large
amount of physical memory, not all of it can be "permanently
mapped" by the kernel. The physical memory that's not permanently
mapped is called "high memory".
And that leaves open the question of how much shared memory you can
address. That presumably has to fit into the 4GB, and if your
PostgreSQL processes had (by some fluke) 4GB of shared memory, there
wouldn't be any "local" memory for sort memory and the likes.
Add to that the consideration that there are reports of Linux "falling
over" when you get to right around 2GB/4GB. I ran a torture test a
while back that _looked_ like it was running into that; I can't verify
that, unfortunately.
I don't see there being a whole lot of use of having more than about
8GB on an ia-32 system; what with shared memory maxing out at
somewhere between 1 and 2GB, that suggests having ~8GB in total.
I'd add another PG cluster if I had 16GB...
--
let name="aa454" and tld="freenet.carleton.ca" in name ^ "@" ^ tld;;
http://www.ntlug.org/~cbbrowne/postgresql.html
"A statement is either correct or incorrect. To be *very* incorrect is
like being *very* dead ... "
-- Herbert F. Spirer
Professor of Information Management
University of Conn.
(DATAMATION Letters, Sept. 1, 1984)
Josh,
The app servers are seperate dual-cpu boxes with 2GB RAM on each.
Yes, from all the responses i have seen, i will be reducing the numbers to what has been suggested.
Thanks to all,
anjan
-----Original Message-----
From: Josh Berkus [mailto:josh@agliodbs.com]
Sent: Tue 10/21/2003 1:22 PM
To: Anjan Dave; Richard Huxton; pgsql-performance@postgresql.org
Cc:
Subject: Re: [PERFORM] Tuning for mid-size server
Anjan,
> From what I know, there is a cache-row-set functionality that doesn't
> exist with the newer postgres...
What? PostgreSQL has always used the kernel cache for queries.
> Concurrent users will start from 1 to a high of 5000 or more, and could
> ramp up rapidly. So far, with increased users, we have gone up to
> starting the JVM (resin startup) with 1024megs min and max (recommended
> by Sun) - on the app side.
Well, just keep in mind when tuning that your calculations should be based on
*available* RAM, meaning RAM not used by Apache or the JVM.
With that many concurrent requests, you'll want to be *very* conservative with
sort_mem; I might stick to the default of 1024 if I were you, or even lower
it to 512k.
--
Josh Berkus
Aglio Database Solutions
San Francisco
Hopefully, i am not steering this into a different direction, but is there a way to find out how much sort memory each
queryis taking up, so that we can scale that up with increasing users?
THanks,
Anjan
-----Original Message-----
From: scott.marlowe [mailto:scott.marlowe@ihs.com]
Sent: Tue 10/21/2003 1:33 PM
To: Josh Berkus
Cc: Anjan Dave; Richard Huxton; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Tuning for mid-size server
On Tue, 21 Oct 2003, Josh Berkus wrote:
> Anjan,
>
> > From what I know, there is a cache-row-set functionality that doesn't
> > exist with the newer postgres...
>
> What? PostgreSQL has always used the kernel cache for queries.
>
> > Concurrent users will start from 1 to a high of 5000 or more, and could
> > ramp up rapidly. So far, with increased users, we have gone up to
> > starting the JVM (resin startup) with 1024megs min and max (recommended
> > by Sun) - on the app side.
>
> Well, just keep in mind when tuning that your calculations should be based on
> *available* RAM, meaning RAM not used by Apache or the JVM.
>
> With that many concurrent requests, you'll want to be *very* conservative with
> sort_mem; I might stick to the default of 1024 if I were you, or even lower
> it to 512k.
Exactly. Remember, Anjan, that that if you have a single sort that can't
fit in RAM, it will use the hard drive for temp space, effectively
"swapping" on its own. If the concurrent sorts run the server out of
memory, the server will start swapping process, quite possibly the sorts,
in a sort of hideous round robin death spiral that will bring your machine
to its knees as the worst possible time, midday under load. sort_mem is
one of the small "foot guns" in the postgresql.conf file that people tend
to pick up and go "huh, what's this do?" right before cranking it up.
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
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.
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
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
On Tue, 2003-10-21 at 14:27, Christopher Browne wrote:
> In the last exciting episode, josh@agliodbs.com (Josh Berkus) wrote:
> > So what is the ceiling on 32-bit processors for RAM? Most of the
> > 64-bit vendors are pushing Athalon64 and G5 as "breaking the 4GB
> > barrier", and even I can do the math on 2^32. All these 64-bit
> > vendors, then, are talking about the limit on ram *per application*
> > and not per machine?
>
> I have been seeing ia-32 servers with 8GB of RAM; it looks as though
> there are ways of having them support ("physically, in theory, if you
> could get a suitable motherboard") as much as 64GB.
>
> But that certainly doesn't get you past 2^32 bytes per process, and
> possibly not past 2^31 bytes/process.
>
> >From Linux kernel help:
>
> CONFIG_NOHIGHMEM:
>
> Linux can use up to 64 Gigabytes of physical memory on x86
> systems. However, the address space of 32-bit x86 processors is
> only 4 Gigabytes large. That means that, if you have a large
> amount of physical memory, not all of it can be "permanently
> mapped" by the kernel. The physical memory that's not permanently
> mapped is called "high memory".
>
> And that leaves open the question of how much shared memory you can
> address. That presumably has to fit into the 4GB, and if your
> PostgreSQL processes had (by some fluke) 4GB of shared memory, there
> wouldn't be any "local" memory for sort memory and the likes.
>
> Add to that the consideration that there are reports of Linux "falling
> over" when you get to right around 2GB/4GB. I ran a torture test a
> while back that _looked_ like it was running into that; I can't verify
> that, unfortunately.
Well thank goodness that Linux & Postgres work so well on Alpha
and long-mode AMD64.
--
-----------------------------------------------------------------
Ron Johnson, Jr. ron.l.johnson@cox.net
Jefferson, LA USA
"Fear the Penguin!!"
> So what is the ceiling on 32-bit processors for RAM? Most of the 64-bit > vendors are pushing Athalon64 and G5 as "breaking the 4GB barrier", and even > I can do the math on 2^32. All these 64-bit vendors, then, are talking > about the limit on ram *per application* and not per machine? 64-bit CPU on 64-bit OS. Up to physical address limit for anything and everything. 64-bit CPU on 32-bit OS. Up to 4GB minus the kernel allocation -- which is usually 2GB on Windows and Linux. On Windows, you can up this to 3GB by using the /3GB switch. Linux requires a kernel recompile. PAE is then used to "move" the memory window to point to different areas of the physical memory.
Anjan Dave wrote: > Shared_buffers (25% of RAM / 8KB)) = 8589934592 * .25 / 8192 = 262144 250,000 is probably the max you can use due to the 2GB process limit unless you recompile the Linux Kernel to use 3GB process/1GB kernel. Yes, I've got 8GB also and I started at 262144 and kept working my way down until Linux would allocate the memory. > > Sort_mem (4% of RAM / 1KB) = 335544. We'll take about half of that - 167772 > > Effective_cache_size = 262144 (same as shared_buffers - 25%) This should reflect the amount of memory available for caching. And unless you plan on running a ton of memory hogging software on the same machine, you probably will have 6GB available as cache. Top on my system confirms the 6GB number so I've got my setting at 750,000. (Left a little space for OS/programs/etc.) > In the /etc/sysctl file: > ================= > kernel.shmall = 536870912 (512MB) SHMALL Total amount of shared memory > available (bytes or pages) > kernel.shmmax = 536870912 (512MB) SHMMAX Maximum size of shared memory > segment (bytes) Ain't gonna happen unless you recompile the linux kernel to do 3/1. Through trial-and-error, I've found the largest number is: 2,147,483,648 > Are the above settings ok to begin with? Are there any other parameters > that I should configure now, or monitor lateron? Above is pretty good. I'd also bump up the free space map settings and maybe try to symlink the pg_xlog directory (log files) to a seperate drive.
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
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