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".
2009/7/16 J. Carlos Muro <mur...@gmail.com>
> 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
>
