On 02/15/2014 05:27 PM, Karsten Hilbert wrote:
> On Sat, Feb 15, 2014 at 10:17:05PM +0000, Antman, Jason (CMG-Atlanta) wrote:
>
>> [...] I see how my original "brilliant" idea
>> (multiple DBs per postgres instance on one host, [...]) is insane,
>> without some specific support for it in postgres.
> "multiple DBs per PostgreSQL instance on one host" is quite
> a usual setup unless I am misinterpreting what you say.
The context there is "multiple DBs" where each DB is so big that the
only way we can deal with it is filesystem snapshots (and using NetApp's
flexclone technology, which only stores *changed* blocks for clones, not
the whole thing), so we can't restore a pg_dump... we have to clone the
actual files on disk.
>
>> I'm not sure we could reasonably spin them up on demand. Moreover, if we
>> were spinning up a VM on demand, we'd end up with the same situation
>> we're in now - every dev comes in in the morning and spins up their VM.
> Would a cron-ed serialized-into-batches spinup
> help with that ?
Yeah, but then we're back to square one with the resource utilization.
The idea of using one postgres instance per physical server, and running
multiple independent clones of the same database on it, is that we could
allocate a large amount of memory to shared cache/buffers, and simply
allow the in-use-at-this-instant databases to utilize it, and the idle
ones to expire out of cache. I.e. we have hardware with 192G of RAM. If
each database is only queried, say, for 10 seconds out of each 5 minute
interval, how do we maximize resource utilization / squeeze as many DBs
onto as few pieces of hardware as possible. If we spin up VMs every
morning, we're back to where we are now, with one postgres sever per VM,
and a large amount of physical memory being used up by virtualization
overhead (guest OS) and idle postgres processes.
>
> Karsten
-Jason
--
Jason Antman | Systems Engineer | CMGdigital
jason.antman@coxinc.com | p: 678-645-4155