Thread: Different size in the DATA directory
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
> 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?
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? | |
On Fri, 1 Nov 2002, [iso-8859-1] Maur�cio 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.
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
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, Pennsylvania19073
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
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 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.
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