>>> On Thu, Dec 13, 2007 at 10:11 AM, in message
<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



---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

On Thu, 2007-12-13 at 11:46 -0600, Kevin Grittner wrote:
> >>> On Thu, Dec 13, 2007 at 10:11 AM, in message
> <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.

They're not.

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> Simon Riggs wrote:
>> We might be able to have bgwriter set hint bits on dirty blocks,

> I don't think that works, because the bgwriter has no access to the
> catalogs, therefore it cannot examine the page contents. To bgwriter,
> pages are opaque.

Another issue is that this'd require bgwriter to access the clog SLRU
area. I seem to remember worrying that that could lead to low-level
deadlocks, though I cannot recall the exact case at the moment.
Even without that, it would increase contention for SLRU, which we
probably don't want.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

On Thu, 2007-12-13 at 13:52 -0500, Tom Lane wrote:
> Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> > Simon Riggs wrote:
> >> We might be able to have bgwriter set hint bits on dirty blocks,
>
> > I don't think that works, because the bgwriter has no access to the
> > catalogs, therefore it cannot examine the page contents. To bgwriter,
> > pages are opaque.
>
> Another issue is that this'd require bgwriter to access the clog SLRU
> area. I seem to remember worrying that that could lead to low-level
> deadlocks, though I cannot recall the exact case at the moment.
> Even without that, it would increase contention for SLRU, which we
> probably don't want.

I was trying to highlight the problems, not advocate that as an
approach, sorry if I wasn't clear enough. Even if you solved the
problems both of you have mentioned I don't think the dynamic behaviour
will be useful enough to merit the effort of trying. I'm definitely not
going to be spending any time on this. Fish are frying elsewhere.

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

>>> On Thu, Dec 13, 2007 at 12:12 PM, in message
<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?

What impact would lack of the hint bits have until a vacuum?

-Kevin




---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

On Thu, 2007-12-13 at 15:19 -0600, Kevin Grittner wrote:
> >>> On Thu, Dec 13, 2007 at 12:12 PM, in message
> <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.

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com


---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

>>> On Thu, Dec 13, 2007 at 3:40 PM, in message
<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
>> <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



---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Simon Riggs wrote:
> On Thu, 2007-12-13 at 15:19 -0600, Kevin Grittner wrote:
>
>> 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.
>
>
Given vacuum must then touch every page, is there a win in only setting
hint bits on pages where vacuum has to do some other work on the page?
As vacuum is causing significant IO load for data that may not be
accessed for some time.

The question becomes what is the impact of not setting hint bits? Is it
better or worse than the IO caused by vacuum?

Regards

Russell Smith

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Russell Smith <mr-russ@pws.com.au> writes:
> Given vacuum must then touch every page, is there a win in only setting
> hint bits on pages where vacuum has to do some other work on the page?
> As vacuum is causing significant IO load for data that may not be
> accessed for some time.

Well, if vacuum doesn't do it then some other poor sod will have to.

My feeling is that vacuum's purpose in life is to offload maintenance
cycles from foreground queries, so we should be happy to have it setting
all the hint bits. If Kevin doesn't like the resultant I/O load then he
should use the vacuum_cost_delay parameters to dial down vacuum speed.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

>>> On Fri, Dec 14, 2007 at 1:42 AM, in message <29185.1197618162@sss.pgh.pa.us>,
Tom Lane <tgl@sss.pgh.pa.us> wrote:

> My feeling is that vacuum's purpose in life is to offload maintenance
> cycles from foreground queries, so we should be happy to have it setting
> all the hint bits.

Absolutely.

> If Kevin doesn't like the resultant I/O load then he
> should use the vacuum_cost_delay parameters to dial down vacuum speed.

It's not that I don't like it -- I'm often called upon to diagnose
issues, and understanding the dynamics of things like this helps me
interpret what I'm seeing. No complaint here.

-Kevin




---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq