Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1cbaiO-00068P-Dy for pgsql-admin@arkaria.postgresql.org; Wed, 08 Feb 2017 22:27:52 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1cbaiN-0003kw-Ss for pgsql-admin@arkaria.postgresql.org; Wed, 08 Feb 2017 22:27:51 +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 1cbaiM-0003ja-Ih for pgsql-admin@postgresql.org; Wed, 08 Feb 2017 22:27:50 +0000 Received: from mail-io0-x243.google.com ([2607:f8b0:4001:c06::243]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1cbaiI-0007ry-M7 for pgsql-admin@postgresql.org; Wed, 08 Feb 2017 22:27:50 +0000 Received: by mail-io0-x243.google.com with SMTP id 101so449375iom.0 for ; Wed, 08 Feb 2017 14:27:46 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=from:to:subject:date:message-id:mime-version:thread-index :content-language; bh=1raCD1+cFFlDuTC6XtNIPE2Y/cgsoUnXaw1Ud4k+NcE=; b=hUFaBVpr6XyfbcXdIPRWfVCs9FATLEGjH3aGijI27ypN8FaJ0vHxdPzOYx/DxwDuEj Y5vdXUPpLwdgpKLWwM5o76c2zNw+txRCENlxODwxyyauZjGwlgVMPu8JJU00+wTNMfhh p/6CLcYblw9PcfhS/UWbvAnA/ILZjbxRZHCC/776hsdjV6hdL904CgDHV9e9Z5t0g3zX eIfzr2WlWrABseJtcnH9nU1qzBTT6v9/E9xA6cYuZkIkL4PQkZEWXrhrCkqWCfGNGItc VmvexFvIeGrtFaDqFZvvZjragxWFOO1aRljw2IKHYfqxQ7KP87yqFFm9qWWGbvliaET7 mr5w== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:from:to:subject:date:message-id:mime-version :thread-index:content-language; bh=1raCD1+cFFlDuTC6XtNIPE2Y/cgsoUnXaw1Ud4k+NcE=; b=eV0vbwTS3ZBDg/aGTSRTvNSVzPngaAGclkmf94UFp8C5xTeUKtg3A7IUFxoeJP7XpA rteqR8TRghSJlPuQj///pbt8ppWPfDMF5LloWrWcwhERQqj0bAW4HbgLZPoZJhAqBteB 5yP5/0a1b+ZrN7wCWN+gea9Xvi/uB1Q6ShSImFnvcAS7/1r4SEgqOZkIdv6CP6kFofQv 0wm3oLpsjGGW7VHaUyjkwz3U15Fb08yfTedZjkgIpnWTZ66By/hn15Co1y9q/sV3qhy4 O4kNXdp1bvd5SvMI7m9wrkipkiRuFOzDJ8cQNSiToTsxMQkGw6jjNQHW9z6aiBvA3EmX r+wQ== X-Gm-Message-State: AMke39nieR99WaBi5jWzBtbXZt5pI2ZFIhWb0njvumNi2IVWUSKkBk1BIipKmm6nCizEyw== X-Received: by 10.107.21.4 with SMTP id 4mr489907iov.139.1486592864743; Wed, 08 Feb 2017 14:27:44 -0800 (PST) Received: from irylachi ([200.55.161.75]) by smtp.gmail.com with ESMTPSA id d11sm1803789itg.1.2017.02.08.14.27.37 (version=TLS1_2 cipher=ECDHE-RSA-AES128-SHA bits=128/128); Wed, 08 Feb 2017 14:27:44 -0800 (PST) From: "Lazaro Garcia" To: , Subject: Why pgpool TPS is lowest versus postgresql direct connections? Date: Wed, 8 Feb 2017 17:27:42 +0100 Message-ID: <002501d28228$46ff87e0$d4fe97a0$@gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0026_01D28230.A8C3EFE0" X-Mailer: Microsoft Outlook 15.0 Thread-Index: AdKCJvz93UmYdSheR8OTaueypO6RWA== 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_0026_01D28230.A8C3EFE0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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 ------=_NextPart_000_0026_01D28230.A8C3EFE0 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

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_0026_01D28230.A8C3EFE0--