Обсуждение: WAL internals

Hello,
I am currently working on a heavily stressed system and i am
having some difficulties to make the background writer work properly.

But before going any further, i would like to be sure that i understood
how transaction
processing, and management are handled by postgreSQL. Correct me if i'm
wrong :

1. A Transaction is sent from the postgreSQL backend

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.

transactions go on and on this way.

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

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.

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 ?

by the way, i made sure all changes took effect i restarted the
postmaster process.
I first asked these questions on the novice mailing list.

Thanks in advance for your help

Regards,

Fabrice Franquenk.

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@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.