Message-ID: <526460B7.3020008@fuzzy.cz>
Date: Mon, 21 Oct 2013 01:01:11 +0200
From: Tomas Vondra <tv@fuzzy.cz>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:24.0) Gecko/20100101 Thunderbird/24.0
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: Hot Standby performance issue
References: <1381869626548-5774673.post@n5.nabble.com>
 <1382291901534-5775181.post@n5.nabble.com>
In-Reply-To: <1382291901534-5775181.post@n5.nabble.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance