Using statement_timeout as a performance tool?

From: Mark Stosberg <mark(at)summersault(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Using statement_timeout as a performance tool?
Date: 2007-02-01 14:10:09
Message-ID: epssco$jes$1@sea.gmane.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance


Hello,

I'm working on setting up replication with Slony, and will soon have a
slave that a lot of SELECT traffic will be sent to (over 500k/day).

The primary query we need to run is somewhat complex, but seems to
complete on average in well under a second.

However, every so often (less in 1 in 10,000 queries) we'll see the
query take 2 or 3 minutes.

It's not clear why this is happening-- perhaps there is something else
going on that is affecting this query.

I'm considering the use of "statement_timeout" to limit the time of
this particular query, to suppress the rare "run away", and avoid tying
up the processor for that additional time.

I think it may be better to give up, quit spending cycles on it right
then, and return an "oops, try again in a few minutes" message instead.
>From the data we have, seems like it has a strong chance of working again.

Is anyone else using "statement_timeout" as part of an overall
performance plan?

Mark

Browse pgsql-performance by date

  From Date Subject
Next Message Michael Artz 2007-02-01 16:42:03 Subselect query enhancement
Previous Message Tomas Vondra 2007-02-01 01:32:20 Re: Slow update