Обсуждение: ERROR: canceling statement due to statement timeout

Hi,

I've had a website up for a couple of months and it's starting to get
these db timeouts as traffic has increased to say 1k pageviews a day.

It uses django with the gis stuff to find distances from user to
object and it's hosted on webfaction. I've asked webfaction about it
and they say no one else is having postgres problems on my server.

My question is which (anonymizable) infomation can I post here so that
you helpful people can assist with with a diagnosis and hopefully a
fix?

Thanks,
Tim

On 03/07/10 00:22, Tim wrote:
> Hi,
>
> I've had a website up for a couple of months and it's starting to get
> these db timeouts as traffic has increased to say 1k pageviews a day.

Take note of which queries are timing out. You might need to enable some
more logging in your app or the db to find out.

Use EXPLAIN ANALYZE to collect information about the performance of
those queries. You can post that here, along with some information about
your schema.

Monitor pg_catalog.pg_stat_activity for query runtimes and general load.

Turn off the statement_timeout, or at least increase it considerably.

--
Craig Ringer

On Fri, Jul 2, 2010 at 12:22 PM, Tim <tavspam@gmail.com> wrote:
> I've had a website up for a couple of months and it's starting to get
> these db timeouts as traffic has increased to say 1k pageviews a day.
>

Are you using any two-phase commit (ie, prepared transactions?)

We find that if you try to insert data that will conflict with data
pending in a 2PC, you get a statement timeout immediately.

On 6 July 2010 16:36, Vick Khera <vivek@khera.org> wrote:
> On Fri, Jul 2, 2010 at 12:22 PM, Tim <tavspam@gmail.com> wrote:
>> I've had a website up for a couple of months and it's starting to get
>> these db timeouts as traffic has increased to say 1k pageviews a day.
>>
>
> Are you using any two-phase commit (ie, prepared transactions?)
>
> We find that if you try to insert data that will conflict with data
> pending in a 2PC, you get a statement timeout immediately.

I haven't setup anything like that . But geodjango might be doing
things like that in the background.

I'll get some queries together and post them here.