Re: [PERFORM] Hot Standby performance issue

2013-10-21 Thread Magnus Hagander
On Oct 22, 2013 1:14 AM, "Tomas Vondra" wrote: > > On 22.10.2013 00:59, sparikh wrote: > > Yes, Expalin without Analyze is taking long. It is weird. In the > > pg_stat_activity Explain was the only query running. So server was > > almost idle. Using New relic interface I checked CPU was almost idl

Re: [PERFORM] Hot Standby performance issue

2013-10-21 Thread sparikh
> Do you suggest if I remove all the data files from /data/base folder > of standby and again rebuild using rsync from primary ? do you see > any issues there.? This is just to rule out any fragmentation on > standby side. The EXPLAIN really should not do much I/O. I doubt it has anything to d

Re: [PERFORM] Hot Standby performance issue

2013-10-21 Thread Tomas Vondra
On 22.10.2013 00:59, sparikh wrote: > Yes, Expalin without Analyze is taking long. It is weird. In the > pg_stat_activity Explain was the only query running. So server was > almost idle. Using New relic interface I checked CPU was almost idle > - around 10-20%. There were some IO activity - around

Re: [PERFORM] Hot Standby performance issue

2013-10-21 Thread sparikh
Yes, Expalin without Analyze is taking long. It is weird. In the pg_stat_activity Explain was the only query running. So server was almost idle. Using New relic interface I checked CPU was almost idle - around 10-20%. There were some IO activity - around 40-50%. I forgot to mention before I could

Re: [PERFORM] Hot Standby performance issue

2013-10-21 Thread Tomas Vondra
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?

Re: [PERFORM] Hot Standby performance issue

2013-10-21 Thread sparikh
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. I agree we must upgrade to latest version (9.1.10), but unfortunately kind of resourc

Re: [PERFORM] Hot Standby performance issue

2013-10-21 Thread Tomas Vondra
On 21.10.2013 17:05, sparikh wrote: > Stupid question - when you say that a query is fast on primary but > slow on standby, are you referring to exactly the same query, > including parameter values? > > Yes . It is exactly and exactly the same query with the same > parameters. Yes, it sounds stupi

Re: [PERFORM] Hot Standby performance issue

2013-10-21 Thread sparikh
Stupid question - when you say that a query is fast on primary but slow on standby, are you referring to exactly the same query, including parameter values? Yes . It is exactly and exactly the same query with the same parameters. Yes, it sounds stupid but that is what happening. Though plan s