Re: [PERFORM] Tuning for mid-size server

2003-12-14 Thread Andrew Sullivan
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 CanadaToronto, Ontario Canada
[EMAIL PROTECTED]  M2P 2A8
 +1 416 646 3304 x110


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings


Re: [PERFORM] Tuning for mid-size server

2003-10-24 Thread William Yu
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.

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
 subscribe-nomail command to [EMAIL PROTECTED] so that your
 message can get through to the mailing list cleanly


Re: [PERFORM] Tuning for mid-size server

2003-10-22 Thread Ron Johnson
On Tue, 2003-10-21 at 14:27, Christopher Browne wrote:
 In the last exciting episode, [EMAIL PROTECTED] (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. [EMAIL PROTECTED]
Jefferson, LA USA

Fear the Penguin!! 


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings


Re: [PERFORM] Tuning for mid-size server

2003-10-21 Thread Richard Huxton
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

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster


Re: [PERFORM] Tuning for mid-size server

2003-10-21 Thread Josh Berkus
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

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster


Re: [PERFORM] Tuning for mid-size server

2003-10-21 Thread scott.marlowe
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.



---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match


Re: [PERFORM] Tuning for mid-size server

2003-10-21 Thread Anjan Dave
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:[EMAIL PROTECTED] 
Sent: Tuesday, October 21, 2003 11:57 AM
To: Anjan Dave; [EMAIL PROTECTED]
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

---(end of broadcast)---
TIP 8: explain analyze is your friend


Re: [PERFORM] Tuning for mid-size server

2003-10-21 Thread Josh Berkus
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

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])


Re: [PERFORM] Tuning for mid-size server

2003-10-21 Thread Josh Berkus
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

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster


Re: [PERFORM] Tuning for mid-size server

2003-10-21 Thread scott.marlowe
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.



---(end of broadcast)---
TIP 8: explain analyze is your friend


Re: [PERFORM] Tuning for mid-size server

2003-10-21 Thread scott.marlowe
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.


---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org


Re: [PERFORM] Tuning for mid-size server

2003-10-21 Thread Andrew Sullivan
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 CanadaToronto, Ontario Canada
[EMAIL PROTECTED]  M2P 2A8
 +1 416 646 3304 x110


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings


Re: [PERFORM] Tuning for mid-size server

2003-10-21 Thread Andrew Sullivan
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 CanadaToronto, Ontario Canada
[EMAIL PROTECTED]  M2P 2A8
 +1 416 646 3304 x110


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster


Re: [PERFORM] Tuning for mid-size server

2003-10-21 Thread Jeff
On Tue, 21 Oct 2003 10:12:15 -0700
Josh Berkus [EMAIL PROTECTED] 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 [EMAIL PROTECTED]
http://www.jefftrout.com/
http://www.stuarthamm.net/

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings


Re: [PERFORM] Tuning for mid-size server

2003-10-21 Thread Christopher Browne
In the last exciting episode, [EMAIL PROTECTED] (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)

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster


Re: [PERFORM] Tuning for mid-size server

2003-10-21 Thread Anjan Dave
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:[EMAIL PROTECTED] 
Sent: Tue 10/21/2003 1:22 PM 
To: Anjan Dave; Richard Huxton; [EMAIL PROTECTED] 
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



---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])


Re: [PERFORM] Tuning for mid-size server

2003-10-21 Thread Anjan Dave
Hopefully, i am not steering this into a different direction, but is there a way to 
find out how much sort memory each query is taking up, so that we can scale that up 
with increasing users?

THanks,
Anjan

-Original Message- 
From: scott.marlowe [mailto:[EMAIL PROTECTED] 
Sent: Tue 10/21/2003 1:33 PM 
To: Josh Berkus 
Cc: Anjan Dave; Richard Huxton; [EMAIL PROTECTED] 
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.




---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly


Re: [PERFORM] Tuning for mid-size server

2003-10-21 Thread Andrew Sullivan
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 CanadaToronto, Ontario Canada
[EMAIL PROTECTED]  M2P 2A8
 +1 416 646 3304 x110


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]


Re: [PERFORM] Tuning for mid-size server

2003-10-21 Thread scott.marlowe
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.


---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org


Re: [PERFORM] Tuning for mid-size server

2003-10-21 Thread Andrew Sullivan
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 CanadaToronto, Ontario Canada
[EMAIL PROTECTED]  M2P 2A8
 +1 416 646 3304 x110


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])


Re: [PERFORM] Tuning for mid-size server

2003-10-21 Thread Josh Berkus
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


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly