On 01/19/2018 06:07 PM, Konstantin Knizhnik wrote: > > ... > >>>> 3) Is there any sort of shrinking the pools? I mean, if the backend is >>>> idle for certain period of time (or when we need backends for other >>>> databases), does it get closed automatically? >>>> >>> When client is disconnected, client session is closed. But backen is not >>> terminated even if there are no more sessions at this backend. >>> It was done intentionally, to avoid permanent spawning of new processes >>> when there is one or few clients which frequently connect/disconnect to >>> the database. >>> >> Sure, but it means a short peak will exhaust the backends indefinitely. >> That's acceptable for a PoC, but I think needs to be fixed eventually. >> > Sorry, I do not understand it. > You specify size of backends pool which will server client session. > Size of this pool is chosen to provide the best performance at the > particular system and workload. > So number of backends will never exceed this optimal value even in case > of "short peak". > From my point of view terminating backends when there are no active > sessions is wrong idea in any case, it was not temporary decision just > for PoC. >
That is probably true when there is just a single pool (for one database/user). But when there are multiple such pools, it forces you to keep the sum(pool_size) below max_connections. Which seems strange. I do think the ability to evict backends after some timeout, or when there is pressure in other pools (different user/database) is rather useful. >> >> Well, I haven't said it has to be single-threaded like pgbouncer. I >> don't see why the bgworker could not use multiple threads internally (of >> course, it'd need to be not to mess the stuff that is not thread-safe). >> > > Certainly architecture with N multiple scheduling bgworkers and M > executors (backends) may be more flexible > than solution when scheduling is done in executor itself. But we will > have to pay extra cost for redirection. > > I am not sure that finally it will allow to reach better performance. > More flexible solution in many cases doesn't mean more efficient solution. > Sure, I wasn't really suggesting it's a clear win. I was responding to your argument that pgbouncer in some cases reaches 100% CPU utilization - that can be mitigated to a large extent by adding threads. Of course, the cost for extra level of indirection is not zero. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
