why is there such a diference???

what should I do (besides buying bigger an= d bigger=20 HDs) ???

[]'s Mauricio

------=_NextPart_000_0046_01C2819F.7D7CA8A0-- From mauricio@cristorei.com.br Fri Nov 1 15:24:44 2002 Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id EC1A747580B for ; Fri, 1 Nov 2002 10:24:44 -0500 (EST) Received: from cr1.cristorei.net.br (unknown [200.162.160.50]) by postgresql.org (Postfix) with ESMTP id EC783474E53 for ; Fri, 1 Nov 2002 10:24:43 -0500 (EST) Received: from mauricio ([200.162.160.203] unverified) by cr1.cristorei.net.br with Microsoft SMTPSVC(5.0.2195.4905); Fri, 1 Nov 2002 12:24:48 -0300 Message-ID: <008b01c281b2$901bd4a0$cba0a2c8@cristorei.com.br> From: =?iso-8859-1?Q?Maur=EDcio_Sessue_Otta?= To: "dima" <_pppp@mail.ru> Cc: References: <004901c281b0$411de2a0$cba0a2c8@cristorei.com.br> <3DC29C3C.8030803@mail.ru> Subject: Re: Different size in the DATA directory Date: Fri, 1 Nov 2002 12:25:43 -0200 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2600.0000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 X-OriginalArrivalTime: 01 Nov 2002 15:24:48.0091 (UTC) FILETIME=[D07342B0:01C281BA] X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200211/5 X-Sequence-Number: 10147 I do daily vacuums on the production server | > Hi, in my production server a "du -hs" in the DATA directory | > returns 21GB | > in a newly installed server, after I restored the dumps from the | > production server, the "du -hs" gives me just 8GB | > | > why is there such a diference??? | > | > what should I do (besides buying bigger and bigger HDs) ??? | VACUUM? | | From _pppp@mail.ru Fri Nov 1 15:22:36 2002 Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id C260A474E53 for ; Fri, 1 Nov 2002 10:22:36 -0500 (EST) Received: from mx2.mail.ru (mx2.mail.ru [194.67.57.12]) by postgresql.org (Postfix) with ESMTP id DA76F474E44 for ; Fri, 1 Nov 2002 10:22:35 -0500 (EST) Received: from drweb by mx2.mail.ru with drweb-scanned (Exim MX.2) id 187dd9-0007Zc-00; Fri, 01 Nov 2002 18:22:39 +0300 Received: from [213.128.193.142] (helo=mail.ru) by mx2.mail.ru with esmtp (Exim SMTP.2) id 187dd9-0007YN-00; Fri, 01 Nov 2002 18:22:39 +0300 Message-ID: <3DC29C3C.8030803@mail.ru> Date: Fri, 01 Nov 2002 18:22:36 +0300 From: dima <_pppp@mail.ru> User-Agent: Mozilla/5.0 (X11; U; FreeBSD i386; en-US; rv:1.0.0) Gecko/20020816 X-Accept-Language: ru, en-us, en MIME-Version: 1.0 To: =?ISO-8859-1?Q?Maur=EDcio_Sessue_Otta?= Cc: pgsql-sql@postgresql.org Subject: Re: Different size in the DATA directory References: <004901c281b0$411de2a0$cba0a2c8@cristorei.com.br> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200211/4 X-Sequence-Number: 10146 > Hi, in my production server a "du -hs" in the DATA directory > returns 21GB > in a newly installed server, after I restored the dumps from the > production server, the "du -hs" gives me just 8GB > > why is there such a diference??? > > what should I do (besides buying bigger and bigger HDs) ??? VACUUM? From sszabo@megazone23.bigpanda.com Fri Nov 1 15:32:49 2002 Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 2C35F474E53 for ; Fri, 1 Nov 2002 10:32:49 -0500 (EST) Received: from megazone.bigpanda.com (megazone.bigpanda.com [63.150.15.178]) by postgresql.org (Postfix) with ESMTP id B4AFF474E44 for ; Fri, 1 Nov 2002 10:32:48 -0500 (EST) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id AEAF4D616; Fri, 1 Nov 2002 07:32:49 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id A46135C04; Fri, 1 Nov 2002 07:32:49 -0800 (PST) Date: Fri, 1 Nov 2002 07:32:49 -0800 (PST) From: Stephan Szabo To: =?iso-8859-1?Q?Maur=EDcio_Sessue_Otta?= Cc: dima <_pppp@mail.ru>, Subject: Re: Different size in the DATA directory In-Reply-To: <008b01c281b2$901bd4a0$cba0a2c8@cristorei.com.br> Message-ID: <20021101073110.X58632-100000@megazone23.bigpanda.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=X-UNKNOWN Content-Transfer-Encoding: QUOTED-PRINTABLE X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200211/6 X-Sequence-Number: 10148 On Fri, 1 Nov 2002, [iso-8859-1] Maur=EDcio Sessue Otta wrote: > I do daily vacuums on the production server You haven't said what version you're running, but if you're using 7.2 and non-FULL vacuums you also will want to make sure that you have the free space map settings in postgresql.conf are large enough. Also, you may need to use reindex to lower the space usage by indexes. From tim@perdue.net Fri Nov 1 16:02:49 2002 Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id AB623475E20 for ; Fri, 1 Nov 2002 11:02:49 -0500 (EST) Received: from sccmmhc01.mchsi.com (sccmmhc01.mchsi.com [204.127.203.183]) by postgresql.org (Postfix) with ESMTP id 3166647580B for ; Fri, 1 Nov 2002 11:02:49 -0500 (EST) Received: from perdue.net ([12.216.72.93]) by sccmmhc01.mchsi.com (InterMail vM.4.01.03.27 201-229-121-127-20010626) with ESMTP id <20021101160253.KJZG6470.sccmmhc01.mchsi.com@perdue.net>; Fri, 1 Nov 2002 16:02:53 +0000 Message-ID: <3DC2A578.6040605@perdue.net> Date: Fri, 01 Nov 2002 10:02:00 -0600 From: Tim Perdue User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.2b) Gecko/20021016 X-Accept-Language: en-us, en MIME-Version: 1.0 To: =?ISO-8859-1?Q?Maur=EDcio_Sessue_Otta?= , pgsql-sql@postgresql.org Subject: Re: Different size in the DATA directory References: <004901c281b0$411de2a0$cba0a2c8@cristorei.com.br> In-Reply-To: <004901c281b0$411de2a0$cba0a2c8@cristorei.com.br> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 8bit X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200211/7 X-Sequence-Number: 10149 Maurício Sessue Otta wrote: > Hi, in my production server a "du -hs" in the DATA directory > returns 21GB > in a newly installed server, after I restored the dumps from the > production server, the "du -hs" gives me just 8GB > > why is there such a diference??? > > what should I do (besides buying bigger and bigger HDs) ??? > > []'s Mauricio I had this same problem with 7.1.x, even doing full vacuums on SourceForge.net last year. I assumed after a while that there was some hidden bug where file bloat occurred, despite the vacuum. After 3 months or so, you'd have to dump/restore the entire db and you'd be golden for a while again. After the dump/restore process things seemed much snappier too, and vacuum ran almost instantly. I haven't verified if this problem still occurs in 7.2.x, using vacuum full. Tim From pgman@candle.pha.pa.us Sat Nov 2 03:48:12 2002 Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id A03B8475AF8 for ; Fri, 1 Nov 2002 22:48:12 -0500 (EST) Received: from candle.pha.pa.us (momjian.navpoint.com [207.106.42.251]) by postgresql.org (Postfix) with ESMTP id D5B31475A7F for ; Fri, 1 Nov 2002 22:48:10 -0500 (EST) Received: (from pgman@localhost) by candle.pha.pa.us (8.11.6/8.10.1) id gA23m0w10244; Fri, 1 Nov 2002 22:48:00 -0500 (EST) From: Bruce Momjian Message-Id: <200211020348.gA23m0w10244@candle.pha.pa.us> Subject: Re: Different size in the DATA directory In-Reply-To: <3DC2A578.6040605@perdue.net> To: Tim Perdue Date: Fri, 1 Nov 2002 22:48:00 -0500 (EST) Cc: =?ISO-8859-1?Q?Maur=EDcio_Sessue_Otta?= , pgsql-sql@postgresql.org X-Mailer: ELM [version 2.4ME+ PL99 (25)] MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200211/29 X-Sequence-Number: 10171 Tim Perdue wrote: > I had this same problem with 7.1.x, even doing full vacuums on > SourceForge.net last year. > > I assumed after a while that there was some hidden bug where file bloat > occurred, despite the vacuum. After 3 months or so, you'd have to > dump/restore the entire db and you'd be golden for a while again. After > the dump/restore process things seemed much snappier too, and vacuum ran > almost instantly. > > I haven't verified if this problem still occurs in 7.2.x, using vacuum full. Tim, I guess your problem is dead index pages that can't be reclaimed, and it isn't fixed in 7.3. Only REINDEX fixes it, and we have a /contrib/reindexdb script in 7.3. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073 From fduch@antar.bryansk.ru Sat Nov 2 14:33:49 2002 Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id EF65C47657E for ; Sat, 2 Nov 2002 09:33:49 -0500 (EST) Received: from dyatel.antar.bryansk.ru (dyatel.antar.bryansk.ru [195.239.214.85]) by postgresql.org (Postfix) with ESMTP id 149AB476380 for ; Sat, 2 Nov 2002 09:33:43 -0500 (EST) Received: from drweb by dyatel.antar.bryansk.ru with drweb-scanned (Exim 4.10) id 187zLH-000Bsu-00; Sat, 02 Nov 2002 17:33:39 +0300 Received: from fduch by dyatel.antar.bryansk.ru with local (Exim 4.10) id 187zLD-000Bsf-00; Sat, 02 Nov 2002 17:33:35 +0300 Date: Sat, 2 Nov 2002 17:33:35 +0300 From: "Alexander M. Pravking" To: Bruce Momjian Cc: Tim Perdue , Maur?cio Sessue Otta , pgsql-sql@postgresql.org Subject: Re: Different size in the DATA directory Message-ID: <20021102143335.GC13252@dyatel.antar.bryansk.ru> References: <3DC2A578.6040605@perdue.net> <200211020348.gA23m0w10244@candle.pha.pa.us> Mime-Version: 1.0 Content-Type: text/plain; charset=koi8-r Content-Disposition: inline In-Reply-To: <200211020348.gA23m0w10244@candle.pha.pa.us> User-Agent: Mutt/1.4i X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200211/33 X-Sequence-Number: 10175 On Fri, Nov 01, 2002 at 10:48:00PM -0500, Bruce Momjian wrote: > Tim, I guess your problem is dead index pages that can't be reclaimed, > and it isn't fixed in 7.3. Only REINDEX fixes it, and we have a > /contrib/reindexdb script in 7.3. As I see, contrib/reindexdb requires perl for commandline procesing. I don't think it's a good idea, since e.g. FreeBSD 5.0-CURRENT have no perl in standard distribution. Thomas, why not to use sed? -- Fduch M. Pravking From pgman@candle.pha.pa.us Sun Nov 3 01:20:31 2002 Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 26A8A47646B for ; Sat, 2 Nov 2002 20:20:31 -0500 (EST) Received: from candle.pha.pa.us (momjian.navpoint.com [207.106.42.251]) by postgresql.org (Postfix) with ESMTP id 2A06F47646A for ; Sat, 2 Nov 2002 20:20:29 -0500 (EST) Received: (from pgman@localhost) by candle.pha.pa.us (8.11.6/8.10.1) id gA31K4N00212; Sat, 2 Nov 2002 20:20:04 -0500 (EST) From: Bruce Momjian Message-Id: <200211030120.gA31K4N00212@candle.pha.pa.us> Subject: Re: Different size in the DATA directory In-Reply-To: <20021102143335.GC13252@dyatel.antar.bryansk.ru> To: "Alexander M. Pravking" Date: Sat, 2 Nov 2002 20:20:04 -0500 (EST) Cc: Tim Perdue , "Maur?cio Sessue Otta" , pgsql-sql@postgresql.org X-Mailer: ELM [version 2.4ME+ PL99 (25)] MIME-Version: 1.0 Content-Type: multipart/mixed; boundary=ELM1036286404-15342-0_ Content-Transfer-Encoding: 7bit X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200211/36 X-Sequence-Number: 10178 --ELM1036286404-15342-0_ Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII Alexander M. Pravking wrote: > On Fri, Nov 01, 2002 at 10:48:00PM -0500, Bruce Momjian wrote: > > Tim, I guess your problem is dead index pages that can't be reclaimed, > > and it isn't fixed in 7.3. Only REINDEX fixes it, and we have a > > /contrib/reindexdb script in 7.3. > > As I see, contrib/reindexdb requires perl for commandline > procesing. I don't think it's a good idea, since > e.g. FreeBSD 5.0-CURRENT have no perl in standard distribution. > > Thomas, why not to use sed? No perl? I am no perl guy, but I assumed everyone had that already. I just looked at the code, and yes, it should use sed rather than perl, especially since it is using it just for processing command line args. Seems it is a problem/bug for you. Patch applied to use sed rather than perl. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073 --ELM1036286404-15342-0_ Content-Transfer-Encoding: 7bit Content-Type: text/plain Content-Disposition: inline; filename="/bjm/diff" Index: contrib/reindexdb/reindexdb =================================================================== RCS file: /cvsroot/pgsql-server/contrib/reindexdb/reindexdb,v retrieving revision 1.2 diff -c -c -r1.2 reindexdb *** contrib/reindexdb/reindexdb 18 Oct 2002 18:41:20 -0000 1.2 --- contrib/reindexdb/reindexdb 3 Nov 2002 01:19:21 -0000 *************** *** 111,118 **** dbname="$2" shift ;; ! -d*|--dbname=*) ! dbname=`echo $1 | perl -pn -e 's/^--?d(bname=)?//'` ;; # Reindex specific Table. Disables index reindexing. --- 111,121 ---- dbname="$2" shift ;; ! -d*) ! dbname=`echo "$1" | sed 's/^-d/'` ! ;; ! --dbname=*) ! dbname=`echo "$1" | sed 's/^--dbname=//'` ;; # Reindex specific Table. Disables index reindexing. *************** *** 120,127 **** table="$2" shift ;; ! -t*|--table=*) ! table=`echo $1 | perl -pn -e 's/^--?t(able=)?//'` ;; # Reindex specific index. Disables table reindexing. --- 123,133 ---- table="$2" shift ;; ! -t*) ! table=`echo "$1" | sed 's/^-t//'` ! ;; ! --table=*) ! table=`echo "$1" | sed 's/^--table=//'` ;; # Reindex specific index. Disables table reindexing. *************** *** 129,136 **** index="$2" shift ;; ! -i*|--index=*) ! index=`echo $1 | perl -pn -e 's/^--?i(ndex=)?//'` ;; # Yeah, no options? Whine, and show usage. --- 135,145 ---- index="$2" shift ;; ! -i*) ! index=`echo "$1" | sed 's/^-i//'` ! ;; ! --index=*) ! index=`echo "$1" | sed 's/^--index=//'` ;; # Yeah, no options? Whine, and show usage. --ELM1036286404-15342-0_-- From fduch@antar.bryansk.ru Sun Nov 3 12:19:39 2002 Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id B287E475B07 for ; Sun, 3 Nov 2002 07:19:39 -0500 (EST) Received: from dyatel.antar.bryansk.ru (dyatel.antar.bryansk.ru [195.239.214.85]) by postgresql.org (Postfix) with ESMTP id 2091F4758DC for ; Sun, 3 Nov 2002 07:19:39 -0500 (EST) Received: from drweb by dyatel.antar.bryansk.ru with drweb-scanned (Exim 4.10) id 188Jj5-0006vw-00; Sun, 03 Nov 2002 15:19:35 +0300 Received: from fduch by dyatel.antar.bryansk.ru with local (Exim 4.10) id 188Jj2-0006vl-00; Sun, 03 Nov 2002 15:19:32 +0300 Date: Sun, 3 Nov 2002 15:19:32 +0300 From: "Alexander M. Pravking" To: Bruce Momjian Cc: Tim Perdue , Maur?cio Sessue Otta , pgsql-sql@postgresql.org Subject: Re: Different size in the DATA directory Message-ID: <20021103121932.GA26315@dyatel.antar.bryansk.ru> References: <20021102143335.GC13252@dyatel.antar.bryansk.ru> <200211030120.gA31K4N00212@candle.pha.pa.us> Mime-Version: 1.0 Content-Type: text/plain; charset=koi8-r Content-Disposition: inline In-Reply-To: <200211030120.gA31K4N00212@candle.pha.pa.us> User-Agent: Mutt/1.4i X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200211/38 X-Sequence-Number: 10180 On Sat, Nov 02, 2002 at 08:20:04PM -0500, Bruce Momjian wrote: > Alexander M. Pravking wrote: > > As I see, contrib/reindexdb requires perl for commandline > > procesing. I don't think it's a good idea, since > > e.g. FreeBSD 5.0-CURRENT have no perl in standard distribution. > > > > Thomas, why not to use sed? > > No perl? I am no perl guy, but I assumed everyone had that already. > > I just looked at the code, and yes, it should use sed rather than perl, > especially since it is using it just for processing command line args. > > Seems it is a problem/bug for you. Patch applied to use sed rather than > perl. Well, I DO use perl, so it's not a problem for me :) But I see many people on these lists that do not. Thanks, Bruce. -- Fduch M. Pravking