On Jul 14, 2005, at 12:12 AM, Greg Stark wrote:
Dan Harris <[EMAIL PROTECTED]> writes:
I keep the entire database vacuumed regularly.
How often is "regularly"?
Well, once every day, but there aren't a ton of inserts or updates
going on a daily basis. Maybe 1,000 total inserts?
Also, if you've done occasional massive batch updates like you
describe here
you may need a VACUUM FULL or alternatively a CLUSTER command to
compact the
table -- vacuum identifies the free space but if you've doubled the
size of
your table with a large update that's a lot more free space than
you want
hanging around waiting to be used.
I have a feeling I'm going to need to do a cluster soon. I have done
several mass deletes and reloads on it.
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.
...
update eventactivity set ftindex = false where ftindex = true;
( added the
where clause because I don't want to alter where ftindex is null )
It's definitely worthwhile doing an "EXPLAIN UPDATE..." to see if
this even
used the index. It sounds like it did a sequential scan.
I tried that, and indeed it was using an index, although after
reading Simon's post, I realize that was kind of dumb to have an
index on a bool. I have since removed it.
Sequential scans during updates are especially painful. If there
isn't free
space lying around in the page where the updated record lies then
another page
has to be used or a new page added. If you're doing a massive
update you can
exhaust the free space available making the update have to go back
and forth
between the page being read and the end of the table where pages
are being
written.
This is great info, thanks.
#####
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
[I assume you ran "vmstat 10" or some other interval and then
waited for at
least the second line? The first line outputted from vmstat is mostly
meaningless]
Yeah, this was at least 10 or so down the list ( the last one before
ctrl-c )
Um. That's a pretty meager i/o rate. Just over 2MB/s. The cpu is
76% idle
which sounds fine but that could be one processor pegged at 100%
while the
others are idle. If this query is the only one running on the
system then it
would behave just like that.
Well, none of my processors had ever reached 100% until I changed to
ext2 today ( read below for more info )
Is it possible you have some foreign keys referencing these records
that
you're updating? In which case every record being updated might be
causing a
full table scan on another table (or multiple other tables). If
those tables
are entirely in cache then it could cause these high cpu low i/o
symptoms.
No foreign keys or triggers.
Ok, so I remounted this drive as ext2 shortly before sending my first
email today. It wasn't enough time for me to notice the ABSOLUTELY
HUGE difference in performance change. Ext3 must really be crappy
for postgres, or at least is on this box. Now that it's ext2, this
thing is flying like never before. My CPU utilization has
skyrocketed, telling me that the disk IO was constraining it immensely.
I always knew that it might be a little faster, but the box feels
like it can "breathe" again and things that used to be IO intensive
and run for an hour or more are now running in < 5 minutes. I'm a
little worried about not having a journalized file system, but that
performance difference will keep me from switching back ( at least to
ext3! ). Maybe someday I will try XFS.
I would be surprised if everyone who ran ext3 had this kind of
problem, maybe it's specific to my kernel, raid controller, I don't
know. But, this is amazing. It's like I have a new server.
Thanks to everyone for their valuable input and a big thanks to all
the dedicated pg developers on here who make this possible!
-Dan
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
