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
Date: Thu, 13 Dec 2007 11:46:37 -0600
Message-ID: <47611B9D.EE98.0025.0@wicourts.gov> (raw)
In-Reply-To: <1197562283.4255.1829.camel@ebony.site>
References: <4760FF69.EE98.0025.0@wicourts.gov>
<1197562283.4255.1829.camel@ebony.site>
>>> On Thu, Dec 13, 2007 at 10:11 AM, in message
<camel@ebony.site/">1197562283.4255.1829.camel@ebony.site>, Simon Riggs <simon@2ndquadrant.com>
wrote:
> On Thu, 2007-12-13 at 09:46 -0600, Kevin Grittner wrote:
>
>> 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.
>
> Writing hint bits. Annoying isn't it? :-(
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.
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.
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)?
-Kevin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: Kevin.Grittner@wicourts.gov, simon@2ndquadrant.com
Subject: Re: Heavy write activity on first vacuum of fresh TOAST data
In-Reply-To: <47611B9D.EE98.0025.0@wicourts.gov>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox