Re: [GENERAL] Need help understanding WAL and checkpoints

[drew_hunt1976]

Hi Albe

--- On Wed, 2013/2/6, Albe Laurenz laurenz.a...@wien.gv.at wrote:

 drew_hunt wrote:
  I'm trying to get my head around WAL and checkpoints and need to ask a 
  couple of questions before I
  get a headache.
  
  Firstly, I see the terms WAL log, WAL file and transaction log all 
  over the place - are these
  the same thing (i.e. files in the pg_xlog directory)?
 
 Usually they mean the same thing.
 For exact definitions, read
 http://www.postgresql.org/docs/current/static/continuous-archiving.html#BACKUP-ARCHIVING-WAL
 WAL consists of a stream of WAL records and is physically
 represented as WAL segment files (in pg_xlog).
 
  I'm a bit confused by this paragraph in the docs:
  
  Checkpoints are points in the sequence of transactions at which it is 
  guaranteed that the heap and
  index data files have been updated with all information written before the 
  checkpoint. At checkpoint
  time, all dirty data pages are flushed to disk and a special checkpoint 
  record is written to the log
  file. (The changes were previously flushed to the WAL files.)
  
  ( see: http://www.postgresql.org/docs/current/static/wal-configuration.html 
  )
  
  a special checkpoint record is written to the log file.
      - which log file is meant here?
 
 The WAL.
 
  The changes were previously flushed to the WAL files.
     - does previously here mean at a previous point in time or in 
  previous PostgreSQL versions?
 
 The former.
 
     - at what point are changes flushed to WAL files?
 
 The change must be on disk in a WAL segment before the
 transaction can commit.
 
  So say I perform an operation like :
  
    UPDATE foo SET bar='baz'
  
  are the following assumptions correct?
  
  - The first time this changed data hits the disk, it is as an entry in the 
  WAL log
  
  - At some point a checkpoint occurs, and the changed data is written to the 
  actual data file from
  system memory (the dirty data pages?)
  
  - the only time the actual data files will be updated from the WAL log 
  (i.e. not from system memory)
  will be after a crash, when the logs are replayed from the last checkpoint?
 
 All three are correct.

Many thanks for the answers, its cleared things up for me :)

- drew


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Need help understanding WAL and checkpoints

[drew_hunt1976]

Hi there

I'm trying to get my head around WAL and checkpoints and need to ask a couple 
of questions before I get a headache.

Firstly, I see the terms WAL log, WAL file and transaction log all over 
the place - are these the same thing (i.e. files in the pg_xlog directory)?

I'm a bit confused by this paragraph in the docs:

Checkpoints are points in the sequence of transactions at which it is 
guaranteed that the heap and index data files have been updated with all 
information written before the checkpoint. At checkpoint time, all dirty data 
pages are flushed to disk and a special checkpoint record is written to the log 
file. (The changes were previously flushed to the WAL files.)

( see: http://www.postgresql.org/docs/current/static/wal-configuration.html )

a special checkpoint record is written to the log file.
    - which log file is meant here?

The changes were previously flushed to the WAL files.
   - does previously here mean at a previous point in time or in previous 
PostgreSQL versions?

   - at what point are changes flushed to WAL files?

So say I perform an operation like :

  UPDATE foo SET bar='baz'

are the following assumptions correct?

- The first time this changed data hits the disk, it is as an entry in the WAL 
log

- At some point a checkpoint occurs, and the changed data is written to the 
actual data file from system memory (the dirty data pages?)

- the only time the actual data files will be updated from the WAL log (i.e. 
not from system memory) will be after a crash, when the logs are replayed from 
the last checkpoint?

thanks for any help on this!

- drew


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general