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
