Disk Utilization Increases And Time for Vacuum Increases. - Mailing list pgsql-admin

=========================================================
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