On Jul 14, 2005, at 12:12 AM, Greg Stark wrote:
Well, once every day, but there aren't a ton of inserts or updates
going on a daily basis. Maybe 1,000 total inserts?
Dan Harris <[EMAIL PROTECTED]> writes:
I keep the entire database vacuumed regularly.
How often is "regularly"?
I have a feeling I'm going to need to do a cluster soon. I have done
several mass deletes and reloads on it.
Also, if you've done occasional massive batch updates like you
you may need a VACUUM FULL or alternatively a CLUSTER command to
table -- vacuum identifies the free space but if you've doubled the
your table with a large update that's a lot more free space than
hanging around waiting to be used.
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
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
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
space lying around in the page where the updated record lies then
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
between the page being read and the end of the table where pages
This is great info, thanks.
vmstat output ( as I am waiting for this to finish ):
procs -----------memory---------- ---swap-- -----io---- --system--
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
Yeah, this was at least 10 or so down the list ( the last one before
Well, none of my processors had ever reached 100% until I changed to
ext2 today ( read below for more info )
Um. That's a pretty meager i/o rate. Just over 2MB/s. The cpu is
which sounds fine but that could be one processor pegged at 100%
others are idle. If this query is the only one running on the
system then it
would behave just like that.
Is it possible you have some foreign keys referencing these records
you're updating? In which case every record being updated might be
full table scan on another table (or multiple other tables). If
are entirely in cache then it could cause these high cpu low i/o
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!
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings