[PERFORM] The never ending quest for clarity on shared_buffers
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 5 (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. ---(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] The never ending quest for clarity on shared_buffers
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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] The never ending quest for clarity on shared_buffers
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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
