From: Tomas Vondra <tv@fuzzy.cz>
To: pgsql-performance@postgresql.org
Subject: Re: Hot Standby performance issue
Date: Mon, 21 Oct 2013 01:01:11 +0200
Message-ID: <526460B7.3020008@fuzzy.cz> (raw)
In-Reply-To: <1382291901534-5775181.post@n5.nabble.com>
References: <1381869626548-5774673.post@n5.nabble.com>
<1382291901534-5775181.post@n5.nabble.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tv@fuzzy.cz
Subject: Re: Hot Standby performance issue
In-Reply-To: <526460B7.3020008@fuzzy.cz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox