WAL Internals question

From: Fabrice Franquenk <Fabrice(dot)Franquenk(at)bull(dot)net>
To: pgsql-novice(at)postgresql(dot)org
Subject: WAL Internals question
Date: 2006-07-04 15:44:41
Message-ID: 44AA8CE9.1030506@bull.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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 ?

Thank you for your help

Regards,

Fabrice Franquenk.

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Tom Lane 2006-07-04 18:18:11 Re: WAL Internals question
Previous Message Derrick Betts 2006-07-03 17:50:11 Re: Column names as variables in plpgsql