On Wed, Nov 14, 2012 at 4:08 PM, John R Pierce <pierce@hogranch.com> wrote:
> On 11/14/12 1:34 PM, Vlad wrote:
>>
>> thanks for your feedback. While implementing connection pooling would make
>> resources utilization more efficient, I don't think it's the root of my
>> problem. Most of the connected clients are at IDLE. When I do
>>
>> select * from pg_stat_activity where current_query not like '%IDLE%';
>>
>> I only see several active queries at any given time.
>
>
> what about during these spikes?
Yeah -- if you are seeing a lot of queries pile up during these times,
it's time to explore connection pooler because it will keep system
load manageable during these situations.
things to check:
*) blocked queries (pg_locks/pg_stat_activity)
*) i/o wait. in particular, is linux page cache flushing.
*) query stampede: we will want to measure TPS leading into the stall
and out of it.
*) anything else running on the box?
*) you have a large amount of table? maybe this statistics file related?
*) let's log checkpoints to see if there is correlation with stall
*) nice to have vmstat/iostat during/before/after stall. for example,
massive spike of context switches during stall could point to o/s
scheduler issue.
merlin