Forum OpenACS Q&A: PG relpages vs actual diskspace

Collapse
1: PG relpages vs actual diskspace
Posted by Jonathan Ellis on 02/26/03 09:56 PM
select 8*sum(relpages) from pg_class;
?column?
----------
  446048

du -s pgdata/
4818806 pgdata

what am I missing here?

Collapse
2: Re: PG relpages vs actual diskspace (response to 1)
Posted by Don Baccus on 02/26/03 10:15 PM
power-of-2 K vs. exact bytes?  The same reason 128MB is more than 128 million bytes?  That's part of it but not all of it.

Does relpages include indexes?

Collapse
3: Re: PG relpages vs actual diskspace (response to 1)
Posted by Jonathan Ellis on 02/26/03 10:31 PM
du reports in K (1024 bytes) by default; a pg page is 8 * 1024 bytes by default.  [hence my magic number.]  so that's not a factor.

pg_class includes indexes on separate rows from the base tables, but the global sum will include them as well.

Collapse
4: Re: PG relpages vs actual diskspace (response to 3)
Posted by Jeff Davis on 02/26/03 10:48 PM
the numbers will be correct if you vacuum first since relplages is updated when vacuuming. here is an example: 
# select relpages , relname, relfilenode from pg_class where relfilenode = 1005440;
 relpages |      relname      | relfilenode 
----------+-------------------+-------------
       34 | apm_package_files |     1005440

34 * 8 * 1024 = 278528

$ wc -c ./base/1004872/1005440
 327680 ./base/1004872/1005440

# vacuum analyze  apm_package_files;
VACUUM
xarg=# select relpages , relname, relfilenode from pg_class where relfilenode = 1005440;
 relpages |      relname      | relfilenode 
----------+-------------------+-------------
       40 | apm_package_files |     1005440
40*8*1024 = 327680.
Collapse
5: Re: PG relpages vs actual diskspace (response to 1)
Posted by Jonathan Ellis on 02/26/03 11:09 PM
True, but I vacuum nightly.

here was the problem:

find . | grep core
./base/92744/core
./base/92744/core.9620
./base/92744/core.10583
./base/92744/core.10741
./base/92744/core.10840
./base/92744/core.11104
./base/92744/core.13042
./base/92744/core.13198
./base/92744/core.13436
./base/92744/core.13687
./base/92744/core.13982
./base/92744/core.14216
./base/92744/core.14460
./base/92744/core.24794

the dates are from a couple weeks ago when I had some memory go bad.  D'oh! :)