Re: 1 machine + master DB with postgres_fdw + multiple DB instances on different ports

On Tue, Jun 17, 2014 at 12:17 AM, Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at>
wrote:

> Gezeala M. Bacuño II wrote:
> > Does anybody have a similar setup:
> >
> > [a] 1 physical machine with half a terabyte of RAM, Xeon E7- 8837 @
> 2.67GHz, huge ZFS pools + ZIL +
> > L2ARC
> > [b] master DB pg9.3 postgres_fdw with read/write capabilities, with
> tablespaces and WAL on separate
> > zpools, archiving enabled (for zfs snapshots purposes), +17K tables,
> multi-TB in size and growing
> > [c] multiple DB instances listening on different ports or sockets on the
> same machine with [b]
> > (looking at 2 DB instances as of now which may increase later on)
> >
> >
> > On the master DB there are several schemas with foreign tables located
> on any of the [c] DB instance.
> > postgres_fdw foreign server definitions and all table sequence are on
> the master DB. Basically, I'm
> > looking at any benefits in terms of decreasing the master DB scaling,
> size, separate shared_buffers
> > and separate writer processes per instance (to utilize more CPU?). I'm
> also planning on relocating
> > seldom accessed tables on [c] DBs. Am I on the right path on utilizing
> foreign data wrappers this way?
>
>
correction: benefits in terms of *decreasing the master DB size*, scaling,
separate..

> You are very likely not going to gain anything that way.
>
> Access to foreign tables is slower than access to local tables, and
> (particularly when joins are
> involved) you will end up unnecessarily sending lots of data around
> between the databases.
> So I'd expect performance to suffer.
>

factoring in the fdw load during joins, I'm thinking there's probably not
gonna be that much performance hit since all data are in 1 machine (we have
tablespace set-up in place too)

>
> In addition, all the database clusters will have to share the memory, so I
> don't see an
> improvement over having everything in one database.
>

this machine does have half a terabyte of RAM, shared_buffers at 8GB per
cluster, work_mem at 512MB and ZFS arc, we will still have lots of RAM to
spare.

> Since the size will stay the same, you are not going to save anything on
> backups either.
>

not looking into decreasing the overall size of all db clusters but rather
decreasing the size and relation counts per cluster making each db cluster
manageable.

> Depending on the workload and how you distribute the tables, it might be a
> win to
> distribute a large database across several physical machines.
>

avoiding additional network load, only 2 machines available in the same
location and the other one is a failover server.

>
> I would test any such setup for performance.
>
> Yours,
> Laurenz Albe
>