Message-Id: <47611B9D.EE98.0025.0@wicourts.gov>
Date: Thu, 13 Dec 2007 11:46:37 -0600
From: "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
To: "Simon Riggs" <simon@2ndquadrant.com>
Cc: <pgsql-performance@postgresql.org>
Subject: Re: Heavy write activity on first vacuum of fresh
	TOAST data
References: <4760FF69.EE98.0025.0@wicourts.gov>
	<1197562283.4255.1829.camel@ebony.site>
In-Reply-To: <1197562283.4255.1829.camel@ebony.site>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

>>> On Thu, Dec 13, 2007 at 10:11 AM, in message
<1197562283.4255.1829.camel@ebony.site>, Simon Riggs <simon@2ndquadrant.com=
>
wrote:=20
> On Thu, 2007-12-13 at 09:46 -0600, Kevin Grittner wrote:
> =20
>> The data was inserted through a Java program using a prepared
>> statement with no indexes on the table.  The primary key was then
>> added, and now I've started a vacuum.  The new table wound up being
>> the first big table vacuumed, and I noticed something odd.  Even
>> though there have been no rollbacks, updates, or deletes on this
>> table, the vacuum is writing as much as it is reading while dealing
>> with the TOAST data.
>=20
> Writing hint bits. Annoying isn't it? :-(
=20
Is there anything in the documentation that mentions this pattern
of activity?  Since I started clearing the WAL file tails before
compression, it has surprised me how much WAL file activity there
is from the nightly vacuum.  I had assumed that some part of this
was freezing old tuples, but that didn't seem to exactly match the
pattern of activity.  If the hint bit changes are written to the
WAL, I think this explains it.
=20
Maybe this too arcane for the docs, but I'm not so sure.
Effectively, it means that every new tuple which has much of a
lifespan has to be written at least three times, if I'm
understanding you: once during the database transaction which
creates it, once in the first subsequent vacuum of that table to
flag that it was committed, and again when it reaches the freeze
threshold to prevent transaction number wraparound.
=20
That last one could be sort of a surprise for someone at some
point after, say, restoring from pg_dump, couldn't it?  Would it
make any kind of sense for a person to do the first vacuum after
a bulk load using the FREEZE keyword (or the more recent
equivalent setting)?
=20
-Kevin
=20