[prev in list] [next in list] [prev in thread] [next in thread]
List: pgsql-performance
Subject: Re: [PERFORM] 1 machine + master DB with postgres_fdw + multiple DB instances on different ports
From: Gezeala_M._Bacuño_II <gezeala () gmail ! com>
Date: 2014-06-17 16:41:37
Message-ID: CAJKO3mVGr+9mBWiAAuW1qUfQSypCi=nHMaxiBzGcnr0keFfgEQ () mail ! gmail ! com
[Download RAW message or body]
On Tue, Jun 17, 2014 at 12:17 AM, Albe Laurenz <laurenz.albe@wien.gv.at>
wrote:
> Gezeala M. Bacu=C3=B1o 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 th=
e
> 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
>
[Attachment #3 (text/html)]
<div dir="ltr"><div class="gmail_extra"><br><div class="gmail_quote">On Tue, Jun 17, \
2014 at 12:17 AM, Albe Laurenz <span dir="ltr"><<a \
href="mailto:laurenz.albe@wien.gv.at" \
target="_blank">laurenz.albe@wien.gv.at</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid \
rgb(204,204,204);padding-left:1ex"><div class=""><div class="h5">Gezeala M. Bacuño \
II wrote:<br> > Does anybody have a similar setup:<br>
><br>
> [a] 1 physical machine with half a terabyte of RAM, Xeon E7- 8837 @ 2.67GHz, \
huge ZFS pools + ZIL +<br> > L2ARC<br>
> [b] master DB pg9.3 postgres_fdw with read/write capabilities, with tablespaces \
and WAL on separate<br> > zpools, archiving enabled (for zfs snapshots purposes), \
+17K tables, multi-TB in size and growing<br> > [c] multiple DB instances \
listening on different ports or sockets on the same machine with [b]<br> > \
(looking at 2 DB instances as of now which may increase later on)<br> ><br>
><br>
> On the master DB there are several schemas with foreign tables located on any of \
the [c] DB instance.<br> > postgres_fdw foreign server definitions and all table \
sequence are on the master DB. Basically, I'm<br> > looking at any benefits in \
terms of decreasing the master DB scaling, size, separate shared_buffers<br> > and \
separate writer processes per instance (to utilize more CPU?). I'm also planning \
on relocating<br> > seldom accessed tables on [c] DBs. Am I on the right path on \
utilizing foreign data wrappers this way?<br> \
<br></div></div></blockquote><div><br></div><div>correction: benefits in terms of \
*decreasing the master DB size*, scaling, separate..</div><div> </div><blockquote \
class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid \
rgb(204,204,204);padding-left:1ex">
<div class=""><div class="h5">
</div></div>You are very likely not going to gain anything that way.<br>
<br>
Access to foreign tables is slower than access to local tables, and (particularly \
when joins are<br> involved) you will end up unnecessarily sending lots of data \
around between the databases.<br> So I'd expect performance to \
suffer.<br></blockquote><div><br></div><div>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)<br>
<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px \
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"> <br>
In addition, all the database clusters will have to share the memory, so I don't \
see an<br> improvement over having everything in one \
database.<br></blockquote><div><br></div><div>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.<br>
<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px \
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"> Since the size will \
stay the same, you are not going to save anything on backups \
either.<br></blockquote><div><br></div><div>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.<br>
<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px \
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"> <br>
Depending on the workload and how you distribute the tables, it might be a win to<br>
distribute a large database across several physical \
machines.<br></blockquote><div><br></div><div>avoiding additional network load, only \
2 machines available in the same location and the other one is a failover server.<br>
<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px \
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"> <br>
I would test any such setup for performance.<br>
<br>
Yours,<br>
Laurenz Albe<br>
</blockquote></div><br></div></div>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic