Skip site navigation (1) Skip section navigation (2)

Why the table allocates so much Space ?

From: "Roth, Michael J(dot)" <MichaelRoth(at)schmuecker(dot)de>
To: "'pgsql-novice(at)postgresql(dot)org'" <pgsql-novice(at)postgresql(dot)org>
Subject: Why the table allocates so much Space ?
Date: 2001-10-08 08:43:56
Message-ID: C35FA50F3392D5119B450002A551B7AD048AB6@OFFICE-F2 (view raw or flat)
Thread:
Lists: pgsql-novice
Hi,

we're using PostgreSQL 7.1.3 (after an upgrade from 7.0.3).
Now we lost good performance on our Server, all the
db-operations take a long time.

OK, we try vacuumdb and see the following:

.....
NOTICE:  Analyzing...
NOTICE:  --Relation commuser--
NOTICE:  Pages 1222: Changed 0, reaped 1129, Empty 0, New 0; Tup 5251: Vac
63103
, Keep/VTL 0/0, Crash 0, UnUsed 253, MinLen 124, MaxLen 308; Re-using:
Free/Avai
l. Space 8966056/8959140; EndEmpty/Avail. Pages 0/1128. CPU 0.03s/0.03u sec.
NOTICE:  Rel commuser: Pages: 1222 --> 94; Tuple(s) moved: 5251. CPU
0.38s/0.23u
 sec.
.....

After this, here's the "normal space" and the performance is ok:

.....
NOTICE:  Analyzing...
NOTICE:  --Relation commuser--
NOTICE:  Pages 94: Changed 0, reaped 86, Empty 0, New 0; Tup 5251: Vac 0,
Keep/V
TL 0/0, Crash 0, UnUsed 262, MinLen 124, MaxLen 308; Re-using: Free/Avail.
Space
 6784/0; EndEmpty/Avail. Pages 0/0. CPU 0.00s/0.00u sec.
.....

But only a few Minutes later, we lost performance again and vaccumdb say's:

.....
NOTICE:  --Relation commuser--
NOTICE:  Pages 283: Changed 0, reaped 190, Empty 0, New 0; Tup 5251: Vac
10582,
Keep/VTL 0/0, Crash 0, UnUsed 251, MinLen 124, MaxLen 308; Re-using:
Free/Avail.
 Space 1502280/1495404; EndEmpty/Avail. Pages 0/189. CPU 0.02s/0.00u sec.
NOTICE:  Rel commuser: Pages: 283 --> 94; Tuple(s) moved: 5251. CPU
0.05s/0.06u
sec.
.....

So we _must_ vacuum the db all 10-15 minutes.
What's going wrong here ?

Regards,
M. Roth



--
Computers are like airconditioners: They stop
working properly if you open windows.
----------------------------------------------------
Michael J. Roth     mailto:MichaelRoth(at)schmuecker(dot)de 
Tel: 069/57005-421, Fax: 069/57005-800
Schm├╝cker&Partner GmbH      http://www.schmuecker.de 
Lurgiallee 14, 60539 Frankfurt            
----------------------------------------------------


pgsql-novice by date

Next:From: Duncan Adams (DNS)Date: 2001-10-08 10:03:54
Subject: sequence last_value
Previous:From: Patrick CoulombeDate: 2001-10-07 19:52:55
Subject: Re: from 6.5 > 7.1, cannot pg_dumpall.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group