Обсуждение: Tests with switch of WAL segment files.

Hi! I am trying to better understand when switch of WAL files takes place. I have executed the next tests while "archive_mode = off":

    $ initdb -D /var/lib/postgresql/8.3/data

    $ du -sk /var/lib/postgresql/8.3/data/base
    13156   base

    $ psql -c "drop table if exists heavytable; create table heavytable as select * from pg_class, pg_description;"
    $ du -sk /var/lib/postgresql/8.3/data/base
    116532  base
    $ ls -lh /var/lib/postgresql/8.3/data/pg_xlog
    -rw------- 1 postgres postgres  16M 2009-07-16 17:21 000000010000000000000000

    $ psql -t -c "select pg_size_pretty(pg_total_relation_size('heavytable')) as size;"
    97 MB

    $ psql -c "drop table if exists heavytable; create table heavytable as select * from pg_class, pg_description;"
    $ psql -c "drop table if exists heavytable; create table heavytable as select * from pg_class, pg_description;"
    $ psql -c "drop table if exists heavytable; create table heavytable as select * from pg_class, pg_description;"
    $ du -sk /var/lib/postgresql/8.3/data/base
    116588  base
    $ ls -lh /var/lib/postgresql/8.3/data/pg_xlog
    -rw------- 1 postgres postgres  16M 2009-07-16 17:26 000000010000000000000000

I create many other times the 'heavytable', nothing changed, WAL segment file keeps to be the same, the size of base directory almost didn't change.
I guess that's normal, and I think there is something that I haven't yet reached to understand from all the docs that I have read. I have two questions:

Q1 - Why do I have just one segment file the whole time? I thought PostgreSQL would create at least 3 of them at the beginning... I have "checkpoint_segments = 3".

