X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id E8C3C9FA380 for ; Sun, 28 May 2006 06:43:40 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 11490-10 for ; Sun, 28 May 2006 06:43:31 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mail.clickspace.com (router2.clickspace.com [65.110.166.227]) by postgresql.org (Postfix) with ESMTP id 072C39FA317 for ; Sun, 28 May 2006 06:43:30 -0300 (ADT) Received: from [10.0.1.4] ([68.147.204.179]) (authenticated user brendan@clickspace.com) by mail.clickspace.com (using TLSv1/SSLv3 with cipher RC4-SHA (128 bits)) for pgsql-performance@postgresql.org; Sun, 28 May 2006 03:43:29 -0600 Mime-Version: 1.0 (Apple Message framework v750) In-Reply-To: <961F6F02-4B00-494C-B071-6DF76A305C14@clickspace.com> References: <961F6F02-4B00-494C-B071-6DF76A305C14@clickspace.com> Content-Type: multipart/alternative; boundary=Apple-Mail-4-850995727 Message-Id: <21B15664-F4EE-462C-88B4-EEEB2B4A979F@clickspace.com> From: Brendan Duddridge Subject: Re: App very unresponsive while performing simple update Date: Sun, 28 May 2006 03:43:23 -0600 To: PostgreSQL Performance X-Mailer: Apple Mail (2.750) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200605/503 X-Sequence-Number: 19290 --Apple-Mail-4-850995727 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed 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 > --Apple-Mail-4-850995727 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=ISO-8859-1 Further to my issue, the update = never did finish. I received the following message in psql:

ssprod=3D# update product = set is_hungry =3D 'true'=A0 where date_modified > current_date - = 10;
ERROR:=A0 deadlock detected
DETAIL:=A0 Process = 18778 waits for ShareLock on transaction 711698780;=A0=A0blocked by = process 15784.
Process 15784 waits for ShareLock on = transaction 711697098;=A0blocked 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=A0| CTO | 403-277-5591 x24 | =A0brendan@clickspace.com =

ClickSpace = Interactive Inc.
Suite L100, 239 - 10th Ave. SE
Calgary, AB = =A0T2G 0V9

http://www.clickspace.com=A0
=

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 =3D 'true'=A0 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=A0 = 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=A0| CTO | 403-277-5591 x24 | =A0brendan@clickspace.com =

ClickSpace = Interactive Inc.
Suite L100, 239 - 10th Ave. SE
Calgary, AB = =A0T2G 0V9

http://www.clickspace.com=A0
=


= --Apple-Mail-4-850995727--