Skip site navigation (1) Skip section navigation (2)

Re: [PATCHES] Automatic free space map filling

From: ITAGAKI Takahiro <itagaki(dot)takahiro(at)lab(dot)ntt(dot)co(dot)jp>
To: Simon Riggs <simon(at)2ndquadrant(dot)com>
Cc: pgsql-patches(at)postgresql(dot)org, pgsql-hackers(at)postgresql(dot)org
Subject: Re: [PATCHES] Automatic free space map filling
Date: 2006-03-13 08:38:01
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackerspgsql-patches
Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:

> > "Zeugswetter Andreas DCP SD" <ZeugswetterA(at)spardat(dot)at> wrote:
> > > Ok, we cannot reuse a dead tuple. Maybe we can reuse the space of a dead
> > > tuple by reducing the tuple to it's header info.
> > 
> > Attached patch realizes the concept of his idea. The dead tuples will be
> > reduced to their headers are done by bgwriter.
> I'm interested in this patch but you need to say more about it. I get
> the general idea but it would be useful if you could give a full
> description of what this patch is trying to do and why.

OK, I try to explain the patch. Excuse me for a long writing.

* Purpose
  The basic idea is just "reducing the dead tuple to it's header info",
suggested by Andreas. This is a lightweight per-page sweeping to reduce
the consumption of free space map and the necessity of VACUUM; i.e,
normal VACUUM is still needed occasionally.

  I think it is useful on heavy-update workloads. It showed 5-10% of
performance improvement on DBT-2 after 9 hours running *without* vacuum.
I don't know whether it is still effective with well-scheduled vacuum.

* Why does bgwriter do vacuum?
  Sweeping has cost, so non-backend process should do. Also, the page worth
vacuum are almost always dirty, because tuples on the page are just updated
or deleted. Bgwriter treats dirty pages, so I think it is a good place for

* Locking
  We must take super-exclusive-lock of the pages before vacuum. In the patch,
bgwriter tries to take exclusive-lock before it writes a page, and does
vacuum only if the lock is super-exclusive. Otherwise, it gives up and
writes the pages normally. This is an optimistic way, but I assume the
possibility is high because the most pages written by bgwriter are least
recently used (LRU).

* Keep the headers
  We cannot remove dead tuples completely in per-page sweep, because
references to the tuples from indexes still remains. We might keep only
line pointers (4 bytes), but it might lead line-pointer-bloat problems,
so the headers (4+32 byte) should be left.

* Other twists and GUC variables in the patch
- Bgwriter cannot access the catalogs, so I added BM_RELATION hint bit
  to BufferDesc. Only relation pages will be swept. This is enabled by
  GUC variable 'bgvacuum_relation'.
- I changed bgwriter_lru_maxpages to be adjusted automatically. Backends
  won't do vacuum not to disturb their processing, so bgwriter should write
  most of dirty pages. ('bgvacuum_autotune')
- After sweepping, the page will be added to free space map. I made a simple
  replacement algorithm of free space map, that replaces the page with least
  spaces near the added one. ('bgvacuum_fsm')

* Issues
- If WAL is produced by sweeping a page, writing the page should be pended
  for a while, because flushing the WAL is needed before writing the page.
- Bgwriter writes pages in 4 contexts, background-writes for LRU, ALL,
  checkpoint and shutdown. In current patch, pages are swept in 3 contexts
  except shutdown, but it may be better to do only on LRU.

* Related discussions
- Real-Time Vacuum Possibility (Rod Taylor)
    | have the bgwriter take a look at the pages it has, and see if it can do
    | any vacuum work based on pages it is about to send to disk
- Pre-allocated free space for row updating (like PCTFREE) (Satoshi Nagayasu)
    | light-weight repairing on a single page is needed to maintain free space
- Dead Space Map (Heikki Linnakangas)
    | vacuuming pages one by one as they're written by bgwriter

Thank you for reading till the last.
I'd like to hear your comments.

ITAGAKI Takahiro
NTT Cyber Space Laboratories

In response to


pgsql-hackers by date

Next:From: Magnus HaganderDate: 2006-03-13 08:46:13
Subject: Re: [PERFORM] Hanging queries on dual CPU windows
Previous:From: Qingqing ZhouDate: 2006-03-13 05:38:58
Subject: Re: [PERFORM] Hanging queries on dual CPU windows

pgsql-patches by date

Next:From: Sergey E. KoposovDate: 2006-03-13 08:47:32
Subject: Re: fix of some issues with multi-line query editing
Previous:From: Michael GlaesemannDate: 2006-03-13 07:42:59
Subject: Re: Proposed patch for error locations

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group