Re: BUG #4801: Performance failure: 600 MB written to each WAL log

Dear Simon,

On Fri, May 08, 2009 at 07:15:19PM +0100, Simon Riggs wrote:
! > Server becomes very slow while tremendous amounts of data are written
! > to WAL logs - disk activity log shows 600 MB of effective write
! > requests before each 16 MB WAL log is archived and recycled.
!
! VACUUM FULL does many things, most of them slowly. It re-writes normal
! database blocks setting hint bits, which are not WAL logged. This may
! explain the figures you have.

You did not get me fully right. The 600 MB are written ONTO EACH
16 MB WAL LOGFILE before it gets switched. And that is a lot more
than superfluous flushes should account for.

And since this does not happen always, only after the server falls
into this strange mode of operation it will stay there - and after
restarting, the vacuum process will practically do the same things
again and may work correctly then - I consider this a remarkable
effect, because such things usually tend to point to some race
condition somewhere.

! 8.2 is also a fairly poor performer with VACUUM and will cause many WAL
! unnecessary flushes per WAL file.

Yes, I see that too.

! Not sure why you are using an old
! release of PostgreSQL on a new BSD version,

Hm. Because it works, and it is stable and reliable? I am using this
database system more or less since it was called Ingres, and I think
it has already improved a lot. ;)

The only reason for upgrading the BSD was to get ZFS, and get rid of the
full_block_writes. Up to now I was practically drowning in WAL logs;
now they have reduced by factor 8, and I am very happy with that.
(And for a backup system one MUST archive the WAL logs, too. It
doesnt make much sense to do offline backups, and not have current offline
backups of the database needed to find something on these backups...)

I am not very fond of major upgrades. Due to the heap of
various functionalities that I have accumulated over time, each
major upgrade will bring some problems, incompatibilities and
features, and then the first thing to recognize is always: something
that used to work does no longer or different. And then I have to
dig into that respective construct and figure out how it needs to be
adjusted. For instance, the interface between postgresQL and Ruby-
on-Rails Rel. 1.2 is still for postgres-7 - it works with 8.2, but
there is an issue with escapes in strings ("standard_conforming_strings");
so likely I have to go for RoR Rel. 2.x - which means work thru
my RoR apps and fixup things that are deprecated. Etc. etc., You get
the idea.

So, usually I try to upgrade at least two major levels at once.

! but if you upgrade
! PostgreSQL and use VACUUM instead you will see improvement.

I do daily VACUUM, anyway. But out of good habit I also do a monthly
VACUUM FULL. I like self-cleaning systems (You should see the mess in
my rooms ;))

So, well, on the bottomline I read from Your message: You guys have
redesigned the VACUUM process in 8.3, and therefore nobody is really
eager to dig into the old stuff and search for strange problems
there. That's an argument, I can understand.

best regards,
Peter