amazon aroura config - seriously overcommited defaults? (May be Off Topic)

From: Square Bob <square_bob(at)yahoo(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: amazon aroura config - seriously overcommited defaults? (May be Off Topic)
Date: 2018-12-08 19:00:33
Message-ID: a053cc2d-bf74-cd33-09e1-024b7e87b494@yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-performance

All;

My apologies if this is off topic.

Our company is moving to Aurora, In the past I would take care not to
allow postgresql to over-commit memory beyond the actual memory on the
server, which meant I would add the buffer pool + (work_mem *
max_connections) + (maintenance_work_mem * autovacuum threads)

However as I look at the aroura defaults they are all off the charts,
for example, based on the calculations in the config (amazon doesn't
make it easy, some settings are in pages, some are in kb, some are who
knows what) I see the following settings as default in our aroura config:

The instance size is db.r4.xlarge

this instance size is listed as having 30.5GB of ram

Here's the default settings:

shared_buffers: {DBInstanceClassMemory/10922}

which equates to 24GB

work_mem:   64000 (kb)

which equates to 65.5MB

maintenance_work_mem: GREATEST({DBInstanceClassMemory/63963136*1024},65536)

which equates to 4.2GB

max_connections: LEAST({DBInstanceClassMemory/9531392},5000)

which equates to 3,380

According to my math (If I got it right)  in a worst case scenario,

if we maxed out max_connections, work_mem and maintenance_work_mem limits

the db would request 247GB of memory

Additionally amazon has set effective_cache_size =
{DBInstanceClassMemory/10922}

which equates to about 2.9MB (which given the other outlandish setting
may be the only appropriate setting in the system)

What the hell is amazon doing here? Am I missing the boat on tuning
postgresql memory? Is amazon simply counting on the bet that users will
never fully utilize an instance?

Thanks in advance

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Square Bob 2018-12-08 19:03:27 Fwd: amazon aroura config - seriously overcommited defaults? (May be Off Topic)
Previous Message Ron 2018-12-08 17:30:48 pg_ctl kill INT vs. pg_cancel_backend

Browse pgsql-performance by date

  From Date Subject
Next Message Square Bob 2018-12-08 19:03:27 Fwd: amazon aroura config - seriously overcommited defaults? (May be Off Topic)
Previous Message Ashutosh Bapat 2018-12-07 11:14:14 Re: Query with high planning time at version 11.1 compared versions 10.5 and 11.0