Re: Quad Opteron stuck in the mud

On Jul 14, 2005, at 12:12 AM, Greg Stark wrote:

> Dan Harris <fbsd@drivefaster.net> 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