Re: High SYS CPU - need advise

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.

> 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

merlin