On Fri, Mar 18, 2011 at 2:44 PM, Ben Chobot <bench@silentmedia.com> wrote:
>
> On Mar 18, 2011, at 12:34 PM, Nicholson, Brad (Toronto, ON, CA) wrote:
>
>>>> b) its own postgresql processes (many of them) running in memory
>>>
>>> I believe this is entirely a function of client connections.
>>
>> With a single instance, you can use connection pooling to reduce the overall number of backend connections which
willreduce your memory footprint.
>
> Er, right, for some reason I was thinking I could use connection pooling against multiple clusters, but now that I
thinkabout it that doesn't make much sense, does it?
>
>>>
>>>> c) its own shared_buffers in memory.
>>>
>>> Given that each application will be independent, I don't see a
>>> different between clusters and schemas here either.
>>
>> The difference is that in a single cluster, a single instance is going to make decisions about what data to cache or
not. This is an overly simplified example - but illustrates the point. Say you have 4GB of RAM available to dedicate
toa shared buffers on a server, and two databases (DB A and DB B) to run. You either set up a single instance with a
4GBpool, or two instances with 2GB pools each. Let's say that DB A gets really busy, and DB B is not. In the shared
instanceapproach, the instance can evict buffers cached for DB B in order to load buffers needed for DB A. In the
splitinstance, you can't.
>
> Ah, that's an illustrative example. Thanks.
>
> OK, so are there any good ways to keep a bad/clueless user from gumming up a whole cluster? Something like
statement_timeout,but for transactions, seems like it would be idle.
single cluster, multiple database is likely the best way to go.
postgres is a little thin on resource provisioning features but at the
end of the day, restricting a single client app to N simultaneous
connections on a suitably powerful server is going to be your best way
to deal with this. I highly advise using pgbouncer in front of your
database to do this.
you can always generate scripts to watch for high load situations and
kill off offending backends in emergencies.
statement_timeout is ok, but don't be too aggressive with it or it
will become annoying.
merlin
