From: Andres Freund <andres@2ndquadrant.com>
To: michael@sqlexec.com <michael@sqlexec.com>
Cc: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: MusicBrainz postgres performance issues
Date: Mon, 16 Mar 2015 01:12:34 +0100
Message-ID: <20150316001234.GG29732@awork2.anarazel.de> (raw)
In-Reply-To: <55061BEB.5070209@sqlexec.com>
References: <20150315135022.GC19792@alap3.anarazel.de>
<CAOR=d=0YwcnVi3kGD21W7jYjE7DcWjp0v0GjncH-c2-oJEP5Rw@mail.gmail.com>
<CAOR=d=1Axcbj2VcUO=5tM1nv6RjuA0D+av5+Mt7RTztgTxaMnA@mail.gmail.com>
<20150315172048.GG9324@alap3.anarazel.de>
<CAG95seXv4EKGhDV04rkk_ARgvz8OQUJy3Xbx5Rw16Wxm3+1j5Q@mail.gmail.com>
<20150315174646.GH9324@alap3.anarazel.de>
<CAOR=d=2rC8dYr=tY7JQTNK+eszx2J1pRHuqYFHPbqxVDXiN1sQ@mail.gmail.com>
<20150315224756.GE29732@awork2.anarazel.de>
<550618C2.3030802@2ndquadrant.com>
<55061BEB.5070209@sqlexec.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
(please quote properly)
On 2015-03-15 19:55:23 -0400, michael@sqlexec.com wrote:
> Why is 500 connections "insane". We got 32 CPU with 96GB and 3000 max
> connections, and we are doing fine, even when hitting our max concurrent
> connection peaks around 4500. At a previous site, we were using 2000 max
> connections on 24 CPU and 64GB RAM, with about 1500 max concurrent
> connections. So I wouldn't be too hasty in saying more than 500 is asking
> for trouble. Just as long as you got your kernel resources set high enough
> to sustain it (SHMMAX, SHMALL, SEMMNI, and ulimits), and RAM for work_mem.
It may work acceptably in some scenarios, but it can lead to significant
problems. Several things in postgres things in postgres scale linearly
(from the algorithmic point of view, often CPU characteristics like
cache sizes make it wors) with max_connections, most notably acquiring a
snapshot. It usually works ok enough if you don't have a high number of
queries per second, but if you do, you can run into horrible contention
problems. Absurdly enough that matters *more* on bigger machines with
several sockets. It's especially bad on 4+ socket systems.
The other aspect is that such a high number of full connections usually
just isn't helpful for throughput. Not even the most massive NUMA
systems (~256 hardware threads is the realistic max atm IIRC) can
process 4.5k queries at the same time. It'll often be much more
efficient if all connections above a certain number aren't allocated a
full postgres backend, with all it's overhead, but use a much more
lightweight pooler connection.
Greetings,
Andres Freund
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: andres@2ndquadrant.com, michael@sqlexec.com, tomas.vondra@2ndquadrant.com
Subject: Re: MusicBrainz postgres performance issues
In-Reply-To: <20150316001234.GG29732@awork2.anarazel.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox