On 01/19/2018 06:19 PM, Konstantin Knizhnik wrote: > > > On 19.01.2018 20:03, Claudio Freire wrote: >> >> >> On Fri, Jan 19, 2018 at 1:53 PM, Konstantin Knizhnik >> <k.knizh...@postgrespro.ru <mailto:k.knizh...@postgrespro.ru>> wrote: >> >> >> >> On 19.01.2018 19:28, Pavel Stehule wrote: >>> >>> >>> When I've been thinking about adding a built-in >>> connection pool, my >>> rough plan was mostly "bgworker doing something like >>> pgbouncer" (that >>> is, listening on a separate port and proxying everything >>> to regular >>> backends). Obviously, that has pros and cons, and >>> probably would not >>> work serve the threading use case well. >>> >>> >>> And we will get the same problem as with pgbouncer: one >>> process will not be able to handle all connections... >>> Certainly it is possible to start several such scheduling >>> bgworkers... But in any case it is more efficient to >>> multiplex session in backend themselves. >>> >>> >>> pgbouncer hold all time client connect. When we implement the >>> listeners, then all work can be done by worker processes not by >>> listeners. >>> >> >> Sorry, I do not understand your point. >> In my case pgbench establish connection to the pgbouncer only >> once at the beginning of the test. >> And pgbouncer spends all time in context switches (CPU usage is >> 100% and it is mostly in kernel space: top of profile are kernel >> functions). >> The same picture will be if instead of pgbouncer you will do such >> scheduling in one bgworker. >> For the modern systems are not able to perform more than several >> hundreds of connection switches per second. >> So with single multiplexing thread or process you can not get >> speed more than 100k, while at powerful NUMA system it is possible >> to achieve millions of TPS. >> It is illustrated by the results I have sent in the previous mail: >> by spawning 10 instances of pgbouncer I was able to receive 7 >> times bigger speed. >> >> >> I'm sure pgbouncer can be improved. I've seen async code handle >> millions of packets per second (zmq), pgbouncer shouldn't be radically >> different. >> >> > With pgbouncer you will never be able to use prepared statements which > slows down simple queries almost twice (unless my patch with > autoprepared statements is committed). >
I don't see why that wouldn't be possible? Perhaps not for prepared statements with simple protocol, but I'm pretty sure it's doable for extended protocol (which seems like a reasonable limitation). That being said, I think it's a mistake to turn this thread into a pgbouncer vs. the world battle. I could name things that are possible only with standalone connection pool - e.g. pausing connections and restarting the database without interrupting the clients. But that does not mean built-in connection pool is not useful. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
