| Lists: | pgsql-sql |
|---|
| From: | Maurício Sessue Otta <mauricio(at)cristorei(dot)com(dot)br> |
|---|---|
| To: | <pgsql-sql(at)postgresql(dot)org> |
| Subject: | Different size in the DATA directory |
| Date: | 2002-11-01 14:09:12 |
| Message-ID: | 004901c281b0$411de2a0$cba0a2c8@cristorei.com.br |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | 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
| From: | Maurício Sessue Otta <mauricio(at)cristorei(dot)com(dot)br> |
|---|---|
| To: | "dima" <_pppp(at)mail(dot)ru> |
| Cc: | <pgsql-sql(at)postgresql(dot)org> |
| Subject: | Re: Different size in the DATA directory |
| Date: | 2002-11-01 14:25:43 |
| Message-ID: | 008b01c281b2$901bd4a0$cba0a2c8@cristorei.com.br |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | 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?
|
|
| From: | dima <_pppp(at)mail(dot)ru> |
|---|---|
| To: | Maurício Sessue Otta <mauricio(at)cristorei(dot)com(dot)br> |
| Cc: | pgsql-sql(at)postgresql(dot)org |
| Subject: | Re: Different size in the DATA directory |
| Date: | 2002-11-01 15:22:36 |
| Message-ID: | 3DC29C3C.8030803@mail.ru |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | 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?
| From: | Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> |
|---|---|
| To: | Maurício Sessue Otta <mauricio(at)cristorei(dot)com(dot)br> |
| Cc: | dima <_pppp(at)mail(dot)ru>, <pgsql-sql(at)postgresql(dot)org> |
| Subject: | Re: Different size in the DATA directory |
| Date: | 2002-11-01 15:32:49 |
| Message-ID: | 20021101073110.X58632-100000@megazone23.bigpanda.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-sql |
On Fri, 1 Nov 2002, [iso-8859-1] Maurcio 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 <tim(at)perdue(dot)net> |
|---|---|
| To: | Maurício Sessue Otta <mauricio(at)cristorei(dot)com(dot)br>, pgsql-sql(at)postgresql(dot)org |
| Subject: | Re: Different size in the DATA directory |
| Date: | 2002-11-01 16:02:00 |
| Message-ID: | 3DC2A578.6040605@perdue.net |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | 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
| From: | Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> |
|---|---|
| To: | Tim Perdue <tim(at)perdue(dot)net> |
| Cc: | Maurício Sessue Otta <mauricio(at)cristorei(dot)com(dot)br>, pgsql-sql(at)postgresql(dot)org |
| Subject: | Re: Different size in the DATA directory |
| Date: | 2002-11-02 03:48:00 |
| Message-ID: | 200211020348.gA23m0w10244@candle.pha.pa.us |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | 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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
| From: | "Alexander M(dot) Pravking" <fduch(at)antar(dot)bryansk(dot)ru> |
|---|---|
| To: | Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> |
| Cc: | Tim Perdue <tim(at)perdue(dot)net>, Maur?cio Sessue Otta <mauricio(at)cristorei(dot)com(dot)br>, pgsql-sql(at)postgresql(dot)org |
| Subject: | Re: Different size in the DATA directory |
| Date: | 2002-11-02 14:33:35 |
| Message-ID: | 20021102143335.GC13252@dyatel.antar.bryansk.ru |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | 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
| From: | Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> |
|---|---|
| To: | "Alexander M(dot) Pravking" <fduch(at)antar(dot)bryansk(dot)ru> |
| Cc: | Tim Perdue <tim(at)perdue(dot)net>, "Maur?cio Sessue Otta" <mauricio(at)cristorei(dot)com(dot)br>, pgsql-sql(at)postgresql(dot)org |
| Subject: | Re: Different size in the DATA directory |
| Date: | 2002-11-03 01:20:04 |
| Message-ID: | 200211030120.gA31K4N00212@candle.pha.pa.us |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | 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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
| Attachment | Content-Type | Size |
|---|---|---|
| unknown_filename | text/plain | 1.8 KB |
| From: | "Alexander M(dot) Pravking" <fduch(at)antar(dot)bryansk(dot)ru> |
|---|---|
| To: | Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> |
| Cc: | Tim Perdue <tim(at)perdue(dot)net>, Maur?cio Sessue Otta <mauricio(at)cristorei(dot)com(dot)br>, pgsql-sql(at)postgresql(dot)org |
| Subject: | Re: Different size in the DATA directory |
| Date: | 2002-11-03 12:19:32 |
| Message-ID: | 20021103121932.GA26315@dyatel.antar.bryansk.ru |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | 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