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

Big difference in databasesize compared with disksize

From: Bernhard Schrader <bernhard(dot)schrader(at)innogames(dot)de>
To: pgsql-admin(at)postgresql(dot)org
Subject: Big difference in databasesize compared with disksize
Date: 2012-02-14 13:22:32
Message-ID: 4F3A6018.3000902@innogames.de (view raw or flat)
Thread:
Lists: pgsql-admin
Hi all,

Problem:
If I use
SELECT pg_size_pretty(pg_database_size('dbname')) As fulldbsize;

  fulldbsize
------------
  6455 MB
(1 row)

If I compare this output with
/var/lib/postgresql/9.0/main/base/[DBFOLDER]
I have a difference of 3 GB, which I think is not normal.

To make a little bit clearer what i mean:

/var/lib/postgresql/9.0/main/base/43169# du --apparent-size -h
6.4G	.

/var/lib/postgresql/9.0/main/base/43169# du -sh
9.4G	.

Is such a big fragmentation a normal behavior? It's splitted to several 
Filenodes.

I also made a "xfs_fsr" to defrag all of this, i gathered around 1 GB 
back ( would be more if the tables wouldn't be written in this time ) 
But within some hours the fragmentation was back..

There is also no big dead tuple count in this database, it's getting a 
vacuum analyze every night.

Right now i am out of ideas... What do you think i could do?
If you need more information i can provide.

Environment:
- OS: Debian Lenny
- Disksize: 15GB
- Filesystem: XFS
- Psql: 9.0.3

thanks in advance.

regards
Bernhard


Responses

pgsql-admin by date

Next:From: Kevin GrittnerDate: 2012-02-14 14:34:13
Subject: Re: Real time monitoring of pg_dump progress through web apps
Previous:From: Anup GreensDate: 2012-02-14 13:18:42
Subject: Real time monitoring of pg_dump progress through web apps

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