We're trying to figure out how to account for our disk space
consumption in a database.
$ sudo du -shx /var/lib/postgresql/8.4/main/
1.9G/var/lib/postgresql/8.4/main/
But when we query Postgresql to find out how much disk space is
actually being used by the various databases, we get a total of under
600MB (the exact query we use for determining this is below, derived
from the example query in the PG documentation):
$ pg-dbspace
psql: FATAL: database template0 is not currently accepting connections
1272446976 rp
971186176 sfbox
513794048 yang
30326784 ch
16400384 reviewboard
14958592 pod
6733824 cbkup
5767168 redmine_default
2138112 ibkup
2138112 foo
2113536 template1
2113536 postgres
There are two databases with tablespaces on different volumes than
what /var/lib/postgresql/ is on - their PG-reported consumption is
~2.1GB, and they take up about ~1.5x more on disk:
$ df -h | fgrep /mnt
/dev/sdf 2.0G 1.4G 502M 74% /mnt/box
/dev/sdg 5.0G 2.1G 2.7G 44% /mnt/rp
We're also curious about the 1.5x overhead, but we're mainly not sure
why the rest of the database takes up 3x more than reported, even
discounting pg_xlog (which is for the entire cluster):
$ sudo -i 'du -shx /var/lib/postgresql/8.4/main/*'
1.8G/var/lib/postgresql/8.4/main/base
816K/var/lib/postgresql/8.4/main/global
144K/var/lib/postgresql/8.4/main/pg_clog
28K /var/lib/postgresql/8.4/main/pg_multixact
192K/var/lib/postgresql/8.4/main/pg_stat_tmp
80K /var/lib/postgresql/8.4/main/pg_subtrans
4.0K/var/lib/postgresql/8.4/main/pg_tblspc
4.0K/var/lib/postgresql/8.4/main/pg_twophase
4.0K/var/lib/postgresql/8.4/main/PG_VERSION
129M/var/lib/postgresql/8.4/main/pg_xlog
4.0K/var/lib/postgresql/8.4/main/postmaster.opts
4.0K/var/lib/postgresql/8.4/main/postmaster.pid
0 /var/lib/postgresql/8.4/main/server.crt
0 /var/lib/postgresql/8.4/main/server.key
Any hints? Thanks in advance.
The queries were using:
$ type pg-dbspace
pg-dbspace is a function
pg-dbspace ()
{
for db in $(psql -Atc 'select datname from pg_database');
do
printf '%12d %s\n' $(PGDATABASE=$db pg-space total) $db;
done | sort -rn
}
$ type pg-space
pg-space is a function
pg-space ()
{
local schema=${schema:-${1:-}} flags=;
case ${schema:-} in
total)
local query='select sum(bytes) from schemas' flags=-At
;;
'*')
local query='select * from tables'
;;
'')
local query='select * from schemas'
;;
*)
local query=select * from tables where _schema = '$schema'
;;
esac;
psql $flags -c
with
total as (
select sum(pg_relation_size(oid)) from pg_class where relkind = 'r'
),
basic as (
select
n.nspname as _schema,
relname as _table,
pg_relation_size(r.oid) as bytes,
(100*pg_relation_size(r.oid)/(select * from
total))::numeric(4,1) as pct
from pg_class r inner join pg_namespace n on (n.oid = relnamespace)
where relkind = 'r'
),
tables as (
select
_schema,
_table,
bytes,
lpad(pg_size_pretty(bytes), 9) as size,
pct
from basic
order by bytes desc
),
schemas as (
select
_schema,
sum(bytes) as bytes,
lpad(pg_size_pretty(sum(bytes)::int), 9) as size,
sum(pct) as pct
from basic
group by _schema
order by bytes desc
)
$query;
}
--
Yang Zhang
http://yz.mit.edu/
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
