From: Jehan-Guillaume de Rorthais <ioguix@free.fr>
To: pgsql-admin@postgresql.org,Lazaro Garcia <lazaro3487@gmail.com>
To: pgpool-general@pgpool.net
Subject: Re: Why pgpool TPS is lowest versus postgresql direct connections?
Date: Sat, 11 Feb 2017 12:58:40 +0100
Message-ID: <2F10FF7B-7F4D-49DD-8BAC-DDF485F929B9@free.fr> (raw)
In-Reply-To: <001d01d282bb$43909610$cab1c230$@gmail.com>
References: <001d01d282bb$43909610$cab1c230$@gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
Hi, Le 9 février 2017 10:59:53 GMT+01:00, Lazaro Garcia <lazaro3487@gmail.com> a écrit :
[...]
>Is there any way to tuning this behavior?
The fact is that pgpool need to parse every single query going through it, guess if the query is a RO or a Rw, then decide what to do with it.
Which means:
- It adds a latency on every single query
- pgpool itself can become a bottleneck, especially during bench with lot of small and fast queries
- Pgpool handles N client connections and N*M backends connection
- It tracks xact, prepared queries, etc
In short, pgpool is not transparent and free of load...
>De: Lazaro Garcia [mailto:lazaro3487@gmail.com]
>Enviado el: miércoles, 8 de febrero de 2017 05:28 p. m.
>Para: 'pgpool-general@pgpool.net'; 'pgsql-admin@postgresql.org'
>Asunto: Why pgpool TPS is lowest versus postgresql direct connections?
>
>
>
>After installed Pgpool with 2 postgresql nodes with streaming
>replication, I
>have noticed that access directly to postgresql is more efficient than
>through pgpool.
>
>
>
>I supposed that load balance could increase the transactions per second
>executed because each node could receive more load, but the results
>shown
>below are not expected.
>
>
>
>This is the setup:
>
>
>
>Pgpool 3.6.1 whit connection pooling, streaming replication mode and
>load
>balancing mode.
>
>
>
>2 PostgreSQL server 9.6.1 whit streaming replication.
>
>
>
>For the tests I used sysbench and pgbench.
>
>
>
>The results of sysbench:
>
>
>
>
>Concurrent Users
>
>1
>
>20
>
>50
>
>100
>
>
>PostgreSQL (TPS) Direct
>
>1166
>
>20936
>
>25743
>
>27344
>
>
>Pgpool (TPS)
>
>2240
>
>7628
>
>7013
>
>6135
>
>
>
>
>
>The results of pgbench
>
>
>
>
>1
>
>20
>
>50
>
>100
>
>
>PostgreSQL (TPS) Direct
>
>1403
>
>6805
>
>6194
>
>5726
>
>
>Pgpool (TPS)
>
>511
>
>5430
>
>5528
>
>4705
>
>
>
>
>
>As you can see in both cases even with load balance, the total
>transactions
>per second are lower.
>
>
>
>Is this the expected behavior. Is there any way to allow more TPS when
>pgpool is used?
>
>
>
>There are other publications with similar results:
>
>
>
>https://www.os3.nl/_media/2011-2012/courses/lia/rory_breuk_gerrie_veerman_-_
>report.pdf (page 28)
>
>http://www.mail-archive.com/pgpool-general@pgfoundry.org/msg03326.html
>
>
>
>
>
>Regards
--
Envoyé de mon appareil Android avec K-9 Mail. Veuillez excuser ma brièveté.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
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-admin@postgresql.org
Cc: ioguix@free.fr, lazaro3487@gmail.com, pgpool-general@pgpool.net
Subject: Re: Why pgpool TPS is lowest versus postgresql direct connections?
In-Reply-To: <2F10FF7B-7F4D-49DD-8BAC-DDF485F929B9@free.fr>
* 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