From: | Eduardo Morras <emorrasg(at)yahoo(dot)es> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Streaming Replica Master-Salve Config. |
Date: | 2016-08-07 08:06:12 |
Message-ID: | 20160807100612.53672f44d2a250b34d18c5a4@yahoo.es |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Fri, 5 Aug 2016 12:43:43 -0700
John R Pierce <pierce(at)hogranch(dot)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(at)yahoo(dot)es>
From | Date | Subject | |
---|---|---|---|
Next Message | Михаил | 2016-08-07 08:39:46 | ФС против Постгреса для файлов |
Previous Message | Andreas Joseph Krogh | 2016-08-07 08:05:52 | Re: Updated RUM-index and support for bigint as part of index |