Re: Hot Standby performance issue

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