From: Brendan Duddridge <brendan@clickspace.com>
To: PostgreSQL Performance <pgsql-performance@postgresql.org>
Subject: Re: App very unresponsive while performing simple update
Date: Sun, 28 May 2006 03:43:23 -0600
Message-ID: <21B15664-F4EE-462C-88B4-EEEB2B4A979F@clickspace.com> (raw)
In-Reply-To: <961F6F02-4B00-494C-B071-6DF76A305C14@clickspace.com>
References: <961F6F02-4B00-494C-B071-6DF76A305C14@clickspace.com>
Further to my issue, the update never did finish. I received the
following message in psql:
ssprod=# update product set is_hungry = 'true' where date_modified >
current_date - 10;
ERROR: deadlock detected
DETAIL: Process 18778 waits for ShareLock on transaction 711698780;
blocked by process 15784.
Process 15784 waits for ShareLock on transaction 711697098; blocked
by process 18778.
This is the second time I've tried to run this query without success.
Would changing the isolation level to serializable in my psql session
help with this?
Thanks,
____________________________________________________________________
Brendan Duddridge | CTO | 403-277-5591 x24 | brendan@clickspace.com
ClickSpace Interactive Inc.
Suite L100, 239 - 10th Ave. SE
Calgary, AB T2G 0V9
http://www.clickspace.com
On May 28, 2006, at 3:37 AM, Brendan Duddridge wrote:
> Hi,
>
> Is Postgres supposed to be able to handle concurrent requests while
> doing large updates?
>
> This morning I was executing the following simple update statement
> that would affect 220,000 rows in my product table:
>
> update product set is_hungry = 'true' where date_modified >
> current_date - 10;
>
> But the application that accesses the product table for reading
> became very unresponsive while the update was happening.
>
> Is it just a matter of slow I/O? The CPU usage seemed very low
> (less than 5%) and iostat showed less than 1 MB / sec throughput.
>
> I was doing the update in psql.
>
> Are there any settings that I could tweak that would help with this
> sort of thing?
>
> Thanks,
>
> ____________________________________________________________________
> Brendan Duddridge | CTO | 403-277-5591 x24 | brendan@clickspace.com
>
> ClickSpace Interactive Inc.
> Suite L100, 239 - 10th Ave. SE
> Calgary, AB T2G 0V9
>
> http://www.clickspace.com
>
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: brendan@clickspace.com
Subject: Re: App very unresponsive while performing simple update
In-Reply-To: <21B15664-F4EE-462C-88B4-EEEB2B4A979F@clickspace.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