Disk Utilization Increases And Time for Vacuum Increases.

=========================================================
Disk Utilization Increases And Time for Vacuum Increases.
=========================================================

As reported in some threads before the Disk Utilization
of Postgres Database increases as time passes.

I have taken all the steps that were suggested in the
mailing list and still see that the disk utilization
keeps growing.

One action we have consiously not done is "REINDEX" on the
table. We want to avoid that as far as possible.

I have given my detailed setup and test plan and results.
Any suggestions on how we can take care of this disk utilization
would be appreciated.

*********************************************
Setup:
*********************************************
======
schema
======
create table stressdb (
i integer not null,
s1 varchar(1024) not null,
s2 varchar(1024) not null,
s3 varchar(1024) not null,
b lo
);
alter table stressdb add constraint pk_stressdb primary key (i);
create index idx_stressdb_1 on stressdb (s1);
create index idx_stressdb_2 on stressdb (s2);
create index idx_stressdb_3 on stressdb (s3);
create index idx_stressdb_4 on stressdb (i, s1, s2, s3);
create index idx_stressdb_5 on stressdb (s1, s2, s3);
======

=====================
Postgres version: 7.2
=====================

===============
postgresql.conf
===============
<--snip-->
shared_buffers = 8192
max_fsm_relations = 200
max_fsm_pages = 1000000
sort_mem = 32768
vacuum_mem = 16384
<--snip-->

*********************************************
Stress Test:
*********************************************
loop {
Add 100000 rows
Delete 100000 rows
Run Vacuum
pg_unlink all orphaned items from pg_largeobjects
}

*********************************************
Results:
*********************************************
1: Time taken to run vacuum increases in each iteration.

2: disk utilization increases with each iteration.
# df -k
Filesystem 1K-blocks Used Available Use% Mounted on
START STATS: /dev/sda6 1968620 110004 1838616 6% /var
iteration 1: /dev/sda6 1968620 435436 1513184 23% /var
iteration 2: /dev/sda6 1968620 547916 1400704 29% /var
iteration 3: /dev/sda6 1968620 725872 1222748 38% /var
iteration 4: /dev/sda6 1968620 985488 963132 51% /var


3: relpages for the indices increases with the first few
iterations and then stays constant.

This is determined by running:
"SELECT RELNAME, RELPAGES, RELTUPLES FROM PG_CLASS"

NOTE: Also the reltuples always stay at 0 (zero).
I dont know how to interpret that. What does
it mean.

1st iteration
relname | relpages | reltuples
-------------------------------+----------+-----------
idx_stressdb_4 | 1697 | 0
idx_stressdb_5 | 1317 | 0
idx_stressdb_1 | 603 | 0
idx_stressdb_3 | 593 | 0
idx_stressdb_2 | 588 | 0
pk_stressdb | 306 | 0

2nd iteration
relname | relpages | reltuples
---------------------------------+----------+-----------
idx_stressdb_5 | 2639 | 0
idx_stressdb_4 | 1697 | 0
idx_stressdb_1 | 1196 | 0
idx_stressdb_3 | 1180 | 0
idx_stressdb_2 | 1178 | 0
pk_stressdb | 306 | 0

** 5th ** iteration
relname | relpages | reltuples
---------------------------------+----------+-----------
idx_stressdb_5 | 3945 | 0
idx_stressdb_1 | 1785 | 0
idx_stressdb_2 | 1779 | 0
idx_stressdb_3 | 1760 | 0
idx_stressdb_4 | 1697 | 0
pk_stressdb | 306 | 0

__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com