pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedamazon aroura config - seriously overcommited defaults? (May be Off Topic)
10+ messages / 7 participants
[nested] [flat]
* amazon aroura config - seriously overcommited defaults? (May be Off Topic)
@ 2018-12-08 19:00 Square Bob <square_bob@yahoo.com>
2018-12-08 19:03 ` Fwd: amazon aroura config - seriously overcommited defaults? (May be Off Topic) Square Bob <square_bob@yahoo.com>
2018-12-08 19:21 ` Re: amazon aroura config - seriously overcommited defaults? (May be Off Topic) Pavel Stehule <pavel.stehule@gmail.com>
2018-12-08 20:06 ` Re: amazon aroura config - seriously overcommited defaults? (May be Off Topic) Jeremy Schneider <schnjere@amazon.com>
0 siblings, 3 replies; 10+ messages in thread
From: Square Bob @ 2018-12-08 19:00 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Fwd: amazon aroura config - seriously overcommited defaults? (May be Off Topic)
2018-12-08 19:00 amazon aroura config - seriously overcommited defaults? (May be Off Topic) Square Bob <square_bob@yahoo.com>
@ 2018-12-08 19:03 ` Square Bob <square_bob@yahoo.com>
2018-12-08 19:48 ` Re: amazon aroura config - seriously overcommited defaults? (May be Off Topic) Bob Lunney <bob_lunney@yahoo.com>
2 siblings, 1 reply; 10+ messages in thread
From: Square Bob @ 2018-12-08 19:03 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
This question is probably more of a fit for the performance list, sorry
for the cross post
-------- Forwarded Message --------
Subject: amazon aroura config - seriously overcommited defaults? (May
be Off Topic)
Date: Sat, 8 Dec 2018 12:00:33 -0700
From: Square Bob <square_bob@yahoo.com>
To: pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: amazon aroura config - seriously overcommited defaults? (May be Off Topic)
2018-12-08 19:00 amazon aroura config - seriously overcommited defaults? (May be Off Topic) Square Bob <square_bob@yahoo.com>
2018-12-08 19:03 ` Fwd: amazon aroura config - seriously overcommited defaults? (May be Off Topic) Square Bob <square_bob@yahoo.com>
@ 2018-12-08 19:48 ` Bob Lunney <bob_lunney@yahoo.com>
0 siblings, 0 replies; 10+ messages in thread
From: Bob Lunney @ 2018-12-08 19:48 UTC (permalink / raw)
To: Square Bob <square_bob@yahoo.com>; +Cc: pgsql-performance@lists.postgresql.org
Aurora doesn’t use a typical file system so the RAM usually reserved for the OS file system bufffer cache is instead used for shared_buffers.
We run multiple Aurora/ PG instances and they work quite well. There are limitations in superuser private, so be aware of that, but generally speaking Aurora/PG works well.
Bob Lunney
Sent from my PDP11
> On Dec 8, 2018, at 2:03 PM, Square Bob <square_bob@yahoo.com> wrote:
>
> This question is probably more of a fit for the performance list, sorry for the cross post
>
>
>
> -------- Forwarded Message --------
> Subject: amazon aroura config - seriously overcommited defaults? (May be Off Topic)
> Date: Sat, 8 Dec 2018 12:00:33 -0700
> From: Square Bob <square_bob@yahoo.com>
> To: pgsql-general@lists.postgresql.org
>
>
> 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
>
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: amazon aroura config - seriously overcommited defaults? (May be Off Topic)
2018-12-08 19:00 amazon aroura config - seriously overcommited defaults? (May be Off Topic) Square Bob <square_bob@yahoo.com>
@ 2018-12-08 19:21 ` Pavel Stehule <pavel.stehule@gmail.com>
2 siblings, 0 replies; 10+ messages in thread
From: Pavel Stehule @ 2018-12-08 19:21 UTC (permalink / raw)
To: square_bob@yahoo.com; +Cc: pgsql-general <pgsql-general@lists.postgresql.org>
so 8. 12. 2018 v 20:04 odesílatel Square Bob <square_bob@yahoo.com> napsal:
> 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?
>
>
nobody knows what patches are used there. Max connections over 1000 are
not good idea for native Postgres. But maybe there are some patches - or
just mostly idle connections are expected.
Regards
Pavel
> Thanks in advance
>
>
>
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: amazon aroura config - seriously overcommited defaults? (May be Off Topic)
2018-12-08 19:00 amazon aroura config - seriously overcommited defaults? (May be Off Topic) Square Bob <square_bob@yahoo.com>
@ 2018-12-08 20:06 ` Jeremy Schneider <schnjere@amazon.com>
2018-12-08 23:12 ` Re: amazon aroura config - seriously overcommited defaults? (May be Off Topic) Andres Freund <andres@anarazel.de>
2 siblings, 1 reply; 10+ messages in thread
From: Jeremy Schneider @ 2018-12-08 20:06 UTC (permalink / raw)
To: Square Bob <square_bob@yahoo.com>; pgsql-general@lists.postgresql.org; +Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
On 12/8/18 11:00, Square Bob wrote:
> My apologies if this is off topic.
The AWS Aurora PostgreSQL forums are also a great place to post
questions like this
https://forums.aws.amazon.com/forum.jspa?forumID=227
> 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
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. But
remember that in open source PostgreSQL on linux, all I/O goes through
the filesystem and kernel buffer cache so in reality any available
memory on the box is used for cache.
Unlike normal PostgreSQL, Aurora does not do I/O through the linux
buffer cache. If the default was left at 25% then this would result in
very surprising performance for most people. On other databases where
direct I/O is the normal pattern, 75% of memory on the box is often
cited as a good starting point for OLTP systems. This default used on
Aurora.
> work_mem: 64000 (kb)
> which equates to 65.5MB
At present, this has been left at the community default for both RDS and
Aurora PostgreSQL.
> maintenance_work_mem: GREATEST({DBInstanceClassMemory/63963136*1024},65536)
> which equates to 4.2GB
This formula will set maint_work_mem to 1.639% of the memory on the
system. It should be 511MB on an instance with 30.5GB of memory.
> max_connections: LEAST({DBInstanceClassMemory/9531392},5000)
> which equates to 3,380
On both RDS PostgreSQL and Aurora, max_connections is set to a value
that's conservatively high. While the default setting here won't stop
you, an r4.xlarge has only two physical CPUs and it's probably not a
good idea to run with 3000 connections.
Connection management is a common challenge with databases of all
flavors. The right number is incredibly workload dependent and I'm not
sure whether it's possible to have a truly meaningful default limit as a
formula of the server type.
> 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
It's not quite this straightforward.
First of all, work_mem is per plan node and it's only a guidance for
where things should spill to disk. It doesn't completely prevent runaway
memory usage by queries. Many queries don't need much work_mem at all,
and many other queries use more memory than work_mem.
Secondly, IIRC, autovacuum actually has a hard-coded artificial 1GB
limit regardless of your maint_work_mem. However operations like index
creation can in fact use all of maint_work_mem.
> 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)
That's actually the same as shared_buffers - 75% of the memory on the
server. And remember this is a planner/costing parameter; it has
nothing to do with allocating actual memory.
> 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?
Memory management is hard. Nevermind PostgreSQL - it's hard to even get
a clear picture of what happens in the Linux kernel with regard to
memory. Think about these two questions: (1) Is memory pressure slowing
me down? (2) Is memory pressure causing any risk or danger to the
system? I've heard of issues even with the new MemAvailable value that
was added to /proc/meminfo - it seems difficult to get an accurate
picture. While over-subscription might sound bad, you probably don't
want to just disable swap completely either. There are usually pages
sitting in memory that are completely unnecessary.
I'm not going to claim the RDS defaults are perfect - in fact I'd love
to hear ideas about how they could be improved. [Hopefully without
starting any religious wars...] But I hope I've shown here that they
aren't as completely crazy as they first appeared? :)
-Jeremy
--
Jeremy Schneider
Database Engineer
Amazon Web Services
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: amazon aroura config - seriously overcommited defaults? (May be Off Topic)
2018-12-08 19:00 amazon aroura config - seriously overcommited defaults? (May be Off Topic) Square Bob <square_bob@yahoo.com>
2018-12-08 20:06 ` Re: amazon aroura config - seriously overcommited defaults? (May be Off Topic) Jeremy Schneider <schnjere@amazon.com>
@ 2018-12-08 23:12 ` Andres Freund <andres@anarazel.de>
2018-12-08 23:23 ` Re: amazon aroura config - seriously overcommited defaults? (May be Off Topic) Rob Sargent <robjsargent@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Andres Freund @ 2018-12-08 23:12 UTC (permalink / raw)
To: Jeremy Schneider <schnjere@amazon.com>; +Cc: Square Bob <square_bob@yahoo.com>; pgsql-general@lists.postgresql.org, "pgsql-performance@lists.postgresql.org" <pgsql-performance@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: amazon aroura config - seriously overcommited defaults? (May be Off Topic)
2018-12-08 19:00 amazon aroura config - seriously overcommited defaults? (May be Off Topic) Square Bob <square_bob@yahoo.com>
2018-12-08 20:06 ` Re: amazon aroura config - seriously overcommited defaults? (May be Off Topic) Jeremy Schneider <schnjere@amazon.com>
2018-12-08 23:12 ` Re: amazon aroura config - seriously overcommited defaults? (May be Off Topic) Andres Freund <andres@anarazel.de>
@ 2018-12-08 23:23 ` Rob Sargent <robjsargent@gmail.com>
2018-12-08 23:38 ` Re: amazon aroura config - seriously overcommited defaults? (May be Off Topic) Andres Freund <andres@anarazel.de>
0 siblings, 1 reply; 10+ messages in thread
From: Rob Sargent @ 2018-12-08 23:23 UTC (permalink / raw)
To: Andres Freund <andres@anarazel.de>; +Cc: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
> On Dec 8, 2018, at 3:12 PM, Andres Freund <andres@anarazel.de> 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?
rjs
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: amazon aroura config - seriously overcommited defaults? (May be Off Topic)
2018-12-08 19:00 amazon aroura config - seriously overcommited defaults? (May be Off Topic) Square Bob <square_bob@yahoo.com>
2018-12-08 20:06 ` Re: amazon aroura config - seriously overcommited defaults? (May be Off Topic) Jeremy Schneider <schnjere@amazon.com>
2018-12-08 23:12 ` Re: amazon aroura config - seriously overcommited defaults? (May be Off Topic) Andres Freund <andres@anarazel.de>
2018-12-08 23:23 ` Re: amazon aroura config - seriously overcommited defaults? (May be Off Topic) Rob Sargent <robjsargent@gmail.com>
@ 2018-12-08 23:38 ` Andres Freund <andres@anarazel.de>
2018-12-09 12:51 ` Re: amazon aroura config - seriously overcommited defaults? (May be Off Topic) Andrew Dunstan <andrew.dunstan@2ndquadrant.com>
0 siblings, 1 reply; 10+ messages in thread
From: Andres Freund @ 2018-12-08 23:38 UTC (permalink / raw)
To: Rob Sargent <robjsargent@gmail.com>; +Cc: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
On 2018-12-08 15:23:19 -0800, Rob Sargent wrote:
>
>
> > On Dec 8, 2018, at 3:12 PM, Andres Freund <andres@anarazel.de> 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).
Greetings,
Andres Freund
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: amazon aroura config - seriously overcommited defaults? (May be Off Topic)
2018-12-08 19:00 amazon aroura config - seriously overcommited defaults? (May be Off Topic) Square Bob <square_bob@yahoo.com>
2018-12-08 20:06 ` Re: amazon aroura config - seriously overcommited defaults? (May be Off Topic) Jeremy Schneider <schnjere@amazon.com>
2018-12-08 23:12 ` Re: amazon aroura config - seriously overcommited defaults? (May be Off Topic) Andres Freund <andres@anarazel.de>
2018-12-08 23:23 ` Re: amazon aroura config - seriously overcommited defaults? (May be Off Topic) Rob Sargent <robjsargent@gmail.com>
2018-12-08 23:38 ` Re: amazon aroura config - seriously overcommited defaults? (May be Off Topic) Andres Freund <andres@anarazel.de>
@ 2018-12-09 12:51 ` Andrew Dunstan <andrew.dunstan@2ndquadrant.com>
2018-12-09 15:20 ` Re: amazon aroura config - seriously overcommited defaults? (May be Off Topic) Square Bob <square_bob@yahoo.com>
0 siblings, 1 reply; 10+ messages in thread
From: Andrew Dunstan @ 2018-12-09 12:51 UTC (permalink / raw)
To: Andres Freund <andres@anarazel.de>; Rob Sargent <robjsargent@gmail.com>; +Cc: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
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 <andres@anarazel.de> 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 Dunstan https://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: amazon aroura config - seriously overcommited defaults? (May be Off Topic)
2018-12-08 19:00 amazon aroura config - seriously overcommited defaults? (May be Off Topic) Square Bob <square_bob@yahoo.com>
2018-12-08 20:06 ` Re: amazon aroura config - seriously overcommited defaults? (May be Off Topic) Jeremy Schneider <schnjere@amazon.com>
2018-12-08 23:12 ` Re: amazon aroura config - seriously overcommited defaults? (May be Off Topic) Andres Freund <andres@anarazel.de>
2018-12-08 23:23 ` Re: amazon aroura config - seriously overcommited defaults? (May be Off Topic) Rob Sargent <robjsargent@gmail.com>
2018-12-08 23:38 ` Re: amazon aroura config - seriously overcommited defaults? (May be Off Topic) Andres Freund <andres@anarazel.de>
2018-12-09 12:51 ` Re: amazon aroura config - seriously overcommited defaults? (May be Off Topic) Andrew Dunstan <andrew.dunstan@2ndquadrant.com>
@ 2018-12-09 15:20 ` Square Bob <square_bob@yahoo.com>
0 siblings, 0 replies; 10+ messages in thread
From: Square Bob @ 2018-12-09 15:20 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
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 <andres@anarazel.de> 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
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2018-12-09 15:20 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-12-08 19:00 amazon aroura config - seriously overcommited defaults? (May be Off Topic) Square Bob <square_bob@yahoo.com>
2018-12-08 19:03 ` Square Bob <square_bob@yahoo.com>
2018-12-08 19:48 ` Bob Lunney <bob_lunney@yahoo.com>
2018-12-08 19:21 ` Pavel Stehule <pavel.stehule@gmail.com>
2018-12-08 20:06 ` Jeremy Schneider <schnjere@amazon.com>
2018-12-08 23:12 ` Andres Freund <andres@anarazel.de>
2018-12-08 23:23 ` Rob Sargent <robjsargent@gmail.com>
2018-12-08 23:38 ` Andres Freund <andres@anarazel.de>
2018-12-09 12:51 ` Andrew Dunstan <andrew.dunstan@2ndquadrant.com>
2018-12-09 15:20 ` Square Bob <square_bob@yahoo.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