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 5DFBA52896 for ; Wed, 17 Aug 2005 13:12:49 -0300 (ADT) 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 38346-05 for ; Wed, 17 Aug 2005 16:12:46 +0000 (GMT) Received: from hosting.commandprompt.com (128.commandprompt.com [207.173.200.128]) by svr1.postgresql.org (Postfix) with ESMTP id 76D5C5288A for ; Wed, 17 Aug 2005 13:12:46 -0300 (ADT) Received: from [192.168.1.55] (fc1smp [66.93.38.87]) (authenticated bits=0) by hosting.commandprompt.com (8.13.4/8.13.4) with ESMTP id j7HGB9IF021501; Wed, 17 Aug 2005 09:11:10 -0700 Message-ID: <430362AC.6010807@commandprompt.com> Date: Wed, 17 Aug 2005 09:15:40 -0700 From: "Joshua D. Drake" Organization: Command Prompt, Inc. User-Agent: Mozilla Thunderbird 1.0.6-1.1.fc4 (X11/20050720) X-Accept-Language: en-us, en MIME-Version: 1.0 To: D Kavan Cc: pgsql-admin@postgresql.org Subject: Re: vacuumdb -a -f References: In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Greylist: Sender succeded SMTP AUTH authentication, not delayed by milter-greylist-1.6 (hosting.commandprompt.com [192.168.1.101]); Wed, 17 Aug 2005 09:11:10 -0700 (PDT) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.014 required=5 tests=[AWL=0.014] X-Spam-Level: X-Archive-Number: 200508/223 X-Sequence-Number: 18542 D Kavan wrote: > Hi, > > Thanks for the tips. > > Unfortunatley for me, even after started doing vacuumdb -a 3 times a > day and increasing fsm dramatically , the size of the database won't go > down even 1 MB. It's stil at 5.6 GB, size after restore = 4 GB. I > even did a stop/start instead of a re-load to make sure the settings > took affect. Would a reboot help? A vacuum full will decrease the size but I am not sure why the size is a problem. I normal vacuum will not remove rows, it only make them reusable. So at any given point your database is going to grow after use. I didn't see this whole thread but is there something specific that seems to be the issue beyond size? Sincerely, Joshua D. Drake > > max_fsm_pages = 16000001 > max_fsm_relations = 1000000 > > shared_buffers = 65536 > work_mem = 32768 > maintenance work mem = 786432 > > checkpoint_segments = 18 > > > ##/etc/sysctl.conf > > nel.shmall = 524288 > #kernel.shmall = 2097152 > #kernel.shmmax = 2147483648 > #kernel.shmmax = 1073741824 > kernel.shmmax = 6979321856 > kernel.shmmni = 4096 > kernel.sem = 250 32000 100 128 > fs.file-max = 65536 > net.ipv4.ip_local_port_range = 1024 65000 > vm.overcommit_memory = 2 > > > ~DjK > > > > > >> From: Tom Lane >> To: "D Kavan" >> CC: pgsql-admin@postgresql.org >> Subject: Re: [ADMIN] vacuumdb -a -f Date: Mon, 15 Aug 2005 21:31:01 -0400 >> >> "D Kavan" writes: >> > Even though I run vacuumdb -a -f every night with no exceptions or >> problems, >> > my database size remains 5.6 GB. After I do a dump/restore, the new >> > database size is 4.0 GB. How could that be possible? >> >> The extra 1.6GB probably represents the amount of junk you generate in >> one day. So, forget the -f and instead do plain vacuums on a more >> frequent basis. Make sure your FSM settings are large enough, too. >> >> regards, tom lane > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend -- Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240 PostgreSQL Replication, Consulting, Custom Programming, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/