Quad Opteron stuck in the mud - Mailing list pgsql-performance

Gurus,

A table in one of my databases has just crossed the 30 million row
mark and has begun to feel very sluggish for just about anything I do
with it.  I keep the entire database vacuumed regularly.  And, as
long as I'm not doing a sequential scan, things seem reasonably quick
most of the time.  I'm now thinking that my problem is IO because
anything that involves heavy ( like a seq scan ) IO seems to slow to
a crawl.  Even if I am using indexed fields to grab a few thousand
rows, then going to sequential scans it gets very very slow.

I have also had the occurrence where queries will not finish for days
( I eventually have to kill them ).  I was hoping to provide an
explain analyze for them, but if they never finish... even the
explain never finishes when I try that.

For example, as I'm writing this, I am running an UPDATE statement
that will affect a small part of the table, and is querying on an
indexed boolean field.

I have been waiting for over an hour and a half as I write this and
it still hasn't finished.  I'm thinking "I bet Tom, Simon or Josh
wouldn't put up with this kind of wait time..", so I thought I would
see if anyone here had some pointers.  Maybe I have a really stupid
setting in my conf file that is causing this.  I really can't believe
I am at the limits of this hardware, however.


The query:
update eventactivity set ftindex = false where ftindex = true;
( added the where clause because I don't want to alter where ftindex
is null )



The table:
   Column    |            Type             | Modifiers
-------------+-----------------------------+-----------
entrydate   | timestamp without time zone |
incidentid  | character varying(40)       |
statustype  | character varying(20)       |
unitid      | character varying(20)       |
recordtext  | character varying(255)      |
recordtext2 | character varying(255)      |
insertdate  | timestamp without time zone |
ftindex     | boolean                     |
Indexes: eventactivity1 btree (incidentid),
          eventactivity_entrydate_idx btree (entrydate),
          eventactivity_ftindex_idx btree (ftindex),
          eventactivity_oid_idx btree (oid)




The hardware:

4 x 2.2GHz Opterons
12 GB of RAM
4x10k 73GB Ultra320 SCSI drives in RAID 0+1
1GB hardware cache memory on the RAID controller

The OS:
Fedora, kernel 2.6.6-1.435.2.3smp ( redhat stock kernel )
filesystem is mounted as ext2

#####

vmstat output ( as I am waiting for this to finish ):
procs -----------memory---------- ---swap-- -----io---- --system--
----cpu----
r  b   swpd   free   buff  cache   si   so    bi    bo   in    cs us
sy id wa
0  1   5436 2823908  26140 9183704    0    1  2211   540  694   336
9  2 76 13

#####

iostat output ( as I am waiting for this to finish ):
avg-cpu:  %user   %nice    %sys %iowait   %idle
            9.19    0.00    2.19   13.08   75.53

Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
cciss/c0d0      329.26     17686.03      4317.57  161788630   39496378


#####
This is a dedicated postgresql server, so maybe some of these
settings are more liberal than they should be?

relevant ( I hope ) postgresql.conf options are:

shared_buffers = 50000
effective_cache_size = 1348000
random_page_cost = 3
work_mem = 512000
max_fsm_pages = 80000
log_min_duration_statement = 60000
fsync = true ( not sure if I'm daring enough to run without this )
wal_buffers = 1000
checkpoint_segments = 64
checkpoint_timeout = 3000


#---- FOR PG_AUTOVACUUM --#
stats_command_string = true
stats_row_level = true

Thanks in advance,
Dan