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.
