Skip site navigation (1) Skip section navigation (2)

Re: VACUUMing for 30 minutes

From: "Joshua D(dot) Drake" <jd(at)commandprompt(dot)com>
To: ogjunk-pgjedan(at)yahoo(dot)com
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: VACUUMing for 30 minutes
Date: 2004-12-22 06:38:11
Message-ID: 41C91653.9040602@commandprompt.com (view raw or flat)
Thread:
Lists: pgsql-admin
>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(at)yahoo(dot)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(at)commandprompt(dot)com - http://www.commandprompt.com
PostgreSQL Replicator -- production quality replication for PostgreSQL


Attachment: jd.vcf
Description: text/x-vcard (285 bytes)

In response to

pgsql-admin by date

Next:From: Vinita.BansalDate: 2004-12-22 13:25:29
Subject: default index for primary key of a table
Previous:From: Robert TreatDate: 2004-12-22 06:14:22
Subject: Re: Any tools to compare db's

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group