X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (developer.postgresql.org [64.117.224.193]) by svr1.postgresql.org (Postfix) with ESMTP id E43CAB43A42 for ; Thu, 3 Jul 2003 15:59:24 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr1.postgresql.org [64.117.224.193]) (amavisd-new, port 10024) with ESMTP id 30887-06 for ; Thu, 3 Jul 2003 12:58:46 -0300 (ADT) Received: from www.postgresql.com (www.postgresql.com [64.117.225.209]) by svr1.postgresql.org (Postfix) with ESMTP id 07A40B43A45 for ; Thu, 3 Jul 2003 12:57:55 -0300 (ADT) Received: from curry.tmsl.demon.co.uk (tmsl-adsl.demon.co.uk [80.177.114.181]) by www.postgresql.com (Postfix) with ESMTP id C9F52CF7558 for ; Thu, 3 Jul 2003 05:48:48 -0300 (ADT) Received: from bacon.tmsl.demon.co.uk (bacon.tmsl.demon.co.uk [192.168.7.102]) by curry.tmsl.demon.co.uk (8.11.6/8.11.6) with ESMTP id h638llR14741; Thu, 3 Jul 2003 09:47:47 +0100 Date: Thu, 3 Jul 2003 09:47:44 +0100 From: Paul Thomas To: Matthew Hixson Cc: "pgsql-performance @ postgresql . org" Subject: Re: raise or lower transaction timeout? Message-ID: <20030703094744.A6212@bacon> References: <60DAC973-ACC8-11D7-9D40-000393669C1A@poindextrose.org> Mime-Version: 1.0 Content-Type: text/plain; format=flowed; charset=ISO-8859-1 In-Reply-To: <60DAC973-ACC8-11D7-9D40-000393669C1A@poindextrose.org>; from hixson@poindextrose.org on Wed, Jul 02, 2003 at 21:04:24 +0100 X-Mailer: Balsa 1.2.3 Lines: 35 X-Archive-Number: 200307/28 X-Sequence-Number: 2321 On 02/07/2003 21:04 Matthew Hixson wrote: > We currently have a public website that is serving customers, or at > least trying to. This machine is underpowered but we are going to be > upgrading soon. In the meantime we need to keep the current site alive. > We are running a Java application server. It is receiving > 'transaction timed out' SQLExceptions from the JDBC driver. I am > wondering if it would be better to raise the transaction timeout or to > lower it. On one hand it seems like raising it might improve things. > It might let the transactions complete, even though it would make the > user experience less enjoyable having to wait longer. On the other hand > I could see raising the transaction timeout just cause there to be more > transactions in process which would thereby degrade performance since > the machine would have even more work to do. Would, in fact, lowering > the transaction timeout at least cause the machine to fail fast and > return either an error or the page in a more timely manner on a per-user > level? I'd like to keep people visiting the site while at the same time > relieving some stress from the machine. > We have also done little to no performance tuning of Postgres' > configuration. We do have indexes on all of the important columns and > we have reindexed. Any pointers would be greatly appreciated. As well as the tuning postgresql advice which others have given, there's another thing you could try: Assuming you're using connection pooling, try reducing the maximum number of connections. This will take some of the stress off the database. -- Paul Thomas +------------------------------+---------------------------------------------+ | Thomas Micro Systems Limited | Software Solutions for the Smaller Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +------------------------------+---------------------------------------------+