Re: MusicBrainz postgres performance issues

(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