Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1an4fj-0007hg-P7 for pgsql-performance@arkaria.postgresql.org; Mon, 04 Apr 2016 13:36:03 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1an4fi-0007ML-7b for pgsql-performance@arkaria.postgresql.org; Mon, 04 Apr 2016 13:36:02 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1an4e2-0005Pw-Aw for pgsql-performance@postgresql.org; Mon, 04 Apr 2016 13:34:18 +0000 Received: from mail-yw0-x234.google.com ([2607:f8b0:4002:c05::234]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1an4dx-0006HD-TJ for pgsql-performance@postgresql.org; Mon, 04 Apr 2016 13:34:17 +0000 Received: by mail-yw0-x234.google.com with SMTP id d68so114059486ywe.1 for ; Mon, 04 Apr 2016 06:34:13 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc; bh=EboeR+lsKd/PdJb+Ot0qxYtrIaKJjjkllSubpROmsvY=; b=gH5MiAiLaoTly022A1D7hY9D5dbOUJ5FAt6C0KxN6sAL3Rkqk/xgBlQGpb6Lsd6lRE qLo2elyV2ueWfKhtQ6qUxfstkfxH4TuX5GoWmfI/qVaDtqw2gLtUDD50YzTHmvKuI6TU yaA6Olitj8p1IEwpu/pHUUAjWNV28HEQscmwIEMqU45cOQaYQhL0SKl692UxEnncFpWb 5JGWPm3H6m7bMUI7oTCiiC3Ms7+l7cfZRfhgMl80PyQZGvvfFzbJQsqw2Q/gikIbrcFE CdW9qOe4+QQHjO9l7PGXh7fI8zHZu8JrkbLk7C9BBVsptfEVrV6LD4Qhw+isNKN9pvZT Xp1g== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:mime-version:in-reply-to:references:from:date :message-id:subject:to:cc; bh=EboeR+lsKd/PdJb+Ot0qxYtrIaKJjjkllSubpROmsvY=; b=bsxWVH3/pkMzMbYGvvZiL46xMzFWJ0gNgD08PfZ45pgIuyDy693ZnJQdUgX0Ehhum4 Le/Oa3LXBkLEI7xLwRy1fOfylIDFMU6NOiw3HzQ/AGWjvMh9LNMI5xWyM+Oq7BpfhLjw nEN4i8AYITqR3gBa3FFDRa62fZJZ/Pr3LTISUUQYDIo/wxVqupOenT9rZ8XD+1QUzHLa i62+iAmMRHIPavQ7FW0shKiFxWWH7UwaW2H4ZXzdv8yfwMI0mc5wubXtF43QEnbkMJmn uqFvxYs0sW1jHjduM5v6mZa0klPnHXOJpjODmDhD3FVurigx6JcypT5tKPjfCWiwTfxe B5yQ== X-Gm-Message-State: AD7BkJIjciCvZdFIfsqxBhSZdNmmv+9eGHbib+IfYJQEbHZl+qjSwapvwNAxH4O/PDrF62w6VRACLOF3HNXKQQ== X-Received: by 10.37.12.195 with SMTP id 186mr19679784ybm.154.1459776851928; Mon, 04 Apr 2016 06:34:11 -0700 (PDT) MIME-Version: 1.0 Received: by 10.129.119.9 with HTTP; Mon, 4 Apr 2016 06:33:32 -0700 (PDT) In-Reply-To: <008b01d18e73$f1468950$d3d39bf0$@runbox.com> References: <1459451286.26406.16.camel@sem-jarek-01.prv> <1459497243.11987.7.camel@jlap3.macro.local> <57015083.50707@BlueTreble.com> <008b01d18e73$f1468950$d3d39bf0$@runbox.com> From: Pavel Stehule Date: Mon, 4 Apr 2016 15:33:32 +0200 Message-ID: Subject: Re: Big number of connections To: Mike Sofen Cc: Jim Nasby , jarek , "pgsql-performance@postgresql.org" Content-Type: multipart/alternative; boundary=001a113e9e0ab5eb32052fa8c8bd X-Pg-Spam-Score: -2.7 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --001a113e9e0ab5eb32052fa8c8bd Content-Type: text/plain; charset=UTF-8 Hi 2016-04-04 15:14 GMT+02:00 Mike Sofen : > From: Jim Nasby Sent: Sunday, April 03, 2016 10:19 AM > > >>On 4/1/16 2:54 AM, jarek wrote: > >> I'll be happy to hear form users of big PostgreSQL installations, how > >> many users do you have and what kind of problems we may expect. > >> Is there any risk, that huge number of roles will slowdown overall > >> performance ? > > >Assuming you're on decent sized hardware though, 3000-4000 open > connections shouldn't be much of an >issue *as long as very few are active > at once*. If you get into a situation where there's a surge of activity > >and you suddenly have 2x more active connections than cores, you won't be > happy. I've seen that push >servers into a state where the only way to > recover was to disconnect everyone. > >-- > >Jim Nasby > > Jim - I don't quite understand the math here: on a server with 20 cores, > it can only support 40 active users? > > I come from the SQL Server world where a single 20 core server could > support hundreds/thousands of active users and/or many dozens of > background/foreground data processes. Is there something fundamentally > different between the two platforms relative to active user loads? How > would we be able to use Postgres for larger web apps? > PostgreSQL doesn't contain integrated pooler - so any connection to Postgres enforces one PostgreSQL proces. A performance benchmarks is showing maximum performance about 10x cores. With high number of connections you have to use low size of work_mem, what enforces can have negative impact on performance too. Too high number of active PostgreSQL processes increase a risk of performance problems with spin locks, etc. Usually Web frameworks has own pooling solution - so just use it. If you need more logical connection than is optimum against number of cores, then you should to use external pooler like pgpool II or pgbouncer. http://www.pgpool.net/mediawiki/index.php/Main_Page http://pgbouncer.github.io/ Pgbouncer is light with only necessary functions, pgpool is little bit heavy with lot of functions. Regards Pavel > > Mike Sofen > > > > > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance > --001a113e9e0ab5eb32052fa8c8bd Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Hi

2016-04-04 15:14 GMT+02:00 Mike Sofen <msofen@runbox.com>= ;:
From: Jim Na= sby Sent: Sunday, April 03, 2016 10:19 AM

>>On 4/1/16 2:54 AM, jarek wrote:
>> I'll be happy to hear form users of big PostgreSQL installatio= ns, how
>> many users do you have and what kind of problems we may expect. >> Is there any risk, that huge number of roles will slowdown overall=
>> performance ?

>Assuming you're on decent sized hardware though, 3000-4000 open con= nections shouldn't be much of an >issue *as long as very few are act= ive at once*. If you get into a situation where there's a surge of acti= vity >and you suddenly have 2x more active connections than cores, you w= on't be happy. I've seen that push >servers into a state where t= he only way to recover was to disconnect everyone.
>--
>Jim Nasby

Jim - I don't quite understand the math here: on a server with 20 cores= , it can only support 40 active users?

I come from the SQL Server world where a single 20 core server could suppor= t hundreds/thousands of active users and/or many dozens of background/foreg= round data processes.=C2=A0 Is there something fundamentally different betw= een the two platforms relative to active user loads?=C2=A0 How would we be = able to use Postgres for larger web apps?

PostgreSQL doesn't contain integrated pooler - so any connection to = Postgres enforces one PostgreSQL proces. A performance benchmarks is showin= g maximum performance about 10x cores.=C2=A0 With high number of connection= s you have to use low size of work_mem, what enforces can have negative imp= act on performance too. Too high number of active PostgreSQL processes incr= ease a risk of performance problems with spin locks, etc.

Usually Web frameworks has own pooling solution - so just use it. If you = need more logical connection than is optimum against number of cores, then = you should to use external pooler like pgpool II or pgbouncer.

http://www.pgpoo= l.net/mediawiki/index.php/Main_Page
http://pgbouncer.github.io/

Pgbouncer is ligh= t with only necessary functions, pgpool is little bit heavy with lot of fun= ctions.

Regards

Pavel
=C2= =A0

Mike Sofen





--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-perform= ance

--001a113e9e0ab5eb32052fa8c8bd--