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 17:43:27 -0600
Message-ID: <47616F3F.EE98.0025.0@wicourts.gov> (raw)
In-Reply-To: <1197582010.4255.1912.camel@ebony.site>
References: <4760FF69.EE98.0025.0@wicourts.gov>
<1197562283.4255.1829.camel@ebony.site>
<47611B9D.EE98.0025.0@wicourts.gov>
<1197569564.4255.1855.camel@ebony.site>
<47614D86.EE98.0025.0@wicourts.gov>
<1197582010.4255.1912.camel@ebony.site>
>>> On Thu, Dec 13, 2007 at 3:40 PM, in message
<camel@ebony.site/">1197582010.4255.1912.camel@ebony.site>, Simon Riggs <simon@2ndquadrant.com>
wrote:
> On Thu, 2007-12-13 at 15:19 -0600, Kevin Grittner wrote:
>> >>> On Thu, Dec 13, 2007 at 12:12 PM, in message
>> <camel@ebony.site/">1197569564.4255.1855.camel@ebony.site>, Simon Riggs <simon@2ndquadrant.com>
>> wrote:
>> > On Thu, 2007-12-13 at 11:46 -0600, Kevin Grittner wrote:
>> >> If the hint bit changes are written to the WAL ...
>> >
>> > They're not.
>>
>> So one would expect a write-intensive initial vacuum after a
>> PITR-style recovery?
>
> Very perceptive. I was just thinking about that myself. An interesting
> issue when running with full_page_writes off.
>
>> What impact would lack of the hint bits have until a vacuum?
>
> Vacuum isn't important here. Its the first idiot to read the data that
> gets hit.
OK, I want to understand this well enough to recognize it when I
see it. (As always, I appreciate the helpful answers here.)
Assuming no data is toasted, after a bulk INSERT or COPY into the
database, a subsequent SELECT COUNT(*) would figure out the correct
hint bits and rewrite all rows during execution of the SELECT
statement?
The same is true following a PITR-style recovery?
Toasted data would not be rewritten unless accessed (whether that
be for selection criteria, sort order, results, or whatever)?
A database VACUUM is going to run into every page not previously
accessed and make all hint bits correct?
Would a VACUUM FREEZE of a bulk-loaded table do one write for both
the hint bits and the transaction ID? (I know that hackers
generally prefer that people leave the transaction IDs unfrozen
for a long time to aid in debugging problems, but that seems less
useful in a large table which has just been bulk-loaded, true?)
-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: <47616F3F.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