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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
