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: Hot Standby performance issue
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
