On Thu, 4 Aug 2016 08:35:37 -0700
Periko Support <pheriko.support@gmail.com> wrote:
> Hi.
>
> I'm testing streaming replica with ubuntu 14 and psql9.3.
>
> I'm trying to understand how this works, my plan is to setup the
> slave server for pentaho reports for queries only.
>
> Now, my master server it has 128GB max_connections = 200 maybe I
> will add more. shared_memory=18GB.
>
> My slave server doesn't have that resources, is a Xeon with 32GB of
> RAM with psql same version
> running on ubuntu14.
>
> My questions, does the slave server need to have the same resources?
> 128GB and the same settings in specific max_connections and
> shared_memory?
>
> Any comment appreciated, thanks!!!
a) As others said, max_connections = 200 is too high. Set it at your number of cores (I use number of cores -1)
b) Use pgbouncer as middleware between your apps and the master.
c) Upgrade postgres to current 9.5.
d) Are you sure pentaho reports queries aren't time and recurse hungry? They are (afaik) OLAP queries not OLTP. Perhaps
youshould switch the master with the slave.
e) To answer your question, no, slave don't need to have the same resources or settings.
f) For slave, if pentaho queries are OLAP, increase the work_mem setting.
g) Do some metrics, check, more metrics, recheck, (by metrics I want to say to measure performance, calculate
statisticsand compare results).
h) Read the documentation and wiki chapters on these topics.
i) Ask.
If you set max_connections too high, those connections will compete/figth for same resources, CPU processing, I/O to
disks,Memory and caches, Locks, and postgres will spend more time managing the resources than doing real work. Believe
me(or us) set it as we say and use a bouncer like pgbouncer. It can run on the same server.
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
--- ---
Eduardo Morras <emorrasg@yahoo.es>