Merlin, On Wed, Nov 21, 2012 at 2:17 PM, Merlin Moncure <mmonc...@gmail.com> wrote:
> On Wed, Nov 21, 2012 at 12:17 PM, Vlad <marche...@gmail.com> wrote: > > It turned out we can't use transaction mode, cause there are prepared > > statement used a lot within code, while processing a single http request. > > prepare statements can be fudged within some constraints. if prepared > statements are explicitly named via PREPARE, you can simply prepare > them all on server connection via connect_query setting and disable > the manual preparation. you then change the server_reset_query so > that they are not discarded. some basic experimentation might confirm > if this is viable strategy. automatic protocol level statements can > be an issue though. > We have 350k+ lines of code in our app, so this is not quite feasible as I'd wish. > > Also, I can't 100% rule out that there won't be any long running > > (statistical) queries launched (even though such requests should not > come to > > this database), which would occupy connection for longer time, but do not > > create any race condition... So having pool size at 8 may be too slim . > > there are a number of simple tricks to deal with this: > 1) move long running queries to their own pool (by changing login user > or connection string) > 2) bypass pgbouncer in those cases > 3) increase pool size > > It's pretty much already setup so that long running queries should not hit the same DB cluster as those with (potentially) high connection/query rates, but I still can't rule out that no long-running queries will be issued via pgbouncer. Either case - it seems that the combination of pool size = 200 / pool mode = session / server_lifetime = 30 makes things stable for now. I'm planning to repeat my case on 2.6.x kernel, but it will be a while before I have chance to do that. Thanks. -- vlad
