By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,834 Members | 2,265 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,834 IT Pros & Developers. It's quick & easy.

data directory growing huge

P: n/a
Hello,

it is very possible that this is a well described problem, but I have
not been able to find the solution.

On two production server (7.2rc2) of ours the data directory is growing
to very large sizes while the data that is actually in the db's isnt 1.
that large and 2. growing.

The databases see a fairly limited/constant use at the moment. The data
they contain is close to real-time information and as such they are
continuously updated with new data. The amount of queries executed is
low or very low (this will change in the future).

To prevent the database from growing to unmanageable size it's being
emptied and vacuumed twice a day. This is only a "vacuum" btw, not a
"vacuum analyze". I did an analyze by hand to get the planner to use a
plan suited for 100k+ tables as before that performance was horrid.

The problem is that the data directories have reached sizes of 4.1 and
4.5 Gigabyte respectively. A recreation of the 4.1Gb db after a pg_dump
on a different system yields a total of about 460Mb in ALL databases on
that system, the dump file itself is 10Mb. Clearly the 4Gb is a bit
wastefull...

Next to the daily automatic vacuum's I have done a "vacuum full" by hand
(this took a few hours) but this has not resulted in any reduction in
file sizes.

the bulk of the space is occupied by a dozen huge files in base/16559,
all sufficiently meaninglessly named: 72646, 72653.1 etc.

As vacuuming doesnt help, whats up? Could it be that there are lots of
stuck transactions keeping huge amounts of old&removed rows on disk
through MVCC or something?

thanks for any help,

Esger

--
NeoMail - Webmail that doesn't suck... as much.
http://neomail.sourceforge.net

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 11 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Esger Abbink wrote:
The databases see a fairly limited/constant use at the moment. The data
they contain is close to real-time information and as such they are
continuously updated with new data. The amount of queries executed is
low or very low (this will change in the future).
No wonder data is growing real fast. If it is updated in real time it is going
to generate dead tuples like crazy..
The problem is that the data directories have reached sizes of 4.1 and
4.5 Gigabyte respectively. A recreation of the 4.1Gb db after a pg_dump
on a different system yields a total of about 460Mb in ALL databases on
that system, the dump file itself is 10Mb. Clearly the 4Gb is a bit
wastefull...

Next to the daily automatic vacuum's I have done a "vacuum full" by hand
(this took a few hours) but this has not resulted in any reduction in
file sizes.
Hmm.. Vacuum full should have done the trick..
the bulk of the space is occupied by a dozen huge files in base/16559,
all sufficiently meaninglessly named: 72646, 72653.1 etc.
You can find the corresponding table names by querying pg_class..
As vacuuming doesnt help, whats up? Could it be that there are lots of
stuck transactions keeping huge amounts of old&removed rows on disk
through MVCC or something?


If you can take down the database for some time, you can dump the entire
database using pg_dump, drop the database and recreate from the dump. Surely it
would take lot less than few hours.

And I think you despartely need pg_autovacuum.. A daemon that vacuums in real
time according to requirement. It's in CVS head and works with 7.3/7.4. Up your
max fsm setting appropriately too.. That should help keeping your database size
in check..

HTH

Shridhar

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 12 '05 #2

P: n/a
"Esger Abbink" <pg*******@bumblebeast.com> writes:
On two production server (7.2rc2) of ours the data directory is growing
to very large sizes while the data that is actually in the db's isnt 1.
that large and 2. growing.


You're running a release candidate of 7.2? That's pretty old and has
known bugs...

Your problem, however, is probably index bloat, which is not fixed by
VACUUM in the version you're running. If you REINDEX you should see
storage usage go back to normal.

The upcoming 7.4 should suffer much less from the index bloat problem.

-Doug

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 12 '05 #3

P: n/a
"Esger Abbink" <pg*******@bumblebeast.com> writes:
the bulk of the space is occupied by a dozen huge files in base/16559,
all sufficiently meaninglessly named: 72646, 72653.1 etc.
You could find out what they are by consulting pg_class.relfilenode
(or see the contrib/oid2name utility). But my bet is that they are
indexes and you need to REINDEX them to recover the space.
On two production server (7.2rc2) of ours


[ blanch ] You're running a production server on 7.2rc2? Still?
Get thee to 7.2.4, at least. We do not make dot-releases for idle
amusement.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.