Re: Built-in connection pooling - Mailing list pgsql-hackers
From | Konstantin Knizhnik |
---|---|
Subject | Re: Built-in connection pooling |
Date | |
Msg-id | 8cd0ea7a-3d7e-be3f-9116-bfe0524772a4@postgrespro.ru Whole thread Raw |
In response to | Re: Built-in connection pooling (Claudio Freire <klaussfreire@gmail.com>) |
List | pgsql-hackers |
On 18.01.2018 18:00, Claudio Freire wrote:
On Thu, Jan 18, 2018 at 11:48 AM, Konstantin Knizhnik <k.knizhnik@postgrespro.ru> wrote:
Attached please find new version of the patch with few fixes.
And more results at NUMA system with 144 cores and 3Tb of RAM.
Read-only pgbench (-S):
#Connections\kTPS Vanilla Postgres Session pool size 256 1k 1300 1505 10k 633 1519 100k - 1425
Read-write contention test: access to small number of records with 1% of updates.
#Clients\TPS Vanilla Postgres Session pool size 256 100 557232 573319 200 520395 551670 300 511423 533773 400 468562 523091 500 442268 514056 600 401860 526704 700 363912 530317 800 325148 512238 900 301310 512844 1000 278829 554516 So, as you can see, there is no degrade of performance with increased number of connections in case of using session pooling.TBH, the tests you should be running are comparisons with a similar pool size managed by pgbouncer, not just vanilla unlimited postgres.Of course a limited pool size will beat thousands of concurrent queries by a large margin. The real question is whether a pthread-based approach beats the pgbouncer approach.
Below are are results with pgbouncer:
#Connections\kTPS | Vanilla Postgres | Builti-in session pool size 256 | Postgres + pgbouncer with transaction pooling mode and pool size 256 | Postgres + 10 pgbouncers with pool size 20 |
1k | 1300 | 1505 | 105 | 751 |
10k | 633 | 1519 | 94 | 664 |
100k | - | 1425 | - | - |
(-) here means that I failed to start such number of connections (because of "resource temporary unavailable" and similar errors).
So single pgbouncer is 10 times slower than direct connection to the postgres.
No surprise here: pgbouncer is snigle threaded and CPU usage for pgbouncer is almost 100%.
So we have to launch several instances of pgbouncer and somehow distribute load between them.
In Linux it is possible to use REUSEPORT(https://lwn.net/Articles/542629/) to perform load balancing between several pgbouncer instances.
But you have to edit pgbouncer code: it doesn't support such mode. So I have started several instances of pgbouncer at different ports and explicitly distribute several pgbench instances between them.
But even in this case performance is twice slower than direct connection and built-in session pooling.
It is because of lacked of prepared statements which I can not use with pgbouncer in statement/transaction pooling mode.
Also please notice that with session pooling performance is better than with vanilla Postgres.
It is because with session pooling we can open more connections with out launching more backends.
It is especially noticeable at my local desktop with 4 cores: for normal Postgres optimal number of connections is about 10. But with session pooling 100 connections shows about 30% better result.
So, summarizing all above:
1. pgbouncer doesn't allows to use prepared statements and it cause up to two times performance penalty.
2. pgbouncer is single threaded and can not efficiently handle more than 1k connections.
3. pgbouncer never can provide better performance than application connected directly to Postgres with optimal number of connections. In contrast session pooling can provide better performance than vanilla Postgres with optimal number of connections.
-- Konstantin Knizhnik Postgres Professional: http://www.postgrespro.com The Russian Postgres Company
pgsql-hackers by date: