Thread
-
Database size 1T but unclear why
Mariel Cherkassky <mariel.cherkassky@gmail.com> — 2018-12-09T15:18:55Z
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 . -
Re: Database size 1T but unclear why
Rick Otten <rottenwindfish@gmail.com> — 2018-12-09T15:42:40Z
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.)
-
Re: Database size 1T but unclear why
Justin Pryzby <pryzby@telsasoft.com> — 2018-12-09T16:01:08Z
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%2Bb16xq4ccvWvw%40mail.gmail.com > [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 -
Re: Database size 1T but unclear why
Justin Pryzby <pryzby@telsasoft.com> — 2018-12-11T18:55:31Z
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