[pgpool-general: 5326] Re: Why pgpool TPS is lowest versus postgresql direct connections?

Sat Feb 11 00:14:20 JST 2017

Thank you very much for your explanation.

The num_init_children configuration parameter is bigger than concurrent
users used in the test.

Attached is the results of the test, as you can see with pgbench directly,
the TPS amount are almost double.

The test was performed over two virtual machines with 4 processors and 2 GB
of RAM each one.

Pgbech was executed from other machine.

Ubuntu Server 16.04, PostgreSQL 9.6.1, Pgpool-3.5.5.

Is there any way to increase the pgpool results?

Regards.

De: David Sisk -X (dsisk - TEKSYSTEMS INC at Cisco) [mailto:dsisk at cisco.com]

Enviado el: viernes, 10 de febrero de 2017 04:58 p. m.
Para: Lazaro Garcia; pgpool-general at pgpool.net; pgsql-admin at postgresql.org
Asunto: RE: [pgpool-general: 5318] Why pgpool TPS is lowest versus
postgresql direct connections?

My immediate thought would be this:  PGPool is much slower with a larger
number of concurrent users because some of the connections are being queued
by PGPool. Take a look at your num_init_children parameter
unlike most
connection pooling layers, PGPool doesn’t have separate config parameters
for the initial number of backend connections and the max number of backend
connections
num_init_children controls both.  Increase num_init_children to
at least the number of concurrent connections plus a few, and re-try the
benchmark tests.

Also, the benefits of load balancing don’t tend to show up well with pgbench
or sysbench default benchmarks.  PGPool imposes some overhead in determining
which queries can be load-balanced against a standby
with SELECT queries
that execute in only a few milliseconds, it might take PGPool longer to make
that decision than it would have to just send it straight to the primary.
So, you won’t see the load balancing benefits with SELECT queries that
execute in a few milliseconds, but when you send it SELECT queries that take
a few seconds or longer to execute you’ll start to see the benefits.  You
could create a custom benchmark that has some high-reduction queries that
would take a few seconds to execute, then run the benchmarks with that
you
should then see scalability improvements from the load balancing.

Hope this helps,

David Sisk

Engineer - Software

 <mailto:dsisk at cisco.com> dsisk at cisco.com

Tel: 

Cisco Systems, Inc.

7025-6 Kit Creek Road PO Box 14987
RESEARCH TRIANGLE PARK
27709-4987
United States
cisco.com

Think before you print.

This email may contain confidential and privileged material for the sole use
of the intended recipient. Any review, use, distribution or disclosure by
others is strictly prohibited. If you are not the intended recipient (or
authorized to receive for the recipient), please contact the sender by reply
email and delete all copies of this message.

Please  <http://www.cisco.com/web/about/doing_business/legal/cri/index.html>
click here for Company Registration Information.

From: pgpool-general-bounces at pgpool.net
<mailto:pgpool-general-bounces at pgpool.net>
[mailto:pgpool-general-bounces at pgpool.net] On Behalf Of Lazaro Garcia
Sent: Thursday, February 9, 2017 5:00 AM
To: pgpool-general at pgpool.net <mailto:pgpool-general at pgpool.net> ;
pgsql-admin at postgresql.org <mailto:pgsql-admin at postgresql.org> 
Subject: [pgpool-general: 5318] Why pgpool TPS is lowest versus postgresql
direct connections?

The correct results reported by sysbench was:

Concurrent Users

1

20

50

100

PostgreSQL

3582

11943

12852

10618

Pgpool

2240

7628

7013

6135

Is there any way to tuning this behavior?

Regards

De: Lazaro Garcia [mailto:lazaro3487 at gmail.com] 
Enviado el: miércoles, 8 de febrero de 2017 05:28 p. m.
Para: 'pgpool-general at pgpool.net'; 'pgsql-admin at 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

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20170210/95bf68dd/attachment-0001.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image001.png
Type: image/png
Size: 42730 bytes
Desc: not available
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20170210/95bf68dd/attachment-0001.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image002.gif
Type: image/gif
Size: 134 bytes
Desc: not available
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20170210/95bf68dd/attachment-0001.gif>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: pgbench_direct.log
Type: application/octet-stream
Size: 4812 bytes
Desc: not available
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20170210/95bf68dd/attachment-0002.obj>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: pgbench_pgpool.log
Type: application/octet-stream
Size: 4812 bytes
Desc: not available
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20170210/95bf68dd/attachment-0003.obj>