From: Tomas Vondra <tv@fuzzy.cz>
To: pgsql-performance@postgresql.org
Subject: Re: Hot Standby performance issue
Date: Sat, 19 Oct 2013 01:19:41 +0200
Message-ID: <5261C20D.1020003@fuzzy.cz> (raw)
In-Reply-To: <1381869626548-5774673.post@n5.nabble.com>
References: <1381869626548-5774673.post@n5.nabble.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On 15.10.2013 22:40, sparikh wrote:
> Hi, This is my first post ever in the Postgres forum. I am
> relatively new to Postgres and coming from oracle background.
>
> We have hot stand by setup to serve mainly the read only queries.
> Past few days we have been facing a performance issues on one of the
> transaction search. The search mainly utilizes 3 of our biggest
> transaction tables.
What do you mean by "transaction search"?
>
> We had recently crash on both primary and standby because of the
> space issues. Both servers were brought up and running successfully
> after that incident. The standby is in almost in sync with primary,
> far behind by less than a second. I also rebuilt all the major
> indexes on the primary. I have done some research work to address the
> issue as following.
>
> (1) I checked most of the database parameters settings and they are
> same on both primary and standby, except some specific to the
> individual server.
So, what are the basic paremeters?
And what PostgreSQL version / OS / hw are we dealing with?
> (2) Checked the explain plan for the offending query and they are
> exactly same on both the servers. Checked cpu usage on unix box and
> found it was quite low.
Well, that's hardly useful as you haven't provided the query not the
explain plan. Have you tried EXPLAIN ANALYZE?
> (3) The load on standby does not seem to be issue, because with
> absolutely no load the query takes long and most of the time
> returned with the conflict error.
Not suse I understand this. Are you saying that the standby is mostly
idle, i.e. the query seems to be stuck, and then fails with conflict
error most of the time?
> (4) The hardware settings are exactly same on both primary and
> secondary.
So what are these hardware settings? BTW do you have some stats from the
OS (CPU / IO / memory) collected at the time of the performance issue?
> (5) The same query executes very fast on primary
Query? Explain analyze?
> (6) After we recovered standby it was fine for few weeks and then
> again started slowing down.
Was it slowing down gradually, or did it start failing suddenly?
> I believe autovacuum and analyze does not need to be run on standby
> as it inherits that from primary. Please correct me if I am wrong.
Any commands that would modify the database (including vacuum and
autovacuum) are disabled on the standby.
> Any help or suggestion would be greatly appreciated. Thanks,
Please, post as much details as possible. This reports contains pretty
much no such details - query, explain or explain analyze, info about the
settings / hardware etc.
regards
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: <5261C20D.1020003@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