pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feeddatabase size much bigger than tablespaces on filesystem
7+ messages / 4 participants
[nested] [flat]
* database size much bigger than tablespaces on filesystem
@ 2011-09-14 11:03 Rob Audenaerde <Rob.Audenaerde@Valuecare.nl>
2011-09-14 11:55 ` Re: database size much bigger than tablespaces on filesystem Gabriele Bartolini <Gabriele.Bartolini@2ndQuadrant.it>
2011-09-14 12:00 ` Re: database size much bigger than tablespaces on filesystem Guillaume Lelarge <guillaume@lelarge.info>
0 siblings, 2 replies; 7+ messages in thread
From: Rob Audenaerde @ 2011-09-14 11:03 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: database size much bigger than tablespaces on filesystem
2011-09-14 11:03 database size much bigger than tablespaces on filesystem Rob Audenaerde <Rob.Audenaerde@Valuecare.nl>
@ 2011-09-14 11:55 ` Gabriele Bartolini <Gabriele.Bartolini@2ndQuadrant.it>
1 sibling, 0 replies; 7+ messages in thread
From: Gabriele Bartolini @ 2011-09-14 11:55 UTC (permalink / raw)
To: Rob Audenaerde <Rob.Audenaerde@Valuecare.nl>; +Cc: pgsql-admin
Hi,
On Wed, 14 Sep 2011 04:03:45 -0700, Rob Audenaerde
<Rob.Audenaerde@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.Bartolini@2ndQuadrant.it - www.2ndQuadrant.it
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: database size much bigger than tablespaces on filesystem
2011-09-14 11:03 database size much bigger than tablespaces on filesystem Rob Audenaerde <Rob.Audenaerde@Valuecare.nl>
@ 2011-09-14 12:00 ` Guillaume Lelarge <guillaume@lelarge.info>
2011-09-14 15:21 ` Re: database size much bigger than tablespaces on filesystem Rob Audenaerde <Rob.Audenaerde@Valuecare.nl>
1 sibling, 1 reply; 7+ messages in thread
From: Guillaume Lelarge @ 2011-09-14 12:00 UTC (permalink / raw)
To: Rob Audenaerde <Rob.Audenaerde@Valuecare.nl>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: database size much bigger than tablespaces on filesystem
2011-09-14 11:03 database size much bigger than tablespaces on filesystem Rob Audenaerde <Rob.Audenaerde@Valuecare.nl>
2011-09-14 12:00 ` Re: database size much bigger than tablespaces on filesystem Guillaume Lelarge <guillaume@lelarge.info>
@ 2011-09-14 15:21 ` Rob Audenaerde <Rob.Audenaerde@Valuecare.nl>
2011-09-14 15:47 ` Re: database size much bigger than tablespaces on filesystem Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Rob Audenaerde @ 2011-09-14 15:21 UTC (permalink / raw)
To: Guillaume Lelarge <guillaume@lelarge.info>; +Cc: pgsql-admin
>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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: database size much bigger than tablespaces on filesystem
2011-09-14 11:03 database size much bigger than tablespaces on filesystem Rob Audenaerde <Rob.Audenaerde@Valuecare.nl>
2011-09-14 12:00 ` Re: database size much bigger than tablespaces on filesystem Guillaume Lelarge <guillaume@lelarge.info>
2011-09-14 15:21 ` Re: database size much bigger than tablespaces on filesystem Rob Audenaerde <Rob.Audenaerde@Valuecare.nl>
@ 2011-09-14 15:47 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-09-14 16:04 ` Re: database size much bigger than tablespaces on filesystem Rob Audenaerde <Rob.Audenaerde@Valuecare.nl>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2011-09-14 15:47 UTC (permalink / raw)
To: Rob Audenaerde <Rob.Audenaerde@Valuecare.nl>; +Cc: Guillaume Lelarge <guillaume@lelarge.info>; pgsql-admin
Rob Audenaerde <Rob.Audenaerde@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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: database size much bigger than tablespaces on filesystem
2011-09-14 11:03 database size much bigger than tablespaces on filesystem Rob Audenaerde <Rob.Audenaerde@Valuecare.nl>
2011-09-14 12:00 ` Re: database size much bigger than tablespaces on filesystem Guillaume Lelarge <guillaume@lelarge.info>
2011-09-14 15:21 ` Re: database size much bigger than tablespaces on filesystem Rob Audenaerde <Rob.Audenaerde@Valuecare.nl>
2011-09-14 15:47 ` Re: database size much bigger than tablespaces on filesystem Tom Lane <tgl@sss.pgh.pa.us>
@ 2011-09-14 16:04 ` Rob Audenaerde <Rob.Audenaerde@Valuecare.nl>
2011-09-14 21:05 ` Re: database size much bigger than tablespaces on filesystem Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Rob Audenaerde @ 2011-09-14 16:04 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Guillaume Lelarge <guillaume@lelarge.info>; pgsql-admin
>>>> 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: database size much bigger than tablespaces on filesystem
2011-09-14 11:03 database size much bigger than tablespaces on filesystem Rob Audenaerde <Rob.Audenaerde@Valuecare.nl>
2011-09-14 12:00 ` Re: database size much bigger than tablespaces on filesystem Guillaume Lelarge <guillaume@lelarge.info>
2011-09-14 15:21 ` Re: database size much bigger than tablespaces on filesystem Rob Audenaerde <Rob.Audenaerde@Valuecare.nl>
2011-09-14 15:47 ` Re: database size much bigger than tablespaces on filesystem Tom Lane <tgl@sss.pgh.pa.us>
2011-09-14 16:04 ` Re: database size much bigger than tablespaces on filesystem Rob Audenaerde <Rob.Audenaerde@Valuecare.nl>
@ 2011-09-14 21:05 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 7+ messages in thread
From: Tom Lane @ 2011-09-14 21:05 UTC (permalink / raw)
To: Rob Audenaerde <Rob.Audenaerde@Valuecare.nl>; +Cc: Guillaume Lelarge <guillaume@lelarge.info>; pgsql-admin
Rob Audenaerde <Rob.Audenaerde@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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2011-09-14 21:05 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-09-14 11:03 database size much bigger than tablespaces on filesystem Rob Audenaerde <Rob.Audenaerde@Valuecare.nl>
2011-09-14 11:55 ` Gabriele Bartolini <Gabriele.Bartolini@2ndQuadrant.it>
2011-09-14 12:00 ` Guillaume Lelarge <guillaume@lelarge.info>
2011-09-14 15:21 ` Rob Audenaerde <Rob.Audenaerde@Valuecare.nl>
2011-09-14 15:47 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-09-14 16:04 ` Rob Audenaerde <Rob.Audenaerde@Valuecare.nl>
2011-09-14 21:05 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox