SEO

Pavan Deolasee wrote:
> Until patch version 14, defragmentation and pruning were separate
> operations, though we used to invoke both at the same time. The only
> difference was that pruning can work with a simple exclusive lock
> whereas defragmentation needs vacuum-strength lock. Tom argued
> that its not worth the complexity, so I changed that and clubbed
> both the operations together. What it means: pruning also now needs
> vacuum-strength lock and is always followed by defragmentation.

Interesting. See my previous post to Heikki stating we might need to
change that based on Heikki test results.

> So as the patch stands (version 15), we call heap_page_prune_defrag()
> inside heap_release_fetch() and heapgetpage(), apart from the vacuum
> loop. It checks for PageIsPrunable() before proceeding. PageIsPrunable
> is set when a tuple is updated/deleted. So for read-mostly workloads
> heap_page_prune_defrag will mostly be no-op.
>
> If PageIsPrunable is set, cleanup lock is available (exclusive lock is tried
> conditionally, so we don't wait if there is contention) and we are running
> low on free space (right now if free space is less than 1.2 times the
> average
> tuple size or less than BLCKSZ/8), the entire page is pruned and
> fragmentation
> is repaired.

Looking at the patch I see:

+ /*
+ * Mark the page as clear of prunable tuples. If we find a tuple which
+ * may become prunable, we shall set the hint again.
+ */
+ PageClearPrunable(page);

I like the idea of the page hint bit, but my question is if there is a
long-running transaction, isn't each SELECT going to try to defragment a
page over and over again because there is still something prunable on
the page?

I think we need to find out how hard it would be to try the
defragmentation only on INSERT or UPDATE. The hint bit might still be
useful.

> We also prune-defrag is vacuum lazy and vacuum full. But I assume we
> are not worried about these maintenance activities.

Right.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

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

http://archives.postgresql.org

"Bruce Momjian" <bruce@momjian.us> writes:

> Looking at the patch I see:
>
> + /*
> + * Mark the page as clear of prunable tuples. If we find a tuple which
> + * may become prunable, we shall set the hint again.
> + */
> + PageClearPrunable(page);
>
> I like the idea of the page hint bit, but my question is if there is a
> long-running transaction, isn't each SELECT going to try to defragment a
> page over and over again because there is still something prunable on
> the page?

Well it'll try to prune the chains over and over again. If it doesn't find
anything it won't defragment, but yes.

I think we could tackle that by storing on the page the oldest xmax which
would allow us to prune a tuple. Then you could compare RecentGlobalXmin
against that and not bother looking at any other chains if it hasn't been
passed yet.

It would be hard to do that with single-chain pruning though, once you the
limiting tuple you would then wouldn't know what the next limiting xmax is
until the next time you do a full-page prune. Still that gets us down to at
most two full-page prunes per update instead of a potentially unbounded number
of prunes.

This seems like a further optimization to think about after we have a place to
trigger the pruning where it'll do the most good.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.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

Bruce Momjian wrote:
> Looking at the patch I see:
>
> + /*
> + * Mark the page as clear of prunable tuples. If we find a tuple which
> + * may become prunable, we shall set the hint again.
> + */
> + PageClearPrunable(page);
>
> I like the idea of the page hint bit, but my question is if there is a
> long-running transaction, isn't each SELECT going to try to defragment a
> page over and over again because there is still something prunable on
> the page?

Maybe that risk could be lowered if instead of a flag, we stored the
minimal global xmin needed to prune at least one tuple.

greetings, Florian Pflug



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

On 9/10/07, Florian Pflug <fgp.phlo.org@gmail.com> wrote:
>
>
> Maybe that risk could be lowered if instead of a flag, we stored the
> minimal global xmin needed to prune at least one tuple.
>
>

I like the idea. The question is whether the chances of a Prunable
page being looked up again and again in presence of a long
running transaction are high enough to justify adding 4 bytes
to page header.

Thanks,
Pavan


--
Pavan Deolasee
EnterpriseDB http://www.enterprisedb.com

Gregory Stark wrote:
> "Bruce Momjian" <bruce@momjian.us> writes:
>
> > Looking at the patch I see:
> >
> > + /*
> > + * Mark the page as clear of prunable tuples. If we find a tuple which
> > + * may become prunable, we shall set the hint again.
> > + */
> > + PageClearPrunable(page);
> >
> > I like the idea of the page hint bit, but my question is if there is a
> > long-running transaction, isn't each SELECT going to try to defragment a
> > page over and over again because there is still something prunable on
> > the page?
>
> Well it'll try to prune the chains over and over again. If it doesn't find
> anything it won't defragment, but yes.
>
> I think we could tackle that by storing on the page the oldest xmax which
> would allow us to prune a tuple. Then you could compare RecentGlobalXmin
> against that and not bother looking at any other chains if it hasn't been
> passed yet.

Yea, that might work if we have space on the page.

> It would be hard to do that with single-chain pruning though, once you the
> limiting tuple you would then wouldn't know what the next limiting xmax is
> until the next time you do a full-page prune. Still that gets us down to at
> most two full-page prunes per update instead of a potentially unbounded number
> of prunes.
>
> This seems like a further optimization to think about after we have a place to
> trigger the pruning where it'll do the most good.

I was thinking if we could only try defragementing when we are doing an
INSERT or UPDATE, then the defragment would either free enough space so
we could stay on the page or the new row would go on another page and we
would probably not return to that page again anytime soon.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings