On Wed, Nov 21, 2012 at 7:29 AM, Vlad Marchenko <marchenko@xxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
