Re: VACUUMing for 30 minutes

>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.)
>
>
It depends... how much IO do you have and how active
is the DB (updates/deletes)

>- 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)
>
>
It can definately help.

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

Yes.

>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
>>
>>
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: Have you searched our list archives?
>
>               http://archives.postgresql.org
>
>


--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
PostgreSQL Replicator -- production quality replication for PostgreSQL