Re: Hot Standby performance issue

Hi,

On 19.10.2013 02:53, sparikh wrote:
> 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.

I see no attachment, so it got lost somehow. Can you post the query on
explain.depesz.com and the query inline? Assuming it's not a horror-like
story produced by some ORM, in that case an attachment is probably
appropriate, not to cause hear-attack to casual readers.

> Please note that query has multiple unions only the first query on top is
> causing the performance issue.

So, if you run only the problematic part, is it faster / slower. If it's
equally slow then we can deal with a simpler query, right?

> 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.

I.e. it's a form in the UI, and the application generates query matching
some fields in a form.

>> (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.

IIRC I was unable to parse your description of what's happening on the
standby (might be my fault, as I'm not a native speaker).

I'm still not sure whether the query is stuck or only processing the
data very slowly. Anyway, instead of describing what's happening, could
you collect some data using vmstat/iostat and post it here? Something like

   vmstat 1

and

   iostat -x -k 1

collected while executing the query. Give us ~15-30 seconds of data for
each, depending of how much it fluctuates. Another option is to use
'perf' to monitor the backend executing your query. Something like

   perf record -g -a -p PID

for a reasonable time, and then 'perf report' (there's like a zillion of
options available for perf, but this should give you some idea where
most of the time is spent).

If you have strong stomach, you might even use strace ...

> 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

OK. But that's only the HW+OS. What about the basic database parameters
that you have mentioned checking? Say, shared buffers, work mem and such
things?

> 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"

Any particular reason why you're running a minor version that's 2 years
old and there were numerous bugs fixed since then? Including a serious
security issue in 9.1.9.

Please, plan an update to 9.1.10 ASAP. It might easily be the case that
you're hitting a bug that was already fixed a long time ago.

> 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.

IMHO that's pretty important fact. If your monitoring or (at least) logs
can't give you an answer, then you should improve that. At least start
logging slow queries and deploy at least some very basic monitoring
(e.g. Munin is quite simple and the PostgreSQL plugin, among other
things, collects data about replication conflicts, which might be the
culprit here).

> Hope this helps. Please let me know if you need any other details.


regards
Tomas