Re: [PERFORM] raise or lower transaction timeout?

[Paul Thomas]

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

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [PERFORM] raise or lower transaction timeout?

[Matthew Hixson]

On Wednesday, July 2, 2003, at 07:04 PM, Ang Chin Han wrote:

Matthew Hixson wrote:

I don't know what that is.  I don't have an iostat utility on the 
machine.  This is a Debian Linux machine.  Is there a package with 
that utility in it?
apt-get install sysstat

apt-cache search iostat can be used to search for it, or use 
http://www.debian.org/distrib/packages
Thank you.  iostat shows:

Linux 2.4.20 (datweb10) 07/02/03

avg-cpu:  %user   %nice%sys   %idle
  10.600.002.70   86.71
Device:tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
dev8-07.0132.59   230.79   33242314  235421336
Guess I need to read up on what that's telling me.
  Thanks again,
-M@
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
 joining column's datatypes do not match

Re: [PERFORM] raise or lower transaction timeout?

[Ang Chin Han]

Matthew Hixson wrote:

I don't know what that is.  I don't have an iostat utility on the 
machine.  This is a Debian Linux machine.  Is there a package with that 
utility in it?
apt-get install sysstat

apt-cache search iostat can be used to search for it, or use 
http://www.debian.org/distrib/packages

--
Linux homer 2.4.18-14 #1 Wed Sep 4 13:35:50 EDT 2002 i686 i686 i386 
GNU/Linux
  9:30am  up 189 days, 35 min,  5 users,  load average: 5.01, 5.03, 5.00


pgp0.pgp
Description: PGP signature

Re: [PERFORM] raise or lower transaction timeout?

[Rod Taylor]

> > iostat reports heavy disk usage?

Do you know of another way to report disk activity?

> I don't know what that is.  I don't have an iostat utility on the 
> machine.  This is a Debian Linux machine.  Is there a package with that 
> utility in it?

I don't know.  It must be in a package somewhere.  Based on the amount
of swap that is used, you probably aren't.

>   Many thousand 'selects' per day though.

Ok.. Simple or complex?  Lots of data involved in the typical query or
very little?

> > Please attach your postgresql.conf file.

Try setting (postgresql.conf):

If dealing with large amounts of data in single queries, this will help:

shared_buffers = 2000
sort_mem = 8192


I'm afraid we'll need to see the actual queries (EXPLAIN ANALYZE output)
to help, since you have a primarily read based system.  Care to send in
the most common cases?


signature.asc
Description: This is a digitally signed message part

Re: [PERFORM] raise or lower transaction timeout?

[Matthew Hixson]

On Wednesday, July 2, 2003, at 01:10 PM, Rod Taylor wrote:

   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.
Tuning will often double (if not more) the performance.

What are the specs of the box?  Memory, CPU, Disk.
Dual PIII 1.13Ghz with 1GB of RAM and 18GB SCSI drives.

Are any of these
short?  Are you swapping? Pegged CPU?
The CPU gets pegged.  Load average goes to 20+.  Here is the memory 
usage from top:

Mem:   1033508K total,   939484K used,94024K free,35148K buffers
Swap:   498004K total,75556K used,   422448K free,   252984K cached
iostat reports heavy disk usage?
I don't know what that is.  I don't have an iostat utility on the 
machine.  This is a Debian Linux machine.  Is there a package with that 
utility in it?

What else is running on it?
Resin, a Java web/application server.  It usually consumes ~300MB.

How often does your data turn over? (Frequency of updates / inserts /
deletes)
Maybe a couple hundred updates/inserts per day.  Not very many deletes. 
 Many thousand 'selects' per day though.

I assume you have vacuumed recently? Please attach output of VACUUM
VERBOSE.
Please attach your postgresql.conf file.
Both are attached.

Thanks,
  -M@

# vacuum verbose;
INFO:  --Relation pg_catalog.pg_description--
INFO:  Pages 12: Changed 0, Empty 0; Tup 1390: Vac 0, Keep 0, UnUsed 1.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  --Relation pg_toast.pg_toast_16416--
INFO:  Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  --Relation pg_catalog.pg_group--
INFO:  Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  --Relation pg_toast.pg_toast_1261--
INFO:  Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  --Relation pg_catalog.pg_proc--
INFO:  Pages 58: Changed 0, Empty 0; Tup 1493: Vac 0, Keep 0, UnUsed 164.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  --Relation pg_toast.pg_toast_1255--
INFO:  Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  --Relation pg_catalog.pg_rewrite--
INFO:  Pages 4: Changed 0, Empty 0; Tup 27: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  --Relation pg_toast.pg_toast_16410--
INFO:  Pages 4: Changed 0, Empty 0; Tup 16: Vac 0, Keep 0, UnUsed 1.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  --Relation pg_catalog.pg_type--
INFO:  Pages 5: Changed 0, Empty 0; Tup 239: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  --Relation pg_catalog.pg_attribute--
INFO:  Pages 30: Changed 0, Empty 0; Tup 1809: Vac 0, Keep 0, UnUsed 1.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  --Relation pg_catalog.pg_class--
INFO:  Pages 6: Changed 0, Empty 0; Tup 265: Vac 0, Keep 0, UnUsed 53.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  --Relation pg_catalog.pg_inherits--
INFO:  Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  --Relation pg_catalog.pg_index--
INFO:  Pages 5: Changed 0, Empty 0; Tup 135: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  --Relation pg_catalog.pg_operator--
INFO:  Pages 13: Changed 0, Empty 0; Tup 643: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  --Relation pg_catalog.pg_opclass--
INFO:  Pages 1: Changed 0, Empty 0; Tup 51: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  --Relation pg_catalog.pg_am--
INFO:  Pages 1: Changed 0, Empty 0; Tup 4: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  --Relation pg_catalog.pg_amop--
INFO:  Pages 1: Changed 0, Empty 0; Tup 180: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  --Relation pg_catalog.pg_amproc--
INFO:  Pages 1: Changed 0, Empty 0; Tup 57: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  --Relation pg_catalog.pg_language--
INFO:  Pages 1: Changed 0, Empty 0; Tup 4: Vac 0, Keep 0, UnUsed 2.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  --Relation pg_catalog.pg_largeobject--
INFO:  Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  --Relation pg_catalog.pg_aggregate--
INFO:  Pages 1: Changed 0, Empty 0; Tup 60: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  --Relation pg_catalog.pg_trigger--
INFO:  Pages 1: Changed 0, Empty 0; Tup 8: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  --Relation pg_catalog.pg_listener--

Re: [PERFORM] raise or lower transaction timeout?

[Rod Taylor]

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

Tuning will often double (if not more) the performance.

What are the specs of the box?  Memory, CPU, Disk.  Are any of these
short?  Are you swapping? Pegged CPU? iostat reports heavy disk usage?

What else is running on it?

How often does your data turn over? (Frequency of updates / inserts /
deletes)

I assume you have vacuumed recently? Please attach output of VACUUM
VERBOSE.

Please attach your postgresql.conf file.

Please include the header lines from 'top' output (memory cache / buffer
lines in particular).

-- 
Rod Taylor 

Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
PGP Key: http://www.rbt.ca/rbtpub.asc


signature.asc
Description: This is a digitally signed message part