If I now state a "select pg_switch_xlog();" it returns one label, but if I execute it again N times it will constantly return the same label (while I don't insert data).
I have executed "select pg_switch_xlog();" many times (please see the next log of tests). After that, I realize that PostgreSQL is ready to create the next segment file ONLY whenever I insert new data.

Q2 - After that, I stated a CHECKPOINT, what caused a switch to a new file. I execute CHECKPOINT once again and switch takes place again. But, if I execute CHECKPOINT again and again, from now and on will NOT be any switches. Why? What makes the switch to a new file take place?

Here is the log of tests:

    $ psql -p 5433 --tuples-only webcreds -c "select pg_switch_xlog();"
    0/4EFA80
    $ du -sk /var/lib/postgresql/8.3/data/base
    116684  base
    $ ls -lh /var/lib/postgresql/8.3/data/pg_xlog
    -rw------- 1 postgres postgres  16M 2009-07-16 18:23 000000010000000000000000

    $ psql -c "drop table if exists heavytable; create table heavytable as select * from pg_class, pg_description;"
    $ ls -lh /var/lib/postgresql/8.3/data/pg_xlog
    -rw------- 1 postgres postgres  16M 2009-07-16 18:45 000000010000000000000000
    -rw------- 1 postgres postgres  16M 2009-07-16 18:45 000000010000000000000001

    $ psql -p 5433 --tuples-only webcreds -c "select pg_switch_xlog();"
    0/100B9D0
    $ psql -p 5433 --tuples-only webcreds -c "select pg_switch_xlog();"
    0/2000000
    $ psql -p 5433 --tuples-only webcreds -c "select pg_switch_xlog();"
    0/2000000
    $ psql -p 5433 --tuples-only webcreds -c "select pg_switch_xlog();"
    0/2000000
    $ psql -c "drop table if exists heavytable; create table heavytable as select * from pg_class, pg_description;"
    $ ls -lh /var/lib/postgresql/8.3/data/pg_xlog
    -rw------- 1 postgres postgres  16M 2009-07-16 18:45 000000010000000000000000
    -rw------- 1 postgres postgres  16M 2009-07-16 18:48 000000010000000000000001
    -rw------- 1 postgres postgres  16M 2009-07-16 18:49 000000010000000000000002

    $ psql -p 5433 --tuples-only webcreds -c "CHECKPOINT;" # <== it causes switch
    $ ls -lh /var/lib/postgresql/8.3/data/pg_xlog
    -rw------- 1 postgres postgres  16M 2009-07-16 18:48 000000010000000000000001
    -rw------- 1 postgres postgres  16M 2009-07-16 18:52 000000010000000000000002
    -rw------- 1 postgres postgres  16M 2009-07-16 18:45 000000010000000000000003

    $ psql -p 5433 --tuples-only webcreds -c "CHECKPOINT;"  # <== one of these will cause switch
    $ psql -p 5433 --tuples-only webcreds -c "CHECKPOINT;"
    $ psql -p 5433 --tuples-only webcreds -c "CHECKPOINT;"
    $ ls -lh /var/lib/postgresql/8.3/data/pg_xlog
    -rw------- 1 postgres postgres  16M 2009-07-16 18:48 000000010000000000000002
    -rw------- 1 postgres postgres  16M 2009-07-16 18:52 000000010000000000000003
    -rw------- 1 postgres postgres  16M 2009-07-16 18:45 000000010000000000000004

    $ psql -p 5433 --tuples-only webcreds -c "CHECKPOINT;"  # <== there won't be any switches from now and on!
    $ psql -p 5433 --tuples-only webcreds -c "CHECKPOINT;"
    $ psql -p 5433 --tuples-only webcreds -c "CHECKPOINT;"
    $ ls -lh /var/lib/postgresql/8.3/data/pg_xlog
    -rw------- 1 postgres postgres  16M 2009-07-16 18:48 000000010000000000000002
    -rw------- 1 postgres postgres  16M 2009-07-16 18:52 000000010000000000000003
    -rw------- 1 postgres postgres  16M 2009-07-16 18:45 000000010000000000000004

    $ psql -c "drop table if exists heavytable; create table heavytable as select * from pg_class, pg_description;"
    $ ls -lh /var/lib/postgresql/8.3/data/pg_xlog
    -rw------- 1 postgres postgres  16M 2009-07-16 18:56 000000010000000000000002
    -rw------- 1 postgres postgres  16M 2009-07-16 18:45 000000010000000000000003
    -rw------- 1 postgres postgres  16M 2009-07-16 18:48 000000010000000000000004

    $ psql -p 5433 --tuples-only webcreds -c "CHECKPOINT;"
    $ psql -p 5433 --tuples-only webcreds -c "CHECKPOINT;"
    $ ls -lh /var/lib/postgresql/8.3/data/pg_xlog
    -rw------- 1 postgres postgres  16M 2009-07-16 18:56 000000010000000000000002
    -rw------- 1 postgres postgres  16M 2009-07-16 18:45 000000010000000000000003
    -rw------- 1 postgres postgres  16M 2009-07-16 18:48 000000010000000000000004

    $ psql -c "drop table if exists heavytable; create table heavytable as select * from pg_class, pg_description;"
    $ ls -lh /var/lib/postgresql/8.3/data/pg_xlog
    -rw------- 1 postgres postgres  16M 2009-07-16 18:59 000000010000000000000002
    -rw------- 1 postgres postgres  16M 2009-07-16 18:45 000000010000000000000003
    -rw------- 1 postgres postgres  16M 2009-07-16 18:48 000000010000000000000004

    $ psql -p 5433 --tuples-only webcreds -c "CHECKPOINT;"
    $ ls -lh /var/lib/postgresql/8.3/data/pg_xlog
    -rw------- 1 postgres postgres  16M 2009-07-16 19:00 000000010000000000000002
    -rw------- 1 postgres postgres  16M 2009-07-16 18:45 000000010000000000000003
    -rw------- 1 postgres postgres  16M 2009-07-16 18:48 000000010000000000000004

Thanks in advance!!
J. Carlos Muro

I realized something else, that only happened once.
I recreated the cluster. If I execute "select pg_switch_xlog();" once, then segment file gets almost immediately switched (a new file is created) even though I don't insert new data. If I waite some thime and execute "select pg_switch_xlog();" then segments are almost inmediately switched again, and so on...
If instead I execute "select pg_switch_xlog();" N consecutive times (N > 1), then the segment file is not switched until new inserts arrive... 

I tried to reproduce the case, recreating the cluster, but it didn't happen again. It is very extrange...

Anyone has got some similar experience? BTW, I am running "PostgreSQL 8.3.7 on x86_64-pc-linux-gnu".

"J. Carlos Muro" <murojc@gmail.com> wrote:

>> Hi! I am trying to better understand when switch of WAL files takes
>> place.

I believe that it happens when the file is full, when pg_xlog_switch
is run (if there has been anything written to the current WAL file).
I think, at least in some version, a switch is also forced at certain
points in the shutdown or recovery phases.  Some of the examples you
showed didn't switch files when you expected because you were running
statements which bypass WAL logging.  (In particular, inserting rows
into a table in the same transaction in which the table is created,
when archiving is off, doesn't require WAL logging -- if the
transaction rolls back the table will be gone anyway.)

I hope this helps.

-Kevin