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

stats_command_string = true
stats_row_level = true

Thanks in advance,

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

Reply via email to