On Fri, Mar 18, 2011 at 2:44 PM, Ben Chobot <bench@xxxxxxxxxxxxxxx> 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 will reduce your memory footprint. > > Er, right, for some reason I was thinking I could use connection pooling against multiple clusters, but now that I think about 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 to a shared buffers on a server, and two databases (DB A and DB B) to run. You either set up a single instance with a 4GB pool, or two instances with 2GB pools each. Let's say that DB A gets really busy, and DB B is not. In the shared instance approach, the instance can evict buffers cached for DB B in order to load buffers needed for DB A. In the split instance, 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
