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 7D73F9FA5D2 for ; Wed, 31 May 2006 03:23:20 -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 92153-03 for ; Wed, 31 May 2006 03:23:10 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from noel.decibel.org (noel.decibel.org [67.100.216.10]) by postgresql.org (Postfix) with ESMTP id DDEB49F9312 for ; Wed, 31 May 2006 03:23:09 -0300 (ADT) Received: by noel.decibel.org (Postfix, from userid 1001) id 4D99A56431; Wed, 31 May 2006 01:23:08 -0500 (CDT) Received: (hashcash-sendmail, from uid 1001); Wed, 31 May 2006 01:23:07 -0500 Date: Wed, 31 May 2006 01:23:07 -0500 From: "Jim C. Nasby" To: Greg Stark Cc: Brendan Duddridge , Tom Lane , PostgreSQL Performance Subject: Re: App very unresponsive while performing simple update Message-ID: <20060531062307.GG53487@pervasive.com> References: <961F6F02-4B00-494C-B071-6DF76A305C14@clickspace.com> <21B15664-F4EE-462C-88B4-EEEB2B4A979F@clickspace.com> <874pzanvk6.fsf@stark.xeocode.com> <17733.1148832292@sss.pgh.pa.us> <3C756C36-DC66-4D13-B2CE-99CFCE7BF390@clickspace.com> <87hd39n3c4.fsf@stark.xeocode.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <87hd39n3c4.fsf@stark.xeocode.com> X-Operating-System: FreeBSD 6.0-RELEASE-p4 amd64 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.11 X-Hashcash: 1:20:060531:gsstark@mit.edu::/oFP40Jq4p3yO3oF:000Tu4 X-Hashcash: 1:20:060531:brendan@clickspace.com::tT2Hzc8pFZoyZyD0:000000000000000 0000000000000000000000004MDr X-Hashcash: 1:20:060531:tgl@sss.pgh.pa.us::9laWk8PUZN9uX6+9:00000000000000000000 0000000000000000000000002iKX X-Hashcash: 1:20:060531:pgsql-performance@postgresql.org::8t4bW82CtNRjArdx:00000 0000000000000000000000000M+8 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200605/562 X-Sequence-Number: 19349 On Sun, May 28, 2006 at 07:20:59PM -0400, Greg Stark wrote: > Brendan Duddridge writes: > > > We do have foreign keys on other tables that reference the product table. > > Also, there will be updates going on at the same time as this update. When > > anyone clicks on a product details link, we issue an update statement to > > increment the click_count on the product. e.g. update product set click_count > > = click_count + 1; > > You should realize this will produce a lot of garbage records and mean you'll > have to be running vacuum very frequently. You might consider instead of > updating the main table inserting into a separate clickstream table. That > trades off not getting instantaneous live totals with isolating the > maintenance headache in a single place. That table will grow large but you can > prune it at your leisure without impacting query performance on your main > tables. Actually, you can still get instant results, you just have to hit two tables to do it. > More likely you were blocking on some lock. Until that other query holding > that lock tries to commit Postgres won't actually detect a deadlock, it'll > just sit waiting until the lock becomes available. Wow, are you sure that's how it works? I would think it would be able to detect deadlocks as soon as both processes are waiting on each other's locks. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461