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

Reply via email to