Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1cbrAI-0001On-TH for pgsql-admin@arkaria.postgresql.org; Thu, 09 Feb 2017 16:01:47 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1cbrAI-0001Tc-Fx for pgsql-admin@arkaria.postgresql.org; Thu, 09 Feb 2017 16:01:46 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1cbr8a-0007sW-2N for pgsql-admin@postgresql.org; Thu, 09 Feb 2017 16:00:00 +0000 Received: from mail-it0-x244.google.com ([2607:f8b0:4001:c0b::244]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1cbr8W-0002by-2x for pgsql-admin@postgresql.org; Thu, 09 Feb 2017 15:59:59 +0000 Received: by mail-it0-x244.google.com with SMTP id 203so2641890ith.2 for ; Thu, 09 Feb 2017 07:59:55 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=from:to:references:in-reply-to:subject:date:message-id:mime-version :thread-index:content-language; bh=zG8VVSXqUWidEsM6ZEJZxD9VKQB+v0KxVGITlEpOEQQ=; b=QYTAWrWxF9BEFtulWHFxgAUIwk9snaiNR85iP/NewxN5Gt2qOS5mj1YpUfQQEYELR+ G7k/iSeGWCT2G3aR/cYEKSFlcc2oOJQYsLak9GzEI53M6hzD9yKQkUa7QIL+pVfipkIU 65MyT1RnMkwakGpFCPmxtcJvaOvsB4va6SbGo1ukj+6cCwbvsNc7A0wvRasKzkbGNZXg 5o8FBFk7N/L+4UA3I/1f4sOkbLZqNG77pQ8+do3KGhvBhxhXdWUVsorTxuXuqZDUs7XX sDZK+3ldLRTYVrsPUX5R0VMh+DPa/fn3b/jyDgXeGv2cVqtzIvIjJKOWYZ9JodTjDdX1 Q02w== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:from:to:references:in-reply-to:subject:date :message-id:mime-version:thread-index:content-language; bh=zG8VVSXqUWidEsM6ZEJZxD9VKQB+v0KxVGITlEpOEQQ=; b=Vsy3YCCr235dBrtR7Q6XBjWLJdi/MdXeZ84A/KWixPBpmWa1lSP4VDQBGhV5PCUGiF RKCV84q/QUvGIo+zdRZ2HpaTjt/je3zjwTcdK+S3rsIwHENzq/6BVymANzdHoI/aCZ0y rLZ3/DZTtisJF+yvF1Nu70bPJ4VsuIAm7kptVDQUYQE+YooI63wIxtoPUT+r0SQc/oMG m5iqbnWnFeRfB0iEEsv2ALqreEeeWrcRC9n4f5ayjOAtKKuFkCl3QSWOlxLFmyXMhWnn m7MQM5KHk/LWSPSXaZ+skss1nYK6UTmbNFrPCbzYF7+j/T6ceV9yBXLD0H3XpdEPx3a6 5+RQ== X-Gm-Message-State: AMke39kJ1MTxdxokTKBuApmYPzn9oT0tb1TpadiFfnumJhItcD0nySry5QR0OEOgOz8WKg== X-Received: by 10.36.82.22 with SMTP id d22mr4102980itb.0.1486655993898; Thu, 09 Feb 2017 07:59:53 -0800 (PST) Received: from irylachi ([200.55.161.75]) by smtp.gmail.com with ESMTPSA id n67sm3088356ith.0.2017.02.09.07.59.47 (version=TLS1_2 cipher=ECDHE-RSA-AES128-SHA bits=128/128); Thu, 09 Feb 2017 07:59:53 -0800 (PST) From: "Lazaro Garcia" To: , References: In-Reply-To: Subject: Why pgpool TPS is lowest versus postgresql direct connections? Date: Thu, 9 Feb 2017 10:59:53 +0100 Message-ID: <001d01d282bb$43909610$cab1c230$@gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_001E_01D282C3.A558CEA0" X-Mailer: Microsoft Outlook 15.0 Thread-Index: AdKCJvz93UmYdSheR8OTaueypO6RWAAk/IBw Content-Language: es-mx X-Pg-Spam-Score: -0.9 (/) 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 This is a multipart message in MIME format. ------=_NextPart_000_001E_01D282C3.A558CEA0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable The correct results reported by sysbench was: =20 Concurrent Users 1 20 50 100 PostgreSQL 3582 11943 12852 10618 Pgpool 2240 7628 7013 6135 =20 Is there any way to tuning this behavior? =20 Regards =20 =20 De: Lazaro Garcia [mailto:lazaro3487@gmail.com]=20 Enviado el: mi=E9rcoles, 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 ------=_NextPart_000_001E_01D282C3.A558CEA0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

The correct results = reported by sysbench was:

 

Concurrent = Users

1

20

50

100

PostgreSQL

3582

<= /td>

11943

=

12852

=

10618

=

Pgpool

2240

<= /td>

7628

<= /td>

7013

<= /td>

6135

<= /td>

 

Is there any way to = tuning this behavior?

 

Regards

 

 

De: Lazaro Garcia = [mailto:lazaro3487@gmail.com]
Enviado el: mi=E9rcoles, 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

<= /td>

20936

=

25743

=

27344

=

Pgpool = (TPS)

2240

<= /td>

7628

<= /td>

7013

<= /td>

6135

<= /td>

 

 

The results = of pgbench

 

1

20

50

100

PostgreSQL (TPS) = Direct

1403

<= /td>

6805

<= /td>

6194

<= /td>

5726

<= /td>

Pgpool = (TPS)

511

5430

<= /td>

5528

<= /td>

4705

<= /td>

 

 

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/ro= ry_breuk_gerrie_veerman_-_report.pdf    (page = 28)

http://www.mail-archive.com/pgpool-general@pgfoundry.org/msg03326.= html

 

 

Regards

------=_NextPart_000_001E_01D282C3.A558CEA0--