Обсуждение: WAL Internals question

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.

Fabrice Franquenk <Fabrice.Franquenk@bull.net> writes:
> 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

Hmm, are you sure your changes took effect?  To get the bgwriter's
attention you need to make the change in postgresql.conf and then
SIGHUP or restart the postmaster.  I'd have thought you could easily
measure a difference given that much change ...

            regards, tom lane

Tom Lane a écrit :
> Fabrice Franquenk <Fabrice.Franquenk@bull.net> writes:
>
>> 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
>>
>
> Hmm, are you sure your changes took effect?  To get the bgwriter's
> attention you need to make the change in postgresql.conf and then
> SIGHUP or restart the postmaster.  I'd have thought you could easily
> measure a difference given that much change ...
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
>
I made sure that all changes in postgresql.conf took effect. I restarted
the postmaster and i could not spot
any difference.
The values of  bgwriter_all_percent and bgwriter_all_maxpages are all
default (and i set checkpoints segments value to 250). Could there be
some kind of connection between these parameters and the bgwriter_lru_*
parameters?

by the way, was i correct describing the transaction handling mechanism ?

thanks for your help

regards,
            Fabrice Franquenk