Thread: Vacuum takes a really long time, vacuum full required
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
> 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.
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
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
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