On Tue, Jun 17, 2014 at 12:17 AM, Albe Laurenz <laurenz.a...@wien.gv.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 >
