On Wed, Nov 21, 2012 at 7:29 AM, Vlad Marchenko <marchenko@gmail.com> wrote:
> update on my problem: despite pgbouncer, the problem still occures on my
> end.
As Merlin asked, how big is the pool? Maybe you are using a large
enough pool so as to defeat the purpose of restricting the number of
connections.
> Also, interesting observation - I ran several tests with pgbench, using
> queries that I think are prone to trigger high-sys-cpu-stall. What I noticed
> is when pgbench is started with prepared mode, the system behaves fine
> during stress-test (high user cpu - 85-90%, low sys cpu - 5-7%), high TPS.
> Though when I used non-prepared modes, the sys cpu portion jumps to 40% (and
> tps drops dramatically as well, but this is understandable). The test
> queries are pretty long (10kb+), with couple of outer joins across
> 1000-record tables with indexes.
Could you sanitize the queries (and some statements to generate dummy
data) enough to share?
>
> Maybe, we are looking in a wrong place and the issue is somewhere within
> planer/parser? Is there some extensive locking used in there?
I don't think the locking is particular extensive, but it could be
enough extra to drive something over the edge.
But it would be the same nature of locking as elsewhere (spinlocks and
lwlocks), so it doesn't really change the nature of the problem, which
is still "Why do these user-space locks turn into high SYS cpu?"
> Another observation - it's harder to trigger high-sys-cpu stall on a freshly
> restarted postgresql. Though if it was running for a while, then it's much
> easier to do.
Maybe the long running time has built up enough resource usage to
cause the kernel scheduler to get into a snit, so it decides to
preempt the process while it holds a spinlock, and then refuses to run
it again for a while.
Cheers,
Jeff