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 B31C652BB8 for ; Wed, 17 Aug 2005 12:58:01 -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 34439-06 for ; Wed, 17 Aug 2005 15:57:59 +0000 (GMT) Received: from nproxy.gmail.com (nproxy.gmail.com [64.233.182.205]) by svr1.postgresql.org (Postfix) with ESMTP id 208B7529A0 for ; Wed, 17 Aug 2005 12:57:54 -0300 (ADT) Received: by nproxy.gmail.com with SMTP id c2so36585nfe for ; Wed, 17 Aug 2005 08:57:53 -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:references; b=OlnbjdpW8OaYP0PihTYplx8aRnBFQ7crb0H9yTg7EZTnU7fbU3MZmdVn8uaiIx6A7fh+lzlIk9OeQpEGaBBUd6GTYgzp0AqMqoOS0NqomlSFhP5efvtvdi8Xj59tnREUoVrp/mzKSP7KQs5jU+mBJAmSjPLBepwNXPdqbEe7wYs= Received: by 10.48.161.20 with SMTP id j20mr22672nfe; Wed, 17 Aug 2005 08:57:53 -0700 (PDT) Received: by 10.48.144.16 with HTTP; Wed, 17 Aug 2005 08:57:53 -0700 (PDT) Message-ID: Date: Wed, 17 Aug 2005 16:57:53 +0100 From: Guido Barosio To: D Kavan Subject: Re: vacuumdb -a -f Cc: pgsql-admin@postgresql.org In-Reply-To: Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_4462_1006788.1124294273503" References: X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.159 required=5 tests=[AWL=0.078, HTML_30_40=0.056, HTML_MESSAGE=0.001, RCVD_BY_IP=0.024] X-Spam-Level: X-Archive-Number: 200508/224 X-Sequence-Number: 18543 ------=_Part_4462_1006788.1124294273503 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline How are you finding out the DB size? G.- On 8/17/05, D Kavan wrote:=20 >=20 > Hi, >=20 > Thanks for the tips. >=20 > 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 =3D 4 GB. I even did a > stop/start instead of a re-load to make sure the settings took affect.=20 > Would > 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 > 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 > 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" > >CC: pgsql-admin@postgresql.org > >Subject: Re: [ADMIN] vacuumdb -a -f Date: Mon, 15 Aug 2005 21:31:01 -040= 0 > > > >"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 >=20 >=20 >=20 > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend >=20 --=20 "Adopting the position that you are smarter than an automatic optimization algorithm is generally a good way to achieve less performance, not more" - Tom Lane. ------=_Part_4462_1006788.1124294273503 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline
How are you finding out the DB size?
 
G.-

 
On 8/17/05, = D Kavan <bitsandbytes8= 8@hotmail.com> wrote:
Hi,

Thanks for the tips.<= br>
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 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
a reboot help?

max_fsm_pages =3D 16000001
max_fsm_relations = =3D 1000000

shared_buffers =3D 65536
work_mem =3D 32768
mainte= nance work mem =3D 786432

checkpoint_segments =3D 18


##/e= tc/sysctl.conf

nel.shmall =3D 524288
#kernel.shmall =3D 2097152
#kernel.shmm= ax =3D 2147483648
#kernel.shmmax =3D 1073741824
kernel.shmmax =3D 697= 9321856
kernel.shmmni =3D 4096
kernel.sem =3D 250 32000 100 128
fs= .file-max =3D 65536
net.ipv4.ip_local_port_range =3D 1024 65000
vm.overcommit_memory =3D= 2


~DjK





>From: Tom Lane <tgl@sss.pgh.pa.us>
>To: "D Kav= an" < bitsandbytes88@hotmail.com>
>CC:
pgsql-admin@= postgresql.org
>Subject: Re: [ADMIN] vacuumdb -a -f Date: Mon, 15= Aug 2005 21:31:01 -0400
>
>"D Kavan" <bitsandbytes88@hotmail.com> writes:
> > Even tho= ugh 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 coul= d 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.
>
>      &nbs= p;            &= nbsp;   regards, tom lane



---------------------------(end of broadcast)---------------------------TIP 6: explain analyze is your friend



--
"Adopting the position that you are smarter than a= n automatic
optimization algorithm is generally a good way to achieve less
perfo= rmance, not more" - Tom Lane.=20 ------=_Part_4462_1006788.1124294273503--