Re: Quad Opteron stuck in the mud

Dan Harris <fbsd(at)drivefaster(dot)net> writes:

> Well, once every day, but there aren't a ton of inserts or updates going on a
> daily basis. Maybe 1,000 total inserts?

It's actually deletes and updates that matter. not inserts.

> I have a feeling I'm going to need to do a cluster soon. I have done several
> mass deletes and reloads on it.

CLUSTER effectively does a VACUUM FULL but takes a different approach and
writes out a whole new table, which if there's lots of free space is faster
than moving records around to compact the table.

> 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.

If there are very few records (like well under 10%) with that column equal to
false (or very few equal to true) then it's not necessarily useless. But
probably more useful is a partial index on some other column.

Something like

CREATE INDEX ON pk WHERE flag = false;

> No foreign keys or triggers.

Note that I'm talking about foreign keys in *other* tables that refer to
columns in this table. Every update on this table would have to scan those
other tables looking for records referencing the updated rows.

> 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.

@spock(Fascinating).

I wonder if ext3 might be issuing IDE cache flushes on every fsync (to sync
the journal) whereas ext2 might not be issuing any cache flushes at all.

If the IDE cache is never being flushed then you'll see much better
performance but run the risk of data loss in a power failure or hardware
failure. (But not in the case of an OS crash, or at least no more than
otherwise.)

You could also try using the "-O journal_dev" option to put the ext3 journal
on a separate device.

--
greg