From: | "David Sisk -X (dsisk - TEKSYSTEMS INC at Cisco)" <dsisk(at)cisco(dot)com> |
---|---|
To: | Lazaro Garcia <lazaro3487(at)gmail(dot)com>, "pgpool-general(at)pgpool(dot)net" <pgpool-general(at)pgpool(dot)net>, "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org> |
Subject: | Re: [pgpool-general: 5318] Why pgpool TPS is lowest versus postgresql direct connections? |
Date: | 2017-02-10 15:57:37 |
Message-ID: | 9455fab2a1fe40a68d97c81fdda72b4d@XCH-RCD-012.cisco.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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,
[banner2]
David Sisk
Engineer - Software
dsisk(at)cisco(dot)com<mailto:dsisk(at)cisco(dot)com>
Tel:
Cisco Systems, Inc.
7025-6 Kit Creek Road PO Box 14987
RESEARCH TRIANGLE PARK
27709-4987
United States
cisco.com
[http://www.cisco.com/assets/swa/img/thinkbeforeyouprint.gif]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 click here<http://www.cisco.com/web/about/doing_business/legal/cri/index.html> for Company Registration Information.
From: pgpool-general-bounces(at)pgpool(dot)net [mailto:pgpool-general-bounces(at)pgpool(dot)net] On Behalf Of Lazaro Garcia
Sent: Thursday, February 9, 2017 5:00 AM
To: pgpool-general(at)pgpool(dot)net; pgsql-admin(at)postgresql(dot)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(dot)com]
Enviado el: miércoles, 8 de febrero de 2017 05:28 p. m.
Para: 'pgpool-general(at)pgpool(dot)net'; 'pgsql-admin(at)postgresql(dot)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(at)pgfoundry(dot)org/msg03326.html
Regards
From | Date | Subject | |
---|---|---|---|
Next Message | gleeco | 2017-02-11 02:16:46 | wal error 'already been removed' on promotion |
Previous Message | Lazaro Garcia | 2017-02-10 15:14:20 | Re: [pgpool-general: 5318] Why pgpool TPS is lowest versus postgresql direct connections? |