Message-ID: <5261C20D.1020003@fuzzy.cz>
Date: Sat, 19 Oct 2013 01:19:41 +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>
In-Reply-To: <1381869626548-5774673.post@n5.nabble.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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