On Wed, Nov 21, 2012 at 2:17 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
On Wed, Nov 21, 2012 at 12:17 PM, Vlad <marchenko@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.