Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1ccc1F-0000DU-4i for pgsql-admin@arkaria.postgresql.org; Sat, 11 Feb 2017 18:03:33 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1ccc1E-0001v2-BO for pgsql-admin@arkaria.postgresql.org; Sat, 11 Feb 2017 18:03:32 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1ccWKO-0006al-Qc for pgsql-admin@postgresql.org; Sat, 11 Feb 2017 11:58:56 +0000 Received: from smtp1-g21.free.fr ([212.27.42.1]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1ccWKK-00014O-No for pgsql-admin@postgresql.org; Sat, 11 Feb 2017 11:58:55 +0000 Received: from [10.136.114.168] (unknown [37.169.77.56]) (Authenticated sender: ioguix) by smtp1-g21.free.fr (Postfix) with ESMTPSA id B5D34B004FE; Sat, 11 Feb 2017 12:58:44 +0100 (CET) Date: Sat, 11 Feb 2017 12:58:40 +0100 User-Agent: K-9 Mail for Android In-Reply-To: <001d01d282bb$43909610$cab1c230$@gmail.com> References: <001d01d282bb$43909610$cab1c230$@gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable Subject: Re: Why pgpool TPS is lowest versus postgresql direct connections? To: pgsql-admin@postgresql.org,Lazaro Garcia ,pgpool-general@pgpool.net From: Jehan-Guillaume de Rorthais Message-ID: <2F10FF7B-7F4D-49DD-8BAC-DDF485F929B9@free.fr> X-Pg-Spam-Score: -1.6 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org Hi, Le 9 f=C3=A9vrier 2017 10:59:53 GMT+01:00, Lazaro Garcia a =C3=A9crit : [...] >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=20 - pgpool itself can become a bottleneck, especially during bench with lot o= f small and fast queries=20 - 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]=20 >Enviado el: mi=C3=A9rcoles, 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? > >=20 > >After installed Pgpool with 2 postgresql nodes with streaming >replication, I >have noticed that access directly to postgresql is more efficient than >through pgpool. > >=20 > >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. > >=20 > >This is the setup: > >=20 > >Pgpool 3.6.1 whit connection pooling, streaming replication mode and >load >balancing mode. > >=20 > >2 PostgreSQL server 9.6.1 whit streaming replication. > >=20 > >For the tests I used sysbench and pgbench. > >=20 > >The results of sysbench: > >=20 > > >Concurrent Users > >1 > >20 > >50 > >100 > > >PostgreSQL (TPS) Direct > >1166 > >20936 > >25743 > >27344 > > >Pgpool (TPS) > >2240 > >7628 > >7013 > >6135 > >=20 > >=20 > >The results of pgbench > >=20 > >=09 >1 > >20 > >50 > >100 > > >PostgreSQL (TPS) Direct > >1403 > >6805 > >6194 > >5726 > > >Pgpool (TPS) > >511 > >5430 > >5528 > >4705 > >=20 > >=20 > >As you can see in both cases even with load balance, the total >transactions >per second are lower. > >=20 > >Is this the expected behavior. Is there any way to allow more TPS when >pgpool is used? > >=20 > >There are other publications with similar results: > >=20 > >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 > >=20 > >=20 > >Regards --=20 Envoy=C3=A9 de mon appareil Android avec K-9 Mail. Veuillez excuser ma bri= =C3=A8vet=C3=A9. --=20 Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin