Re: High SYS CPU - need advise

Merlin,

On Wed, Nov 21, 2012 at 2:17 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:

> On Wed, Nov 21, 2012 at 12:17 PM, Vlad <marchenko(at)gmail(dot)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