Re: Restore postgresql data directory to tablespace on new host? Or swap tablespaces?

Hi Jason:

On Sun, Feb 16, 2014 at 1:30 AM, Antman, Jason (CMG-Atlanta)
<Jason.Antman@coxinc.com> wrote:
> I think I jumped on this without really understanding what you were
> saying, or the implications of it. If I run N postgres server instances
> on the same physical host, I can do away with the overhead of running
> each of them in their own virtualized operating system, but they can't
> really share resources other than *OS* cache, right?

Physical or virtual host, is the same.

They are going to share much more. They will share schedulers,
filesystems and a lost of things. Also, I do not know your connection
patterns, but if your testters disconnect promptly you will share used
memory for work mem, among other things.

> My current postgres instances for testing have 16GB shared_buffers (and
> 5MB work_mem, 24GB effective_cache_size). So if, hypothetically (to give
> a mathematically simple example), I have a host machine with 100GB RAM,
> I can't run 10 postgres instances with those settings, right?

No. You cannot properly run  10 instances with 16Gb shared buffers
properly with less than 160 Gb of RAM ( leave aside cache ) unless you
count on lot of thems being passive and swapped out. What I would do
in your case is to really tune trying to trade shared buffers with
effective cache, but say this is correct. BTW, this settings will need
a 48G machine easily ( 16G+24 = 40G + work_mem*N + OS overhead, as
always your numbers continue to puzzle me ). The point is using a
netapp which hides the commonaitly in disks and going for full os
virtualization you would need 40G per copy, which may be slightly
reduced if your vitualization techniques are really good and do
ballooning or deduplicating, but deduplicating at this scale is going
to be hard, and balloning is difficult if you don't tune a lot of the
images ( to free ballon the disk cache ). OTOH if you put all this as
10 copies in a single machine you do not need balloning, as all
instances reuse the work mem, not only among instances on the same
image. Also, if the COW snapshots are done by the OS on this machine,
instead of using 240G for cache you may be able to use only, say, 120
but tell the instance the effective cache size is 24 if they share
blocks, as the OS knows which blocks are shared amount snapshots and
only keeps one copy of them. If you can tune the machine for 8G
shared, 32Gb effective you could probably squeeze more.

What I mean is, you will always be able to squeeze more databases
using these than with full virtualization solutions. On a normal
setup, with 50 developers, with a normal database, I'll normally say
you couls squeze 2-3 more instances, but your numbers are really
difficult to understand, you've given the more strange sets of
parameters I've ever seen. It seems your system has had an organic
growth for a really long time and has gone into a very problematic
corner.

I forgot before, but if your test instances are short lived, depending
on the access pattern you use you may want to turn off autovacuum in
all of them ( and just do not use it if they are short lived and not
too much written or fire manual ones periodically, this way you can do
only one at a time ).

> I'd still
> need to provide for the memory needs of each postgres server/instance
> separately?

This I do not understand. You'll need to provide memory, and just experiment.

> In which case, from an ease of management/sanity
> perspective, it would probably be best to try running each one in
> containers (LXC) or some sort of ultra-low-overhead virtualization,
> rather than ending up with 10 separate instances of postgres running
> directly in the same OS?

IMO it will be much more easier, from a m/s persp. , to run them
WITHOUT any kind of vitualization solution. Remember, you already have
a virtualizer, it is called OS and, although limited, is very good at
what it does. I wouldn't even bother with chroot unless neccessary, in
my experience it just complicates things. Postgres is very well
behaved, and does not need the isolations which virtualization
solutions provide. Remember an instance is just a directory plus a
port number, it does not need more.

Francisco Olarte.