Re: multi-tenant vs. multi-cluster - Mailing list pgsql-general

From Nicholson, Brad (Toronto, ON, CA)
Subject Re: multi-tenant vs. multi-cluster
Date
Msg-id 2626AEE4839D064CB0472A3814DC403F46D2081695@GVW1092EXB.americas.hpqcorp.net
Whole thread Raw
In response to Re: multi-tenant vs. multi-cluster  (Ben Chobot <bench@silentmedia.com>)
List pgsql-general
> -----Original Message-----
> From: Ben Chobot [mailto:bench@silentmedia.com]
> Sent: Friday, March 18, 2011 3:45 PM
> To: Nicholson, Brad (Toronto, ON, CA)
> Cc: pgsql-general General
> Subject: Re: [GENERAL] multi-tenant vs. multi-cluster
>
>
> 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?

Not for reducing overall numbers of connections on the server.

> >>
> >>> 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.

statement_timeout will only time out SQL queries, not DB transactions.  There is nothing internal for that.  It's a
fairlyeasy query to terminate all IDLE transactions, but you have to be careful that you aren't terminating active
sessions.

Brad.

pgsql-general by date:

Previous
From: Katherine Jeschke
Date:
Subject: Surge 2011 Conference CFP
Next
From: Merlin Moncure
Date:
Subject: Re: multi-tenant vs. multi-cluster