From: | Dan Harris <fbsd(at)drivefaster(dot)net> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Quad Opteron stuck in the mud |
Date: | 2005-07-13 18:54:35 |
Message-ID: | E5E21B7B-FFE0-4D24-8CDD-62179AEBBB92@drivefaster.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2005-07-13 18:58:52 | Re: cost-based vacuum |
Previous Message | Ian Westmacott | 2005-07-13 18:40:36 | Re: cost-based vacuum |