Re: amazon aroura config - seriously overcommited defaults? (May be Off Topic)
On 12/8/18 6:38 PM, Andres Freund wrote: On 2018-12-08 15:23:19 -0800, Rob Sargent wrote: On Dec 8, 2018, at 3:12 PM, Andres Freund wrote: On 2018-12-08 12:06:23 -0800, Jeremy Schneider wrote: On RDS PostgreSQL, the default is 25% of your server memory. This seems to be pretty widely accepted as a good starting point on PostgreSQL. FWIW, I think it's widely cited, but also bad advice. 25% for a OLTP workload on a 1TB machine with a database size above 25% is a terrible idea. Sorry, could you please expand “database size above 25%”? 25% of what? Memory available to postgres (i.e. 100% of the server's memory on a server dedicated to postgres, less if it's shared duty). I think the best advice these days is that you need to triangulate to find the best setting for shared_buffers. It's very workload dependent, and there isn't even a semi-reliable rule of thumb. cheers andrew -- Andrew Dunstanhttps://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Database size 1T but unclear why
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.1T17312
5.2Gpgsql_tmp
6.3M1
6.3M12865
6.4M12870
119G17313
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 *| | 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: amazon aroura config - seriously overcommited defaults? (May be Off Topic)
On 12/9/18 5:51 AM, Andrew Dunstan wrote: On 12/8/18 6:38 PM, Andres Freund wrote: On 2018-12-08 15:23:19 -0800, Rob Sargent wrote: On Dec 8, 2018, at 3:12 PM, Andres Freund wrote: On 2018-12-08 12:06:23 -0800, Jeremy Schneider wrote: On RDS PostgreSQL, the default is 25% of your server memory. This seems to be pretty widely accepted as a good starting point on PostgreSQL. FWIW, I think it's widely cited, but also bad advice. 25% for a OLTP workload on a 1TB machine with a database size above 25% is a terrible idea. Sorry, could you please expand “database size above 25%”? 25% of what? Memory available to postgres (i.e. 100% of the server's memory on a server dedicated to postgres, less if it's shared duty). I think the best advice these days is that you need to triangulate to find the best setting for shared_buffers. It's very workload dependent, and there isn't even a semi-reliable rule of thumb. Any advice, approaches to triangulating shared_buffers you can share would be most helpful cheers andrew
Re: Database size 1T but unclear why
On Sun, Dec 9, 2018 at 10:19 AM Mariel Cherkassky < [email protected]> 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
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.1T17312
> 5.2Gpgsql_tmp
> 6.3M1
> 6.3M12865
> 6.4M12870
> 119G17313
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
