Re: [ADMIN] database size much bigger than tablespaces on filesystem

2011-09-14 Thread Gabriele Bartolini 

Hi,

On Wed, 14 Sep 2011 04:03:45 -0700, Rob Audenaerde 
rob.audenae...@valuecare.nl wrote:
Is this corruption of the database? Or are there ways to 'fix' this 
oddity?


Try VACUUM ANALYSE and repeat the operation.

Thanks,
Gabriele

--
 Gabriele Bartolini - 2ndQuadrant Italia
 PostgreSQL Training, Services and Support
 gabriele.bartol...@2ndquadrant.it - www.2ndQuadrant.it

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: [ADMIN] database size much bigger than tablespaces on filesystem

2011-09-14 Thread Guillaume Lelarge 
On Wed, 2011-09-14 at 04:03 -0700, Rob Audenaerde wrote:
 I have a Postgresql 8.3 instance with tablespaces totalling on about 74G. 
 This is fine.
 
 But if I ask Postgresql how big my database is, I get a (unexpected) large 
 answer: 595 GB.
 
 This seems very strange. Disk I/O tests on the system are in the 'normal' 
 range, but queries are slower than they used to be.
 
 Is this corruption of the database? Or are there ways to 'fix' this oddity?
 
 Btw:
 
 I check the tablespace size by the os:
 
 /usr/local/pgsql/data/tblspaces/du -c -h
 74G total 
 
 All tablespaces are there. I check this by the \db command in pgsql
 
 I check te database size like this:
 
 select pg_size_pretty(pg_database_size('database')) 
 595 GB

You should also check the space held in $PGDATA/base.


-- 
Guillaume
  http://blog.guillaume.lelarge.info
  http://www.dalibo.com


-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: [ADMIN] database size much bigger than tablespaces on filesystem

2011-09-14 Thread Rob Audenaerde 
On Wed, 2011-09-14 at 04:03 -0700, Rob Audenaerde wrote:
 I have a Postgresql 8.3 instance with tablespaces totalling on about 74G. 
 This is fine.

 But if I ask Postgresql how big my database is, I get a (unexpected) large 
 answer: 595 GB.

 This seems very strange. Disk I/O tests on the system are in the 'normal' 
 range, but queries are slower than they used to be.

 Is this corruption of the database? Or are there ways to 'fix' this oddity?

 Btw:

 I check the tablespace size by the os:

 /usr/local/pgsql/data/tblspaces/du -c -h
 74G total

 All tablespaces are there. I check this by the \db command in pgsql

 I check te database size like this:

 select pg_size_pretty(pg_database_size('database'))
 595 GB

 You should also check the space held in $PGDATA/base.

I did. It is only 320 MB.


-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: [ADMIN] database size much bigger than tablespaces on filesystem

2011-09-14 Thread Tom Lane 
Rob Audenaerde rob.audenae...@valuecare.nl writes:
 I check te database size like this:
 select pg_size_pretty(pg_database_size('database'))
 595 GB

 You should also check the space held in $PGDATA/base.

 I did. It is only 320 MB.

Bizarre.  Try breaking the results down table-by-table to see if you can
find where the discrepancy is.

regards, tom lane

-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: [ADMIN] database size much bigger than tablespaces on filesystem

2011-09-14 Thread Rob Audenaerde 
 I check te database size like this:
 select pg_size_pretty(pg_database_size('database'))
 595 GB

 You should also check the space held in $PGDATA/base.

 I did. It is only 320 MB.

Bizarre.  Try breaking the results down table-by-table to see if you can
find where the discrepancy is.

   regards, tom lane

It gets stranger. I try this:

select 
  tablename
, pg_relation_size(tablename) 
, pg_size_pretty(pg_relation_size(tablename) ) as relsize
, pg_size_pretty(pg_total_relation_size(tablename) ) as disksize
, pg_total_relation_size(tablename)
from pg_tables where schemaname  'information_schema'
order by 2 desc

And all the tables report a 'disksize' larger than the 'relsize', which seems 
natural. The biggest table (relsize) is around 5 GB. 
I wonder where the rest of the size comes from?

-Rob
-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: [ADMIN] database size much bigger than tablespaces on filesystem

2011-09-14 Thread Tom Lane 
Rob Audenaerde rob.audenae...@valuecare.nl writes:
 It gets stranger. I try this:

 select 
   tablename
 , pg_relation_size(tablename) 
 , pg_size_pretty(pg_relation_size(tablename) ) as relsize
 , pg_size_pretty(pg_total_relation_size(tablename) ) as disksize
 , pg_total_relation_size(tablename)
 from pg_tables where schemaname  'information_schema'
 order by 2 desc

 And all the tables report a 'disksize' larger than the 'relsize',
 which seems natural. The biggest table (relsize) is around 5 GB. 

pg_relation_size isn't going to count indexes nor toast tables, and
toast in particular could be where a lot of the space is.  I'd suggest
sorting by pg_total_relation_size to see if anything jumps out at you.

Another problem with the above query is that it supposes that there are
no similarly-named tables in different schemas.  If there are, you'll
get multiple reports of the size of the one that is visible in your
search path, and no reports for the others.  If I were doing this,
I'd not rely on pg_tables but look at pg_class directly so I could use
the OID, something like

select relname, pg_total_relation_size(c.oid)
from pg_class c where relkind = 'r'
order by 2 desc

regards, tom lane

-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin