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.