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@xxxxxxxxxx | p: 678-645-4155 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
