X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 10D503A5675 for ; Wed, 22 Dec 2004 06:38:16 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 53973-04 for ; Wed, 22 Dec 2004 06:38:05 +0000 (GMT) Received: from hosting.commandprompt.com (128.commandprompt.com [207.173.200.128]) by svr1.postgresql.org (Postfix) with ESMTP id C043F3ABD72 for ; Wed, 22 Dec 2004 06:38:05 +0000 (GMT) Received: from [192.168.1.20] (clbb-248.saw.net [64.146.135.248]) (authenticated bits=0) by hosting.commandprompt.com (8.12.8/8.12.8) with ESMTP id iBM6c5El031349; Tue, 21 Dec 2004 22:38:05 -0800 Message-ID: <41C91653.9040602@commandprompt.com> Date: Tue, 21 Dec 2004 22:38:11 -0800 From: "Joshua D. Drake" User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206) X-Accept-Language: en-us, en MIME-Version: 1.0 To: ogjunk-pgjedan@yahoo.com Cc: pgsql-admin@postgresql.org Subject: Re: VACUUMing for 30 minutes References: <20041222060736.58922.qmail@web12706.mail.yahoo.com> In-Reply-To: <20041222060736.58922.qmail@web12706.mail.yahoo.com> Content-Type: multipart/mixed; boundary="------------090705050006010304010101" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.042 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200412/222 X-Sequence-Number: 15808 This is a multi-part message in MIME format. --------------090705050006010304010101 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit >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 --------------090705050006010304010101 Content-Type: text/x-vcard; charset=utf-8; name="jd.vcf" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="jd.vcf" begin:vcard fn:Joshua Drake n:Drake;Joshua org:Command Prompt, Inc. adr:;;PO Box 215 ;Cascade Locks;OR;97014;US email;internet:jd@commandprompt.com 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 --------------090705050006010304010101--