X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 846FAD7F1D for ; Wed, 12 Oct 2005 13:55:43 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 69849-03 for ; Wed, 12 Oct 2005 16:55:40 +0000 (GMT) Received: from mail.ecircle.de (mail.ecircle.de [195.140.186.200]) by svr1.postgresql.org (Postfix) with ESMTP id 68119D7F17 for ; Wed, 12 Oct 2005 13:55:40 -0300 (ADT) Received: from [192.168.1.110] (unknown [192.168.1.110]) by mail.ecircle.de (READY) with ESMTP id 0F64C95429E; Wed, 12 Oct 2005 18:55:40 +0200 (CEST) Subject: Re: Help tuning postgres From: Csaba Nagy To: newz@bearfruit.org Cc: postgres performance list In-Reply-To: References: <1129128315.2995.174.camel@coppola.muc.ecircle.de> Content-Type: text/plain Message-Id: <1129136130.2995.188.camel@coppola.muc.ecircle.de> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) Date: Wed, 12 Oct 2005 18:55:30 +0200 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.012 required=5 tests=[AWL=0.012] X-Spam-Level: X-Archive-Number: 200510/213 X-Sequence-Number: 14981 Ok, that was the first thing I've done, checking out the explain of the query. I don't really need the analyze part, as the plan is going for the index, which is the right decision. The updates are simple one-row updates of one column, qualified by the primary key condition. This part is OK, the query is not taking extremely long, but the problem is that we execute 500 in a transaction, and that takes too long and blocks other activities. Actually I've done an iostat run in the meantime (just learned how to use it), and looks like the disk is 100 saturated. So it clearly is a disk issue in this case. And it turns out the Oracle hardware has an edge of 3 years over what I have for postgres, so that might very well explain the performance difference... Oh well. Next we'll upgrade the postgres hardware, and then I'll come back to report if it's working better... sorry for the noise for now. Cheers, Csaba. BTW, is the config file good enough for the kind of machine I have ? Cause it's the first time I had to make a production configuration and most of the stuff is according to the commented config guid from varlena with some guesswork added... > I'm not the most experience person on this list, but I've got some big > tables I work with. Doing an update on these big tables often involves > a sequential scan which can be quite slow. > > I would suggest posting the explain analyze output for one of your > slow updates. I'll bet it is much more revealing and takes out a lot > of the guesswork. > > -- > Matthew Nuzum > www.bearfruit.org