Re: [PERFORM] Hot Standby performance issue
Anybody has any idea, or pointer ? This is a high priority issue I have resolve at work. Any help would be of great help. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Hot-Standby-performance-issue-tp5774673p5775103.html Sent from the PostgreSQL - performance mailing list archive at Nabble.com. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Hot Standby performance issue
http://www.postgresql.org/docs/current/static/hot-standby.html#HOT-STANDBY-CAVEATS On Fri, Oct 18, 2013 at 11:49 PM, sparikh spar...@ecotality.com wrote: Anybody has any idea, or pointer ? This is a high priority issue I have resolve at work. Any help would be of great help. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Hot-Standby-performance-issue-tp5774673p5775103.html Sent from the PostgreSQL - performance mailing list archive at Nabble.com. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] 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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Hot Standby performance issue
On 18.10.2013 23:49, sparikh wrote: Anybody has any idea, or pointer ? This is a high priority issue I have resolve at work. Any help would be of great help. To help you we really need much more specific report. The one you posted contains no details whatsoever - it doesn't even mention what version of PostgreSQL you use, on what OS or the query. Please, provide substantially more details. We can't really help you without it. regards Tomas PS: Please, format your messages reasonably, i.e. not one huge paragraph of text. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Hot Standby performance issue
Hi Tomas, Thanks so much for your response and sorry for not providing the enough details. I have attached the zip file which has query,explain plan and database parameter settings for both primary and secondary. Please note that query has multiple unions only the first query on top is causing the performance issue. Transaction search is one of the feature in our Admin user interface(web portal) where user can search for the transactions against our OLTP database. The attached query is generated dynamically by the application. (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? The standby is not idle all the time. What I meant was even with no user activity or no active user sessions, if I issue the query directly from pgadmin tool it takes for ever. Hardware settings both primary and secondary : === Red Hat Enterprise Linux Server release 5.5 (Tikanga) Linux 2.6.18-194.26.1.el5 x86_64 4 CPUs 16 GB RAM Intel Xeon Postgresql Version: = PostgreSQL 9.1.1 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-51), 64-bit 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? Honestly speaking I do not exactly, when users started reporting the issue I started looking into it. But the performance was good in September and somewhere in October it started slowing down. I guess it was gradual. There were no code change in the application or major change in the data volume. Hope this helps. Please let me know if you need any other details. Thanks Again. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Hot-Standby-performance-issue-tp5774673p5775123.html Sent from the PostgreSQL - performance mailing list archive at Nabble.com. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
