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
