Re: WAL internals

On Fri, Jul 07, 2006 at 11:49:13AM +0200, Fabrice Franquenk wrote:
> 2. If the result of the transaction is within the shared buffers, we get
> our result
> instantly. Else some searching is done within the database datafiles to
> get the result
> which is copied to the shared buffers memory zone. The transaction is
> stocked in a WAL
> buffer.
> Now, when the result is copied to the shared buffer, if the transaction
> was an update or a delete
> the line is flagged to be updated/deleted in the datafiles.

The shared buffers are merely images of what's on disk. Whenever a
block is read from disk it goes into a shared buffer. This applies to
index pages, data pages, anything.

Tuples are inserted into the pages in the buffer, and also appended to
the transaction log. The page is marked as dirty but not yet written
out.

> transactions go on and on this way.

When a transaction commits, the WAL is synced.

> At some points, the WAL buffers are written in the checkpoint segments.
> I don't know when,
> if you could just precise this point.

Straight away, why delay?

> 3. Then periodically, there are checkpoints, those will make the changes
> into the datafiles from
> the shared buffers (meaning shared buffers are flushed into the datafiles).
> The last written record in the datafiles is flagged into the checkpoint
> segments
> that way REDOs are possible.

No, all checkpoints do is make sure all pages in the shared buffers
match what's on disk. When that's the case, you don't need to keep the
WAL anymore. REDO just replays the WAL, nothing more. You only read it
on unclean shutdown.

> Now i tried to set the bgwriter_lru_percent to 100% and
> bgwriter_lru_maxpages to 1000 and
> i did not spot any difference with the disk activities, cpu occupation
> or anything else from
> the default set up which is 1% and 5 so i was wondering if commiting
> after every transaction
> would prevent me from seeing any difference ? or is there another
> explanation ?

All the bgwriter does is write out dirty pages to disk so checkpoints
don't take as long. How much data is there ever outstanding on your
system, if it's not much, then the bgwriter probably isn't doing
much...

BTW, check out the documentation on WAL, it's much clear than what I've
written..

Hvae a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.