Thread: Streaming Replica Master-Salve Config.
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!!!
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>
On 8/5/16 12:35 AM, Periko Support 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? max_connections must be the same (or higher) on the standby as on the master: https://www.postgresql.org/docs/current/static/runtime-config-connection.html#GUC-MAX-CONNECTIONS but shared_buffers (which is what I guess you mean with "shared_memory") can be a different value appropriate to the standby's hardware resources. Regards Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, RemoteDBA, Training & Services
Il 04/08/2016 18:15, Eduardo Morras ha scritto: > [...] > a) As others said, max_connections = 200 is too high. Set it at your number of cores (I use number of cores -1) Excuse me for crossthreading, but I have to make things clearer to me. That's one of the things I feel hard to understand how to approach in my architecture. My server has 350 DB with 350 users, everyone with its DB. Every user has a "persistent" connection (used to replicate with rubyrep) and some "burst" connections to connect to colleagues DB when necessary. I'm going to split it across 2 servers, but it doesn't change things. Even with pgbouncer, how can I manage having all these replication connections (plus "burst" connections) with pgbouncer and a low (<100) max_connections? Is it even possible? Thanks Moreno. > --- --- > Eduardo Morras <emorrasg@yahoo.es> > >
On 8/4/2016 9:15 AM, Eduardo Morras wrote: > 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. idle connections only use a small amount of memory, a process, a socket, and some file handles. when you have multiple databases, its impossible to share a connection pool across them. the OP is talking about having 350 'tenants' each with their own database and user on a single server. your 1 connection per core suggestion is ludicrious for this scenario. in many database applications, most connections are idle most of the time. sure you don't want much over about 2-4X your cpu thread count actually active doing queries at the same time if you want the max transaction/second aggregate throughput, but you can still get acceptable performance several times higher than that, depending on the workload, in my benchmarks the aggregate TPS rolls off fairly slowly for quite a ways past the 2-4 connections per hardware thread or core level, at least doing simple OLTP stuff on a high concurrency storage system (lots of fast disks in raid10) -- john r pierce, recycling bits in santa cruz
On Fri, 5 Aug 2016 12:43:43 -0700 John R Pierce <pierce@hogranch.com> wrote: > On 8/4/2016 9:15 AM, Eduardo Morras wrote: > > 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. > > idle connections only use a small amount of memory, a process, a > socket, and some file handles. when you have multiple databases, > its impossible to share a connection pool across them. > > the OP is talking about having 350 'tenants' each with their own > database and user on a single server. No, I was answering to Periko, the mail you cite is from Moreno Andreo, which I c&p and indent here: > Il 04/08/2016 18:15, Eduardo Morras ha scritto: >> [...] >> a) As others said, max_connections = 200 is too high. Set it at your >> number of cores (I use number of cores -1) > Excuse me for crossthreading, but I have to make things clearer to me. > That's one of the things I feel hard to understand how to approach in > my architecture. > My server has 350 DB with 350 users, everyone with its DB. Every user > has a "persistent" connection (used to replicate with rubyrep) and > some "burst" connections to connect to colleagues DB when necessary. > I'm going to split it across 2 servers, but it doesn't change things. > Even with pgbouncer, how can I manage having all these replication > connections (plus "burst" connections) with pgbouncer and a low > (<100) max_connections? Is it even possible? > Thanks > Moreno. He asks for a different scenario, with multiuser & multidatabase. > your 1 connection per core suggestion is ludicrious for this > scenario. in many database applications, most connections are > idle most of the time. sure you don't want much over about 2-4X > your cpu thread count actually active doing queries at the same time > if you want the max transaction/second aggregate throughput, but you > can still get acceptable performance several times higher than that, > depending on the workload, in my benchmarks the aggregate TPS rolls > off fairly slowly for quite a ways past the 2-4 connections per > hardware thread or core level, at least doing simple OLTP stuff on a > high concurrency storage system (lots of fast disks in raid10) Yes, for this scenario, where multiple users/apps has its own database (A*(1:1)) or various (A*(1:D)) you are right. The OP, Periko, asked for a pentaho setup of type (A*(C:1)) where pg_bouncer could help and OLAP queries, which have cpu/io bottleneck. A*(C:D) A = number of users/clients/Apps C = number of Connections per A D = number of Databases in server a = small A value (lower than a "normal" threshold/value) c = small C value (lower than a "normal" threshold/value) d = small D value (lower than a "normal" threshold/value) > -- > john r pierce, recycling bits in santa cruz --- --- Eduardo Morras <emorrasg@yahoo.es>