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
--- [EMAIL PROTECTED] 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 - [EMAIL PROTECTED] - http://www.commandprompt.com
PostgreSQL Replicator -- production quality replication for PostgreSQL
begin:vcard
fn:Joshua Drake
n:Drake;Joshua
org:Command Prompt, Inc.
adr:;;PO Box 215 ;Cascade Locks;OR;97014;US
email;internet:[EMAIL PROTECTED]
title:Consultant
tel;work:503-667-4564
tel;fax:503-210-0334
x-mozilla-html:FALSE
url:http://www.commandprompt.com
version:2.1
end:vcard
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
