pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedDatabase size 1T but unclear why
4+ messages / 3 participants
[nested] [flat]
* Database size 1T but unclear why
@ 2018-12-09 15:18 Mariel Cherkassky <mariel.cherkassky@gmail.com>
2018-12-09 15:42 ` Re: Database size 1T but unclear why Rick Otten <rottenwindfish@gmail.com>
2018-12-09 16:01 ` Re: Database size 1T but unclear why Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 2 replies; 4+ messages in thread
From: Mariel Cherkassky @ 2018-12-09 15:18 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
Hi,
I'm trying to understand why my database consume so much space. I checked
the space it consume on disk :
[root@ base]# du -sh * | sort -n
1.1T 17312
5.2G pgsql_tmp
6.3M 1
6.3M 12865
6.4M 12870
119G 17313
myBIGdb=# select t1.oid,t1.datname AS
db_name,pg_size_pretty(pg_database_size(t1.datname)) as db_size from
pg_database t1 order by pg_database_size(t1.datname) desc
myBIGdb-# ;
oid | db_name | db_size
-------+----------------+---------
17312 | myBIGdb | 1054 GB
17313| mySmallDB | 118 GB
12870 | postgres | 6525 kB
1 | template1 | 6417 kB
12865 | template0 | 6409 kB
(5 rows)
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;
oid | table_name | main_mb | fsm_mb | vm_mb | init_mb |
relation_size_mb | indexes_mb | total_size_mb
------+-----------------------------+---------+--------+-------+---------+------------------+------------+---------------
*17610 *| table_1 | 1 | 0 | 0 | 0
| 115306 | 0 | 115306
17614 | *pg_toast_17610 *| 114025 | 28 | 0 | 0
| 114053 | 1250 | 115304
*17315 *| table_2 | 166 | 0 | 0 | 0
| 2414 | 18 | 2432
17321 | *pg_toast_17315 *| 2222 | 0 | 0 | 0
| 2223 | 24 | 2247
*17540* | table_3 | 1016 | 0 | 0 | 0
| 1368 | 1606 | 2975
17634 | table_4 | 628 | 0 | 0 | 0 |
677 | 261 | 938
17402 | table_5 | 623 | 0 | 0 | 0 |
623 | 419 | 1043
17648 | table_5 | 393 | 0 | 0 | 0 |
393 | 341 | 735
17548 | *pg_toast_17540 *| 347 | 0 | 0 | 0
| 347 | 4 | 351
17835 | table 6 | 109 | 0 | 0 | 0 |
109 | 71 | 181
As you can see , the sum of the biggest tables is under 200G. In addition,
I know that on that database there were some vacuum full operations that
failed. So is there an option of orphans files in case vacuum full failed ?
In addition, what else would you recommend to check to understand why the
database consume so much space ?
Thanks .
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Database size 1T but unclear why
2018-12-09 15:18 Database size 1T but unclear why Mariel Cherkassky <mariel.cherkassky@gmail.com>
@ 2018-12-09 15:42 ` Rick Otten <rottenwindfish@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: Rick Otten @ 2018-12-09 15:42 UTC (permalink / raw)
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
On Sun, Dec 9, 2018 at 10:19 AM Mariel Cherkassky <
mariel.cherkassky@gmail.com> wrote:
> Hi,
> I'm trying to understand why my database consume so much space. I checked
> the space it consume on disk :
>
>
Have you tried running pg_repack? (It is an extension.)
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Database size 1T but unclear why
2018-12-09 15:18 Database size 1T but unclear why Mariel Cherkassky <mariel.cherkassky@gmail.com>
@ 2018-12-09 16:01 ` Justin Pryzby <pryzby@telsasoft.com>
2018-12-11 18:55 ` Re: Database size 1T but unclear why Justin Pryzby <pryzby@telsasoft.com>
1 sibling, 1 reply; 4+ messages in thread
From: Justin Pryzby @ 2018-12-09 16:01 UTC (permalink / raw)
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Database size 1T but unclear why
2018-12-09 15:18 Database size 1T but unclear why Mariel Cherkassky <mariel.cherkassky@gmail.com>
2018-12-09 16:01 ` Re: Database size 1T but unclear why Justin Pryzby <pryzby@telsasoft.com>
@ 2018-12-11 18:55 ` Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 0 replies; 4+ messages in thread
From: Justin Pryzby @ 2018-12-11 18:55 UTC (permalink / raw)
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
On Sun, Dec 09, 2018 at 10:01:08AM -0600, Justin Pryzby wrote:
> 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 :
To find single relations which are using more than 100GB,
you could also run:
|find ..../base/17312 -name '*.[0-9]??'
(technically that should be a regex and not a shell glob but seems to work well
enough).
Justin
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2018-12-11 18:55 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-12-09 15:18 Database size 1T but unclear why Mariel Cherkassky <mariel.cherkassky@gmail.com>
2018-12-09 15:42 ` Rick Otten <rottenwindfish@gmail.com>
2018-12-09 16:01 ` Justin Pryzby <pryzby@telsasoft.com>
2018-12-11 18:55 ` Justin Pryzby <pryzby@telsasoft.com>
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