pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDifferent size in the DATA directory
9+ messages / 6 participants
[nested] [flat]
* Different size in the DATA directory
@ 2002-11-01 14:09 Maurício Sessue Otta <mauricio@cristorei.com.br>
0 siblings, 2 replies; 9+ messages in thread
From: Maurício Sessue Otta @ 2002-11-01 14:09 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Different size in the DATA directory
@ 2002-11-01 14:25 Maurício Sessue Otta <mauricio@cristorei.com.br>
parent: dima <_pppp@mail.ru>
0 siblings, 1 reply; 9+ messages in thread
From: Maurício Sessue Otta @ 2002-11-01 14:25 UTC (permalink / raw)
To: dima <_pppp@mail.ru>; +Cc: pgsql-sql
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?
|
|
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Different size in the DATA directory
@ 2002-11-01 15:22 dima <_pppp@mail.ru>
parent: Maurício Sessue Otta <mauricio@cristorei.com.br>
1 sibling, 1 reply; 9+ messages in thread
From: dima @ 2002-11-01 15:22 UTC (permalink / raw)
To: Maurício Sessue Otta <mauricio@cristorei.com.br>; +Cc: pgsql-sql
> 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?
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Different size in the DATA directory
@ 2002-11-01 15:32 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Maurício Sessue Otta <mauricio@cristorei.com.br>
0 siblings, 0 replies; 9+ messages in thread
From: Stephan Szabo @ 2002-11-01 15:32 UTC (permalink / raw)
To: Maurício Sessue Otta <mauricio@cristorei.com.br>; +Cc: dima <_pppp@mail.ru>; pgsql-sql
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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Different size in the DATA directory
@ 2002-11-01 16:02 Tim Perdue <tim@perdue.net>
parent: Maurício Sessue Otta <mauricio@cristorei.com.br>
1 sibling, 1 reply; 9+ messages in thread
From: Tim Perdue @ 2002-11-01 16:02 UTC (permalink / raw)
To: Maurício Sessue Otta <mauricio@cristorei.com.br>; pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Different size in the DATA directory
@ 2002-11-02 03:48 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Tim Perdue <tim@perdue.net>
0 siblings, 1 reply; 9+ messages in thread
From: Bruce Momjian @ 2002-11-02 03:48 UTC (permalink / raw)
To: Tim Perdue <tim@perdue.net>; +Cc: Maurício Sessue Otta <mauricio@cristorei.com.br>; pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Different size in the DATA directory
@ 2002-11-02 14:33 Alexander M. Pravking <fduch@antar.bryansk.ru>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 1 reply; 9+ messages in thread
From: Alexander M. Pravking @ 2002-11-02 14:33 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Tim Perdue <tim@perdue.net>; Maur?cio Sessue Otta <mauricio@cristorei.com.br>; pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Different size in the DATA directory
@ 2002-11-03 01:20 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Alexander M. Pravking <fduch@antar.bryansk.ru>
0 siblings, 1 reply; 9+ messages in thread
From: Bruce Momjian @ 2002-11-03 01:20 UTC (permalink / raw)
To: Alexander M. Pravking <fduch@antar.bryansk.ru>; +Cc: Tim Perdue <tim@perdue.net>; Maur?cio Sessue Otta <mauricio@cristorei.com.br>; pgsql-sql
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
Attachments:
/bjm/diff (text/plain, 1.8K) download
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.
* Re: Different size in the DATA directory
@ 2002-11-03 12:19 Alexander M. Pravking <fduch@antar.bryansk.ru>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 0 replies; 9+ messages in thread
From: Alexander M. Pravking @ 2002-11-03 12:19 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Tim Perdue <tim@perdue.net>; Maur?cio Sessue Otta <mauricio@cristorei.com.br>; pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2002-11-03 12:19 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-11-01 14:09 Different size in the DATA directory Maurício Sessue Otta <mauricio@cristorei.com.br>
2002-11-01 15:22 ` dima <_pppp@mail.ru>
2002-11-01 14:25 ` Maurício Sessue Otta <mauricio@cristorei.com.br>
2002-11-01 15:32 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-11-01 16:02 ` Tim Perdue <tim@perdue.net>
2002-11-02 03:48 ` Bruce Momjian <pgman@candle.pha.pa.us>
2002-11-02 14:33 ` Alexander M. Pravking <fduch@antar.bryansk.ru>
2002-11-03 01:20 ` Bruce Momjian <pgman@candle.pha.pa.us>
2002-11-03 12:19 ` Alexander M. Pravking <fduch@antar.bryansk.ru>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox