From: sparikh <sparikh@ecotality.com>
To: pgsql-performance@postgresql.org
Subject: Hot Standby performance issue
Date: Tue, 15 Oct 2013 13:40:26 -0700 (PDT)
Message-ID: <1381869626548-5774673.post@n5.nabble.com> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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. 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. (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. (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. (4) The hardware settings are exactly same
on both primary and secondary. (5) The same query executes very fast on
primary (6) After we recovered standby it was fine for few weeks and then
again started slowing down. 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 help or suggestion would be greatly appreciated. Thanks,
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Hot-Standby-performance-issue-tp5774673.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
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: sparikh@ecotality.com
Subject: Re: Hot Standby performance issue
In-Reply-To: <1381869626548-5774673.post@n5.nabble.com>
* 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