Re: Hot Standby performance issue

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 stupid but that is what happening. Though
> plan says it is 18ms it runs for more than 15-20 mins and finally
> returns with conflict error : " ERROR: canceling statement due to
> conflict with recovery "

OK.

> Even the to run execute plan itself takes very long on standby. Just
> to get the execute plan on standby is turning out big deal.

Do you mean EXPLAIN or EXPLAIN ANALYZE?

So far we've seen just EXPLAIN ANALYZE - can you try just EXPLAIN? If it
locks, it's either because of something expensive in the planning, or
locking.

The locking is much more likely, because the primary is behaving just
fine and the resulting plan is exactly the same on both ends.

> Regarding IO spike, yes I can understand that if data is not
> available in the memory then it has to get it from disk. But the
> thing is it remains there as much time until query returns with Query
> conflict error.

I don't think the I/O is a problem at all, because the query takes just
18 milliseconds. However that does not include planning, so either a lot
of time spent waiting for a lock or doing a lot of stuff on CPU, won't
be reported here.

What you can do to debug this is either look at pg_locks on the standby
for connections with "granted=f", or connect using psql and do this

set log_lock_waits = true;
set client_min_messages = log;

EXPLAIN ... query ...;

and it should print what locks the connection is waiting for. Then you
may investigate further, e.g. check who's holding the lock in
pg_stat_activity etc.

But again, I think spending a single minute on this before upgrading to
the current version is a waste of time.

Tomas