Re: Vacuum takes a really long time, vacuum full required - Mailing list pgsql-performance

On Tue, Oct 19, 2004 at 11:40:17AM -0400, Rod Taylor wrote:
> > Whatever the case, the database still slows down to a halt after a month or
> > so, and I have to go in and shut everything down and do a VACUUM FULL by
> > hand.  One index (of many many) takes 2000 seconds to vacuum.  The whole
> > process takes a few hours.
>
> Do a REINDEX on that table instead, and regular vacuum more frequently.
>
> > $ pg_config --version
> >     PostgreSQL 7.3.2
>
> 7.4.x deals with index growth a little better 7.3 and older did.

I did a REINDEX of the database.  The results are pretty insane, the db went
from 16GB to 381MB.  Needless to say things are running a lot faster.

I will now take Tom's well-given advice and upgrade to 7.4.  But at least
now I have something to tell my users who are not able to do a DB upgrade
for whatever reason.

Thanks for all your help folks!
-m

Before:
# du -h pgsql
    135K    pgsql/global
    128M    pgsql/pg_xlog
     80M    pgsql/pg_clog
    3.6M    pgsql/base/1
    3.6M    pgsql/base/16975
    1.0K    pgsql/base/16976/pgsql_tmp
     16G    pgsql/base/16976
     16G    pgsql/base
     16G    pgsql

After Reindex:
# du /data/pgsql/
    131K    /data/pgsql/global
    128M    /data/pgsql/pg_xlog
     81M    /data/pgsql/pg_clog
    3.6M    /data/pgsql/base/1
    3.6M    /data/pgsql/base/16975
    1.0K    /data/pgsql/base/16976/pgsql_tmp
    268M    /data/pgsql/base/16976
    275M    /data/pgsql/base
    484M    /data/pgsql/

After Vacuum:
# du /data/pgsql/
    131K    /data/pgsql/global
    144M    /data/pgsql/pg_xlog
     81M    /data/pgsql/pg_clog
    3.6M    /data/pgsql/base/1
    3.6M    /data/pgsql/base/16975
    1.0K    /data/pgsql/base/16976/pgsql_tmp
    149M    /data/pgsql/base/16976
    156M    /data/pgsql/base
    381M    /data/pgsql/

netdisco=> select relname, relpages from pg_class order by relpages desc;

Before:
             relname             | relpages
---------------------------------+----------
 idx_node_switch_port_active     |   590714
 idx_node_switch_port            |   574344
 idx_node_switch                 |   482202
 idx_node_mac                    |   106059
 idx_node_mac_active             |    99842

After:
             relname             | relpages
---------------------------------+----------
 node_ip                         |    13829
 node                            |     9560
 device_port                     |     2124
 node_ip_pkey                    |     1354
 idx_node_ip_ip                  |     1017
 idx_node_ip_mac_active          |      846