Re: raise or lower transaction timeout?

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 |
+------------------------------+---------------------------------------------+