From: sparikh <sparikh@ecotality.com>
To: pgsql-performance@postgresql.org
Subject: Re: Hot Standby performance issue
Date: Fri, 18 Oct 2013 17:53:36 -0700 (PDT)
Message-ID: <1382144016561-5775123.post@n5.nabble.com> (raw)
In-Reply-To: <5261C20D.1020003@fuzzy.cz>
References: <1381869626548-5774673.post@n5.nabble.com>
<5261C20D.1020003@fuzzy.cz>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: sparikh@ecotality.com
Subject: Re: Hot Standby performance issue
In-Reply-To: <1382144016561-5775123.post@n5.nabble.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox