On Wed, Nov 21, 2012 at 2:17 PM, Merlin Moncure <mmoncure@xxxxxxxxx> wrote:
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.On Wed, Nov 21, 2012 at 12:17 PM, Vlad <marchenko@xxxxxxxxx> wrote:prepare statements can be fudged within some constraints. if prepared
> 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.
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 runningthere are a number of simple tricks to deal with this:
> (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 .
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.
Thanks.
-- vlad
