Thread: 1 machine + master DB with postgres_fdw + multiple DB instances on different ports
1 machine + master DB with postgres_fdw + multiple DB instances on different ports
From
Gezeala M. Bacuño II
Date:
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)
[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?
--
regards
gezeala bacuño II
regards
gezeala bacuño II
Re: 1 machine + master DB with postgres_fdw + multiple DB instances on different ports
From
Albe Laurenz
Date:
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? 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. 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. Since the size will stay the same, you are not going to save anything on backups either. Depending on the workload and how you distribute the tables, it might be a win to distribute a large database across several physical machines. I would test any such setup for performance. Yours, Laurenz Albe
Re: 1 machine + master DB with postgres_fdw + multiple DB instances on different ports
From
Gezeala M. Bacuño II
Date:
On Tue, Jun 17, 2014 at 12:17 AM, Albe Laurenz <laurenz.albe@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