pg.ddx.io pgsql-admin@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Why pgpool TPS is lowest versus postgresql direct connections? 5+ messages / 3 participants [nested] [flat]
* Why pgpool TPS is lowest versus postgresql direct connections? @ 2017-02-08 16:27 Lazaro Garcia <lazaro3487@gmail.com> 0 siblings, 0 replies; 5+ messages in thread From: Lazaro Garcia @ 2017-02-08 16:27 UTC (permalink / raw) To: pgpool-general@pgpool.net; pgsql-admin 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 ^ permalink raw reply [nested|flat] 5+ messages in thread
* Why pgpool TPS is lowest versus postgresql direct connections? @ 2017-02-09 09:59 Lazaro Garcia <lazaro3487@gmail.com> 0 siblings, 2 replies; 5+ messages in thread From: Lazaro Garcia @ 2017-02-09 09:59 UTC (permalink / raw) To: pgpool-general@pgpool.net; pgsql-admin 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@gmail.com] Enviado el: miércoles, 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 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 ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [pgpool-general: 5318] Why pgpool TPS is lowest versus postgresql direct connections? @ 2017-02-10 15:14 Lazaro Garcia <lazaro3487@gmail.com> 0 siblings, 0 replies; 5+ messages in thread From: Lazaro Garcia @ 2017-02-10 15:14 UTC (permalink / raw) To: 'David Sisk -X \(dsisk - TEKSYSTEMS INC at Cisco\)' <dsisk@cisco.com>; pgpool-general@pgpool.net; pgsql-admin 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@cisco.com] Enviado el: viernes, 10 de febrero de 2017 04:58 p. m. Para: Lazaro Garcia; pgpool-general@pgpool.net; pgsql-admin@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@cisco.com> dsisk@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@pgpool.net <mailto:pgpool-general-bounces@pgpool.net> [mailto:pgpool-general-bounces@pgpool.net] On Behalf Of Lazaro Garcia Sent: Thursday, February 9, 2017 5:00 AM To: pgpool-general@pgpool.net <mailto:pgpool-general@pgpool.net> ; pgsql-admin@postgresql.org <mailto:pgsql-admin@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@gmail.com] Enviado el: miércoles, 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 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 -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin Attachments:image001.png (image/png, 41.7K) download
view imagepgbench_direct.log (application/octet-stream, 4.7K) download pgbench_pgpool.log (application/octet-stream, 4.7K) download ^ permalink raw reply [nested|flat] 5+ messages in threadimage002.gif (image/gif, 134B) download
view image
* Re: [pgpool-general: 5318] Why pgpool TPS is lowest versus postgresql direct connections? @ 2017-02-10 15:57 David Sisk -X (dsisk - TEKSYSTEMS INC at Cisco) <dsisk@cisco.com> parent: Lazaro Garcia <lazaro3487@gmail.com> 1 sibling, 0 replies; 5+ messages in thread From: David Sisk -X (dsisk - TEKSYSTEMS INC at Cisco) @ 2017-02-10 15:57 UTC (permalink / raw) To: Lazaro Garcia <lazaro3487@gmail.com>; pgpool-general@pgpool.net <pgpool-general@pgpool.net>; 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@cisco.com<mailto:dsisk@cisco.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@pgpool.net [mailto:pgpool-general-bounces@pgpool.net] On Behalf Of Lazaro Garcia Sent: Thursday, February 9, 2017 5:00 AM To: pgpool-general@pgpool.net; pgsql-admin@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@gmail.com] Enviado el: miércoles, 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 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 Attachments:image001.png (image/png, 41.7K) download
view image^ permalink raw reply [nested|flat] 5+ messages in threadimage002.gif (image/gif, 134B) download
view image
* Re: Why pgpool TPS is lowest versus postgresql direct connections? @ 2017-02-11 11:58 Jehan-Guillaume de Rorthais <ioguix@free.fr> parent: Lazaro Garcia <lazaro3487@gmail.com> 1 sibling, 0 replies; 5+ messages in thread From: Jehan-Guillaume de Rorthais @ 2017-02-11 11:58 UTC (permalink / raw) To: pgsql-admin@postgresql.org,Lazaro Garcia <lazaro3487@gmail.com>; pgpool-general@pgpool.net Hi, Le 9 février 2017 10:59:53 GMT+01:00, Lazaro Garcia <lazaro3487@gmail.com> a écrit : [...] >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 - pgpool itself can become a bottleneck, especially during bench with lot of small and fast queries - 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] >Enviado el: miércoles, 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 > >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 -- Envoyé de mon appareil Android avec K-9 Mail. Veuillez excuser ma brièveté. -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin ^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2017-02-11 11:58 UTC | newest] Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2017-02-08 16:27 Why pgpool TPS is lowest versus postgresql direct connections? Lazaro Garcia <lazaro3487@gmail.com> 2017-02-09 09:59 Why pgpool TPS is lowest versus postgresql direct connections? Lazaro Garcia <lazaro3487@gmail.com> 2017-02-10 15:57 ` David Sisk -X (dsisk - TEKSYSTEMS INC at Cisco) <dsisk@cisco.com> 2017-02-11 11:58 ` Jehan-Guillaume de Rorthais <ioguix@free.fr> 2017-02-10 15:14 Re: [pgpool-general: 5318] Why pgpool TPS is lowest versus postgresql direct connections? Lazaro Garcia <lazaro3487@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox