On 21.10.2013 23:18, sparikh wrote:
> Yes, both Explain and Explain Analyse are taking time. As you
> suggested I set the lock parameters, but no locks are observed. Also
> checked pg_stat_activity and none of the sessions are either waiting
> are blocked.
Not even the one running the explain? That's weird. Is the backend just
sitting there idle, or is it consuming some CPU?
If it's burning CPU, try to run perf to see where it's spending time.
We've already recommended this at least twice, but I haven't seen any
report yet. This might show if there are any issues with spinlocks
(which don't show in pg_locks etc.).
What about long-open transactions, not necessarily blocked/waiting?
SELECT xact_start, current_query, waiting
FROM pg_stat_activity WHERE xact_start ASC LIMIT 10;
> I agree we must upgrade to latest version (9.1.10), but
> unfortunately kind of resources (not only man power) we are having it
> is going to be extremely challenging task for us. Of course all other
> options are not working then we have to take the tough route. No
> choice.
It's not really about this particular issue. As both me and Kevin
pointed out, there are some pretty important ecurity fixes etc. You need
to update irrespectedly of this issue.
BTW minor upgrades (including 9.1.1 -> 9.1.10) are supposed to be
relatively simple, as the the format remains the same. So it's a matter
of shutting down the machine, updating the binaries and starting again.
Of course, I'm not familiar with your setup and requirements.
> I am also working with sys admin to rule any issues at the OS or VM
> level.
OK, good. Post a summary of what you checked.
Tomas