Skip site navigation (1) Skip section navigation (2)

Re: Vacuum takes a really long time, vacuum full required

From: Max Baker <max(at)warped(dot)org>
To: Rod Taylor <pg(at)rbt(dot)ca>
Cc: Postgresql Performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Vacuum takes a really long time, vacuum full required
Date: 2004-10-24 05:08:11
Message-ID: 20041024050811.GA12044@warped.org (view raw or flat)
Thread:
Lists: 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


In response to

pgsql-performance by date

Next:From: Curt SampsonDate: 2004-10-24 05:46:16
Subject: Re: First set of OSDL Shared Mem scalability results, some
Previous:From: Gaetano MendolaDate: 2004-10-23 23:20:46
Subject: Re: futex results with dbt-3

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group