Hello,
I still have this issue of a looooong vacuum process on a DB that is
not really all that big in schema (~ 30 tables) nor size (biggest table
is ~150K rows, another ~120K, all others only a few thousand rows
each).
VACUUMing this DB takes about 30 minutes, and during that time the DB
is pretty unresponsive, although the PG process is not using a lot of
CPU (load ~ 1) nor memory (~20MB for the VACUUM process).
During VACUUM I see these DB sessions:
28764 | simpydb | postgres | select count(*) from pg_stat_activity
25946 | simpydb | otis | VACUUM;
My questions are:
- Does it sounds normal that such a small DB would need 30 minute
vacuuming? (My iRobot Rumba does my apartment in less time.)
- Should I be giving PG more RAM while it's VACUUMing? (the PG process
running VACUUM is using only 20MB now, but I'm not sure if it needs
more)
Here are some possibly relevant config settings:
shared_buffers = 2048
sort_mem = 4096 # min 64, size in KB
effective_cache_size = 10000
#vacuum_mem = 8192 -- oh, look at that. Can I freely give it more
without affecting the memory consumption while VACUUM is not running?
Thanks,
Otis
--- ogjunk-pgjedan@yahoo.com wrote:
> Hello,
>
> I have a DB with about 30 tables, where 2 tables are significantly
> larger than the rest, and contain a bit over 100,000 rows.
>
> Every night I do these 3 things:
> VACUUM;
> ANALYZE;
> pg_dump
>
> I am noticing that the VACUUM part takes nearly 30 minutes, during
> which the DB is not very accessible (and a whole lot of load is put
> on
> the machine in general).
>
> Using pgsession.sh script mentioned earlier, I caught this process
> taking a long time:
>
> 31179 | mydb | otis | FETCH 100 FROM _pg_dump_cursor
>
> Is there anything one can do to minimize the impact of VACUUM?
>
> I am using PG 7.3.4 on a Linux box with a 1.70GHz Celeron, 1GB RAM,
> and
> a 'regular' IDE disk.
>
> Thanks,
> Otis