Subject: Re: Built-in connection pooler
To: Bruce Momjian <bruce@momjian.us>
Cc: PostgreSQL Hackers <pgsql-hackers@postgresql.org>,
 Dimitri Fontaine <dim@tapoueh.org>
References: <ac873432-31cf-d5e4-0b80-b5ac95cfe385@postgrespro.ru>
 <20190128211040.GF26761@momjian.us>
From: Konstantin Knizhnik <k.knizhnik@postgrespro.ru>
Message-ID: <ca90d2cc-6fbf-e9fa-19b9-b4456bbabdff@postgrespro.ru>
Date: Tue, 29 Jan 2019 12:09:50 +0300
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101
 Thunderbird/60.2.1
MIME-Version: 1.0
In-Reply-To: <20190128211040.GF26761@momjian.us>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Content-Language: en-US
Precedence: bulk


On 29.01.2019 0:10, Bruce Momjian wrote:
> On Thu, Jan 24, 2019 at 08:14:41PM +0300, Konstantin Knizhnik wrote:
>> The main differences with pgbouncer&K are that:
>>
>> 1. It is embedded and requires no extra steps for installation and
>> configurations.
>> 2. It is not single threaded (no bottleneck)
>> 3. It supports all clients (if client needs session semantic, then it will be
>> implicitly given dedicated backend)
>>
>>
>> Some performance results (pgbench -S -n):
>>
>> ┌────────────────┬────────┬─────────────┬─────────┬─────────────────────────┐
>> │ #Connections   │ Proxy  │ Proxy/SSL   │ Direct  │ Direct/SSL   │
>> ├────────────────┼────────┼─────────────┼─────────┼──────────────┤
>> │ 1              │  13752 │       12396 │   17443 │        15762 │
>> ├────────────────┼────────┼─────────────┼─────────┼──────────────┤
>> │ 10             │  53415 │       59615 │   68334 │        85885 │
>> ├────────────────┼────────┼─────────────┼─────────┼──────────────┤
>> │ 1000           │  60152 │       20445 │   60003 │        24047 │
>> └────────────────┴────────┴─────────────┴─────────┴──────────────┘
> It is nice it is a smaller patch.  Please remind me of the performance
> advantages of this patch.
>
The primary purpose of pooler is efficient support of large number of 
connections and minimizing system resource usage.
But as far as Postgres is not scaling well at SMP system with larger 
number of CPU cores (due to many reasons discussed in hackers)
reducing number of concurrently working backends can also significantly 
increase performance.

I have not done such testing yet but I am planing to do it as well as 
comparison with pgbouncer and Odyssey.
But please notice that this proxy approach is by design slower than my 
previous implementation used in PgPRO-EE (based on socket redirection).
At some workloads connections throughout proxy cause up to two times 
decrease of performance comparing with dedicated backends.
There is no such problem with old connection pooler implementation which 
was always not worser than vanilla. But it doesn't support SSL connections
and requires much more changes in Postgres core.


-- 
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company