Обсуждение: pg_database_size differs from df -s

Hi folks,

I've got an issue I'm not sure I might have a misunderstanding. When
calling

select sum(pg_database_size(datid)) as total_size from pg_stat_database

the result is much bigger than running a df -s over the postgres folder
- Its about factor 5 to 10 depending on database.

My understanding was, pg_database_size is the database size on disc. Am
I misunderstanding the docu here?

Cheers,
Frank

Frank Lanitz <frank@frank.uvena.de> writes:
> I've got an issue I'm not sure I might have a misunderstanding. When
> calling

> select sum(pg_database_size(datid)) as total_size from pg_stat_database

> the result is much bigger than running a df -s over the postgres folder
> - Its about factor 5 to 10 depending on database.

Did you mean "du -s"?

> My understanding was, pg_database_size is the database size on disc. Am
> I misunderstanding the docu here?

For me, pg_database_size gives numbers that match up fairly well with
what "du" says.  I would not expect an exact match, since du probably
knows about filesystem overhead (such as metadata) whereas
pg_database_size does not.  Something's fishy if it's off by any large
factor, though.  Perhaps you have some tables in a nondefault
tablespace, where du isn't seeing them?

            regards, tom lane

Am 06.06.2012 17:49, schrieb Tom Lane:
> Frank Lanitz <frank@frank.uvena.de> writes:
>> I've got an issue I'm not sure I might have a misunderstanding. When
>> calling
>
>> select sum(pg_database_size(datid)) as total_size from pg_stat_database
>
>> the result is much bigger than running a df -s over the postgres folder
>> - Its about factor 5 to 10 depending on database.
>
> Did you mean "du -s"?

Yepp, sure. Was to confused about the two numbers. ;)

>> My understanding was, pg_database_size is the database size on disc. Am
>> I misunderstanding the docu here?
>
> For me, pg_database_size gives numbers that match up fairly well with
> what "du" says.  I would not expect an exact match, since du probably
> knows about filesystem overhead (such as metadata) whereas
> pg_database_size does not.  Something's fishy if it's off by any large
> factor, though.  Perhaps you have some tables in a nondefault
> tablespace, where du isn't seeing them?

Nope. Its a pretty much clean database without any fancy stuff.

Cheers,
Frank

Frank Lanitz <frank@frank.uvena.de> writes:
> Am 06.06.2012 17:49, schrieb Tom Lane:
>> For me, pg_database_size gives numbers that match up fairly well with
>> what "du" says.  I would not expect an exact match, since du probably
>> knows about filesystem overhead (such as metadata) whereas
>> pg_database_size does not.  Something's fishy if it's off by any large
>> factor, though.  Perhaps you have some tables in a nondefault
>> tablespace, where du isn't seeing them?

> Nope. Its a pretty much clean database without any fancy stuff.

Peculiar.  If you want to put some time into it, you could try comparing
sizes table-by-table to see if you can isolate where the discrepancy is.

The only reason I can think of for du to report a size smaller than the
nominal file length (which is which the pg_xxx_size functions look at)
is if the file contains unallocated "holes".  That really shouldn't ever
happen with PG tables though.

            regards, tom lane

On 6 Jun 2012, at 16:33, Frank Lanitz wrote:

> the result is much bigger than running a df -s over the postgres folder
> - Its about factor 5 to 10 depending on database.


Is your du reporting sizes in Bytes or blocks or ...?

Alban Hertroys

--
The scale of a problem often equals the size of an ego.

On Wed, 2012-06-06 at 18:46 +0200, Julien Rouhaud wrote:
> On Wed, Jun 6, 2012 at 6:28 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> > Frank Lanitz <frank@frank.uvena.de> writes:
> > > Am 06.06.2012 17:49, schrieb Tom Lane:
> > >> For me, pg_database_size gives numbers that match up fairly well with
> > >> what "du" says.  I would not expect an exact match, since du probably
> > >> knows about filesystem overhead (such as metadata) whereas
> > >> pg_database_size does not.  Something's fishy if it's off by any large
> > >> factor, though.  Perhaps you have some tables in a nondefault
> > >> tablespace, where du isn't seeing them?
> >
> > > Nope. Its a pretty much clean database without any fancy stuff.
> >
> > Peculiar.  If you want to put some time into it, you could try comparing
> > sizes table-by-table to see if you can isolate where the discrepancy is.
> >
> >
> Perhaps with the contrib adminpack you may easily find where it comes from
> comparing size from pg_table_size and pg_stat_file ?
>

You don't need the adminpack extension to use pg_stat_file. pg_stat_file
is in PostgreSQL core.


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

On Wed, 6 Jun 2012 20:31:36 +0200
Alban Hertroys <haramrae@gmail.com> wrote:

> On 6 Jun 2012, at 16:33, Frank Lanitz wrote:
>
> > the result is much bigger than running a df -s over the postgres
> > folder
> > - Its about factor 5 to 10 depending on database.
>
>
> Is your du reporting sizes in Bytes or blocks or ...?

Should be byte as its a linux.

cheers,
Frank
--
Frank Lanitz <frank@frank.uvena.de>

Le mercredi 06 juin 2012 à 21:45 +0200, Frank Lanitz a écrit :
> On Wed, 6 Jun 2012 20:31:36 +0200
> Alban Hertroys <haramrae@gmail.com> wrote:
>
> > On 6 Jun 2012, at 16:33, Frank Lanitz wrote:
> >

> >
> > Is your du reporting sizes in Bytes or blocks or ...?
>
> Should be byte as its a linux.
>

Not sure about this; from du's man page :

Display  values are in units of the first available SIZE from
--block-size, and the DU_BLOCK_SIZE, BLOCK_SIZE and BLOCKSIZE
environment variables.
Otherwise, units default to 1024 bytes (or 512 if POSIXLY_CORRECT is
set).

vv=# select sum(pg_database_size(datid)) as total_size from
pg_stat_database;
 total_size
------------
  105086036
(1 ligne)


du -s base
103808    base



--
Vincent Veyron
http://vincentveyron.com
Logiciels de gestion pour le service juridique