Search Postgresql Archives

Re: High SYS CPU - need advise

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Merlin,

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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux