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 266F3529EC for ; Wed, 17 Aug 2005 13:57:22 -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 54443-02 for ; Wed, 17 Aug 2005 16:57:17 +0000 (GMT) Received: from zproxy.gmail.com (zproxy.gmail.com [64.233.162.193]) by svr1.postgresql.org (Postfix) with ESMTP id 653275284B for ; Wed, 17 Aug 2005 13:57:16 -0300 (ADT) Received: by zproxy.gmail.com with SMTP id 8so140733nzo for ; Wed, 17 Aug 2005 09:57:16 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=MydrTXejRI7LY4m3K/FmWgASWtuNFoVV2kF4tDwiNzQw0kMTloFMcQCNHcVKP7xlhLnZ/L9YB5QjGhWS1wydL+V3Ftt1u5zdz5QCT2fjdkNhxQRIIgj/OjbwjpvhgOuGFohgf97yThmYZFFMRdez6gBqkK+T74V9QiIfMwBKFR0= Received: by 10.36.250.79 with SMTP id x79mr631606nzh; Wed, 17 Aug 2005 09:57:16 -0700 (PDT) Received: by 10.36.24.2 with HTTP; Wed, 17 Aug 2005 09:57:16 -0700 (PDT) Message-ID: <1d219a6f05081709575c72f117@mail.gmail.com> Date: Wed, 17 Aug 2005 12:57:16 -0400 From: Chris Hoover To: Guido Barosio Subject: Re: vacuumdb -a -f Cc: D Kavan , pgsql-admin@postgresql.org In-Reply-To: Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline References: X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.024 required=5 tests=[AWL=-0.000, RCVD_BY_IP=0.024] X-Spam-Level: X-Archive-Number: 200508/227 X-Sequence-Number: 18546 You might want to try doing a reindexdb -a -e (reindexdb is in the contrib directory of your pg source). The first time I ran this, I gained back a significant amount of space. I now run a vacuumdb -v -f -a and then a reindexdb -a -e every weekend to have PostgreSQL give back as much space as it can. I have ended up doing this do to space and i/o constraints. Give the reindexdb script a try and see if you don't get your space back. HTH, Chris On 8/17/05, Guido Barosio wrote: > How are you finding out the DB size?=20 > =20 > G.- >=20 > =20 > =20 > On 8/17/05, D Kavan wrote:=20 > > Hi, > >=20 > > Thanks for the tips. > >=20 > > Unfortunatley for me, even after started doing vacuumdb -a 3 times a d= ay=20 > > and increasing fsm dramatically , the size of the database won't go dow= n > > even 1 MB. It's stil at 5.6 GB, size after restore =3D 4 GB. I even = did a > > stop/start instead of a re-load to make sure the settings took affect. > Would=20 > > a reboot help? > >=20 > > max_fsm_pages =3D 16000001 > > max_fsm_relations =3D 1000000 > >=20 > > shared_buffers =3D 65536 > > work_mem =3D 32768 > > maintenance work mem =3D 786432 > >=20 > > checkpoint_segments =3D 18 > >=20 > >=20 > > ##/etc/sysctl.conf=20 > >=20 > > nel.shmall =3D 524288 > > #kernel.shmall =3D 2097152 > > #kernel.shmmax =3D 2147483648 > > #kernel.shmmax =3D 1073741824 > > kernel.shmmax =3D 6979321856 > > kernel.shmmni =3D 4096 > > kernel.sem =3D 250 32000 100 128 > > fs.file-max =3D 65536=20 > > net.ipv4.ip_local_port_range =3D 1024 65000 > > vm.overcommit_memory =3D 2 > >=20 > >=20 > > ~DjK > >=20 > >=20 > >=20 > >=20 > >=20 > > >From: Tom Lane > > >To: "D Kavan" < bitsandbytes88@hotmail.com> > > >CC: pgsql-admin@postgresql.org > > >Subject: Re: [ADMIN] vacuumdb -a -f Date: Mon, 15 Aug 2005 21:31:01 -0= 400 > > > > > >"D Kavan" writes: > > > > Even though I run vacuumdb -a -f every night with no exceptions or > > >problems,=20 > > > > my database size remains 5.6 GB. After I do a dump/restore, the ne= w > > > > database size is 4.0 GB. How could that be possible? > > > > > >The extra 1.6GB probably represents the amount of junk you generate in= =20 > > >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 > >=20 > >=20 > >=20 > > ---------------------------(end of > broadcast)--------------------------- > > TIP 6: explain analyze is your friend > >=20 >=20 >=20 >=20 > --=20 > "Adopting the position that you are smarter than an automatic=20 > optimization algorithm is generally a good way to achieve less > performance, not more" - Tom Lane.