pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Vacuum takes a really long time, vacuum full required
5+ messages / 3 participants
[nested] [flat]

* Vacuum takes a really long time, vacuum full required
@ 2004-10-19 15:38 Max Baker <max@warped.org>
  2004-10-19 15:40 ` Re: Vacuum takes a really long time, vacuum full required Rod Taylor <pg@rbt.ca>
  2004-10-19 16:21 ` Re: Vacuum takes a really long time, vacuum full required Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 2 replies; 5+ messages in thread

From: Max Baker @ 2004-10-19 15:38 UTC (permalink / raw)
  To: pgsql-performance

Hi Folks,

This is my _4th_ time trying to post this, me and the mailing list software
are fighting.  I think it's because of the attachments so I'll just put
links to them instead.  All apologies if this gets duplicated.

I've been having problems maintaining the speed of the database in the
long run.  VACUUMs of the main tables happen a few times a day after maybe
50,000 or less rows are added and deleted (say 6 times a day).

I have a whole lot (probably too much) indexing going on to try to speed
things up. 

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.

I would love suggestions on what I can do either inside my application, or
from a dba point of view to keep the database maintained without having to
inflict downtime.  This is for 'Netdisco' -- an open source network
management software by the way.  I'ld like to fix this for everyone who uses
it.


Sys Info :

$ uname -a
    FreeBSD xxxx.ucsc.edu 4.10-STABLE FreeBSD 4.10-STABLE #0: Mon Aug 16
14:56:19 PDT 2004     root@xxxx.ucsc.edu:/usr/src/sys/compile/xxxx  i386

$ pg_config --version
    PostgreSQL 7.3.2

$ cat postgresql.conf
    max_connections = 32
    shared_buffers = 3900           # 30Mb - Bsd current kernel limit
    max_fsm_relations = 1000        # min 10, fsm is free space map, ~40 bytes
    max_fsm_pages = 10000           # min 1000, fsm is free space map, ~6 bytes
    max_locks_per_transaction = 64  # min 10
    wal_buffers = 8                 # min 4, typically 8KB each

The log of the vacuum and the db schema could not be attached, so they are
at : 
    http://netdisco.net/db_vacuum.txt
    http://netdisco.net/pg_all.input

Thanks for any help!
-m



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Vacuum takes a really long time, vacuum full required
  2004-10-19 15:38 Vacuum takes a really long time, vacuum full required Max Baker <max@warped.org>
@ 2004-10-19 15:40 ` Rod Taylor <pg@rbt.ca>
  2004-10-19 18:12   ` Re: Vacuum takes a really long time, vacuum full required Max Baker <max@warped.org>
  2004-10-24 05:08   ` Re: Vacuum takes a really long time, vacuum full required Max Baker <max@warped.org>
  1 sibling, 2 replies; 5+ messages in thread

From: Rod Taylor @ 2004-10-19 15:40 UTC (permalink / raw)
  To: Max Baker <max@warped.org>; +Cc: pgsql-performance

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




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Vacuum takes a really long time, vacuum full required
  2004-10-19 15:38 Vacuum takes a really long time, vacuum full required Max Baker <max@warped.org>
  2004-10-19 15:40 ` Re: Vacuum takes a really long time, vacuum full required Rod Taylor <pg@rbt.ca>
@ 2004-10-19 18:12   ` Max Baker <max@warped.org>
  1 sibling, 0 replies; 5+ messages in thread

From: Max Baker @ 2004-10-19 18:12 UTC (permalink / raw)
  To: Rod Taylor <pg@rbt.ca>; +Cc: pgsql-performance

Hi Rod,

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.

Great, this is exactly what I think it needs.  Meanwhile, I was checking out

    http://www.postgresql.org/docs/7.3/static/sql-reindex.html

Which suggests I might be able to do a drop/add on each index with the
database 'live'.

However, the DROP INDEX command was taking an awfully long time to complete
and it hung my app in the mean time.   Does anyone know if the DROP INDEX
causes an exclusive lock, or is it just a lengthy process?

> > $ pg_config --version
> >     PostgreSQL 7.3.2
> 
> 7.4.x deals with index growth a little better 7.3 and older did.

Will do.  Meanwhile I'm stuck supporting older 7.x versions, so I'm still
looking for a solution for them.

Thanks!
-m



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Vacuum takes a really long time, vacuum full required
  2004-10-19 15:38 Vacuum takes a really long time, vacuum full required Max Baker <max@warped.org>
  2004-10-19 15:40 ` Re: Vacuum takes a really long time, vacuum full required Rod Taylor <pg@rbt.ca>
@ 2004-10-24 05:08   ` Max Baker <max@warped.org>
  1 sibling, 0 replies; 5+ messages in thread

From: Max Baker @ 2004-10-24 05:08 UTC (permalink / raw)
  To: Rod Taylor <pg@rbt.ca>; +Cc: 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




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Vacuum takes a really long time, vacuum full required
  2004-10-19 15:38 Vacuum takes a really long time, vacuum full required Max Baker <max@warped.org>
@ 2004-10-19 16:21 ` Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 5+ messages in thread

From: Tom Lane @ 2004-10-19 16:21 UTC (permalink / raw)
  To: Max Baker <max@warped.org>; +Cc: pgsql-performance

Max Baker <max@warped.org> writes:
> I've been having problems maintaining the speed of the database in the
> long run.  VACUUMs of the main tables happen a few times a day after maybe
> 50,000 or less rows are added and deleted (say 6 times a day).

> I have a whole lot (probably too much) indexing going on to try to speed
> things up. 

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

The first and foremost recommendation is to increase your FSM settings;
you seem to be using the defaults, which are pegged for a database size
of not more than about 100Mb.

Second is to update to PG 7.4.  I think you are probably suffering from
index bloat to some extent, and 7.4 should help.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2004-10-24 05:08 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-10-19 15:38 Vacuum takes a really long time, vacuum full required Max Baker <max@warped.org>
2004-10-19 15:40 ` Rod Taylor <pg@rbt.ca>
2004-10-19 18:12   ` Max Baker <max@warped.org>
2004-10-24 05:08   ` Max Baker <max@warped.org>
2004-10-19 16:21 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox