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