From: Justin Pryzby <pryzby@telsasoft.com>
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Database size 1T but unclear why
Date: Sun, 9 Dec 2018 10:01:08 -0600
Message-ID: <20181209160108.GB4848@telsasoft.com> (raw)
In-Reply-To: <CA+t6e1mNTXhOhXuNpuFT6z0QSfwMggVpBkV7AqGok+G5Oe1gOQ@mail.gmail.com>
References: <CA+t6e1mNTXhOhXuNpuFT6z0QSfwMggVpBkV7AqGok+G5Oe1gOQ@mail.gmail.com>
On Sun, Dec 09, 2018 at 05:18:55PM +0200, Mariel Cherkassky wrote:
> I'm trying to understand why my database consume so much space. I checked
> the space it consume on disk :
This seems to be essentially the same question you asked last month, so should
either continue the existing thread or link to it. I went to the effort to
look it up:
https://www.postgresql.org/message-id/flat/CA%2Bt6e1mtdVct%2BCn%3Dqs%3Dq%3DLLL_yKSssO6dxiZk%2Bb16xq4...> [root@ base]# du -sh * | sort -n
> 1.1T 17312
> 5.2G pgsql_tmp
> 6.3M 1
> 6.3M 12865
> 6.4M 12870
> 119G 17313
du -h shouldn't be passed to sort -n.
To get useful, sorted output, use du -m.
> However, when checking the sizes of my biggest tables (included with
> indexes and toasts) :
> select a.oid as oid a.relname as table_name,pg_relation_size(a.oid,
> 'main')/1024/1024 as main_MB,
> pg_relation_size(a.oid, 'fsm')/1024/1024 as fsm_MB,
> pg_relation_size(a.oid, 'vm')/1024/1024 as vm_MB,
> pg_relation_size(a.oid, 'init')/1024/1024 as init_MB,
> pg_table_size(a.oid)/1024/1024 AS relation_size_mb,
> pg_indexes_size(a.oid)/1024/1024 as indexes_MB,
> pg_total_relation_size(a.oid)/1024/1024 as total_size_MB
> from pg_class a where relkind in ('r','t') order by
> relation_size_mb desc,total_size_MB desc limit 10;
Why condition on relkind ? It's possible an index or materialized view is huge.
Other "kind"s may be tiny...but no reason not to check. Why not sort by
pg_total_relation_size() ? That would show a bloated index, but I think your
current query could miss it, if it wasn't also in the top 10 largest tables.
> So is there an option of orphans files in case vacuum full failed ?
Andrew answered here:
https://www.postgresql.org/message-id/87pnvl2gki.fsf%40news-spur.riddles.org.uk> In addition, what else would you recommend to check to understand why the
> database consume so much space ?
You can run: du --max=3 -mx ..../base/17312 |sort -nr |head
And: find ..../base/17312 -printf '%s %p\n' |sort -nr |head
That works for anything, not just postgres.
As andrew suggested, you should look for files which have no associated
filenode. You should use pg_relation_filenode(pg_class.oid), or maybe
pg_filenode_relation(tablespace oid, filenode oid)
https://www.postgresql.org/docs/current/functions-admin.html
Justin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: pryzby@telsasoft.com, mariel.cherkassky@gmail.com, pgsql-performance@lists.postgresql.org
Subject: Re: Database size 1T but unclear why
In-Reply-To: <20181209160108.GB4848@telsasoft.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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