[GENERAL] WAL internals

[Fabrice Franquenk]

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.

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] WAL internals

[Martijn van Oosterhout]

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.


signature.asc
Description: Digital signature