Hi,
On 20.10.2013 19:58, sparikh wrote:
> Thanks so much Tomas and Kevin for your valuable inputs. I am getting
> very good response from this forum and learning so many new stuffs. I
> will try all those options and will let you update .
>
>
> standby_performance_issue.rar
> <http://postgresql.1045698.n5.nabble.com/file/n5775181/standby_performance_issue.rar>
Yup, this time it worked. Anyway, next time please consider posting the
explain plan through explain.depesz.com, it's way more readable than the
plans wrapped when posted inline.
For example this is your plan: http://explain.depesz.com/s/SBVg
However this shows only 18 ms runtime. Is this one of the slow runs? I'm
assuming it's not, because 18ms seems quite fast tome. In that case it's
pretty much useless, because we need to see a plan for one of the slow
queries.
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?
Or is the query running much longer than the reported 18 ms?
> On further digging I found from the new relic report that as soon as
> I execute query IO spikes immediately (100%). But the same query on
> primary executes very fast.
Well, that might just as well mean that the primary has the data in
filesystem cache, and standby needs to read that from file. If you
repeat the query multiple times, do you still see I/O spike?
How do you use the standby? Is it just sitting there most of the time,
or is it queried about as much as the primary?
BTW when checking the configuration info you've sent, I've noticed this:
"hot_standby_feedback","off"
IIRC you've reported the query frequently crashes on the standby because
of replication conflicts. Why don't you set this to on?
> I am not sure if postgres has some utility like what oracle's tkprof
> or AWR where I can exactly pin point where exactly the query spends
> time. I will try Tomas' suggestions perf and strace.
No, at least the community version. But the explain analyze is usually a
good source guide.
Tomas