pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
The never ending quest for clarity on shared_buffers
3+ messages / 3 participants
[nested] [flat]

* The never ending quest for clarity on shared_buffers
@ 2004-10-06 20:04 Doug Y <dylists@ptd.net>
  2004-10-06 22:26 ` Re: The never ending quest for clarity on shared_buffers Paul Ramsey <pramsey@refractions.net>
  0 siblings, 1 reply; 3+ messages in thread

From: Doug Y @ 2004-10-06 20:04 UTC (permalink / raw)
  To: pgsql-performance

Hello,
   We recently upgraded os from rh 7.2 (2.4 kernel) to Suse 9.1 (2.6 
kernel), and psql from 7.3.4 to 7.4.2

   One of the quirks I've noticed is how the queries don't always have the 
same explain plans on the new psql... but that's a different email I think.

   My main question is I'm trying to convince the powers that be to let me 
use persistent DB connections (from apache 2 / php), and my research has 
yielded conflicting documentation about the shared_buffers setting... real 
shocker there :)

   For idle persistent connections, do each of them allocate the memory 
specified by this setting (shared_buffers * 8k), or is it one pool used by 
all the connection (which seems the logical conclusion based on the name 
SHARED_buffers)? Personally I'm more inclined to think the latter choice, 
but I've seen references that alluded to both cases, but never a definitive 
answer.

   For what its worth, shared_buffers is currently set to 50000 (on a 4G 
system). Also, effective_cache_size is 125000. max_connections is 256, so I 
don't want to end up with a possible 100G (50k * 8k * 256) of memory tied 
up... not that it would be possible, but you never know.

   I typically never see more than a dozen or so concurrent connections to 
the db (serving 3 web servers), so I'm thinking of actually using something 
like pgpool to keep about 10 per web server, rather than use traditional 
persistent connections of 1 per Apache child, which would probably average 
about 50 per web server.

Thanks.




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: The never ending quest for clarity on shared_buffers
  2004-10-06 20:04 The never ending quest for clarity on shared_buffers Doug Y <dylists@ptd.net>
@ 2004-10-06 22:26 ` Paul Ramsey <pramsey@refractions.net>
  2004-10-07 01:29   ` Re: The never ending quest for clarity on shared_buffers Neil Conway <neilc@samurai.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Paul Ramsey @ 2004-10-06 22:26 UTC (permalink / raw)
  To: Doug Y <dylists@ptd.net>; +Cc: pgsql-performance

Doug Y wrote:

>   For idle persistent connections, do each of them allocate the memory 
> specified by this setting (shared_buffers * 8k), or is it one pool used 
> by all the connection (which seems the logical conclusion based on the 
> name SHARED_buffers)? Personally I'm more inclined to think the latter 
> choice, but I've seen references that alluded to both cases, but never a 
> definitive answer.

The shared_buffers are shared (go figure) :).  It is all one pool shared 
by all connections.  The sort_mem and vacuum_mem are *per*connection* 
however, so when allocating that size you have to take into account your 
expected number of concurrent connections.

Paul



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: The never ending quest for clarity on shared_buffers
  2004-10-06 20:04 The never ending quest for clarity on shared_buffers Doug Y <dylists@ptd.net>
  2004-10-06 22:26 ` Re: The never ending quest for clarity on shared_buffers Paul Ramsey <pramsey@refractions.net>
@ 2004-10-07 01:29   ` Neil Conway <neilc@samurai.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Neil Conway @ 2004-10-07 01:29 UTC (permalink / raw)
  To: Paul Ramsey <pramsey@refractions.net>; +Cc: Doug Y <dylists@ptd.net>; pgsql-performance

On Thu, 2004-10-07 at 08:26, Paul Ramsey wrote:
> The shared_buffers are shared (go figure) :).  It is all one pool shared 
> by all connections.

Yeah, I thought this was pretty clear. Doug, can you elaborate on where
you saw the misleading docs?

> The sort_mem and vacuum_mem are *per*connection* however, so when
> allocating that size you have to take into account your 
> expected number of concurrent connections.

Allocations of size `sort_mem' can actually can actually happen several
times within a *single* connection (if the query plan happens to involve
a number of sort steps or hash tables) -- the limit is on the amount of
memory that will be used for a single sort/hash table. So choosing the
right figure is actually a little more complex than that.

-Neil





^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2004-10-07 01:29 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-10-06 20:04 The never ending quest for clarity on shared_buffers Doug Y <dylists@ptd.net>
2004-10-06 22:26 ` Paul Ramsey <pramsey@refractions.net>
2004-10-07 01:29   ` Neil Conway <neilc@samurai.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox