Content-Type: multipart/alternative;
	boundary=Apple-Mail-5CA664DD-7DBA-47DD-8E24-10EB10927178
Mime-Version: 1.0 (1.0)
Subject: Re: amazon aroura config - seriously overcommited defaults? (May be
 Off Topic)
From: Bob Lunney <bob_lunney@yahoo.com>
In-Reply-To: <b76d913c-010b-39b3-3342-5db083536415@yahoo.com>
Date: Sat, 8 Dec 2018 14:48:24 -0500
Cc: pgsql-performance@lists.postgresql.org
Content-Transfer-Encoding: 7bit
Message-Id: <05ECB2C1-38CF-4E4E-8B4F-DA52A0A79D83@yahoo.com>
References: <a053cc2d-bf74-cd33-09e1-024b7e87b494@yahoo.com>
 <b76d913c-010b-39b3-3342-5db083536415@yahoo.com>
To: Square Bob <square_bob@yahoo.com>
Precedence: bulk


--Apple-Mail-5CA664DD-7DBA-47DD-8E24-10EB10927178
Content-Type: text/plain;
	charset=utf-8
Content-Transfer-Encoding: quoted-printable

Aurora doesn=E2=80=99t use a typical file system so the RAM usually reserved=
 for the OS file system bufffer cache is instead used for shared_buffers.=20=


We run multiple Aurora/ PG instances and they work quite well.  There are li=
mitations in superuser private, so be aware of that, but generally speaking A=
urora/PG works well.=20

Bob Lunney

Sent from my PDP11

> On Dec 8, 2018, at 2:03 PM, Square Bob <square_bob@yahoo.com> wrote:
>=20
> This question is probably more of a fit for the performance list, sorry fo=
r the cross post
>=20
>=20
>=20
> -------- Forwarded Message --------
> Subject:	amazon aroura config - seriously overcommited defaults? (Ma=
y 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
>=20
>=20
> All;
>=20
>=20
> My apologies if this is off topic.
>=20
>=20
> Our company is moving to Aurora, In the past I would take care not to allo=
w postgresql to over-commit memory beyond the actual memory on the server, w=
hich meant I would add the buffer pool + (work_mem * max_connections) + (mai=
ntenance_work_mem * autovacuum threads)
>=20
>=20
> However as I look at the aroura defaults they are all off the charts, for e=
xample, 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:
>=20
>=20
> The instance size is db.r4.xlarge
>=20
>=20
> this instance size is listed as having 30.5GB of ram
>=20
>=20
> Here's the default settings:
>=20
>=20
> shared_buffers: {DBInstanceClassMemory/10922}
>=20
> which equates to 24GB
>=20
>=20
> work_mem:   64000 (kb)
>=20
> which equates to 65.5MB
>=20
>=20
> maintenance_work_mem: GREATEST({DBInstanceClassMemory/63963136*1024},65536=
)
>=20
> which equates to 4.2GB
>=20
>=20
> max_connections: LEAST({DBInstanceClassMemory/9531392},5000)
>=20
> which equates to 3,380
>=20
>=20
> According to my math (If I got it right)  in a worst case scenario,
>=20
> if we maxed out max_connections, work_mem and maintenance_work_mem limits
>=20
> the db would request 247GB of memory
>=20
>=20
> Additionally amazon has set effective_cache_size =3D
> {DBInstanceClassMemory/10922}
>=20
> which equates to about 2.9MB (which given the other outlandish setting may=
 be the only appropriate setting in the system)
>=20
>=20
>=20
> What the hell is amazon doing here? Am I missing the boat on tuning postgr=
esql memory? Is amazon simply counting on the bet that users will never full=
y utilize an instance?
>=20
>=20
> Thanks in advance
>=20
>=20
>=20

--Apple-Mail-5CA664DD-7DBA-47DD-8E24-10EB10927178
Content-Type: text/html;
	charset=utf-8
Content-Transfer-Encoding: quoted-printable

<html><head><meta http-equiv=3D"content-type" content=3D"text/html; charset=3D=
utf-8"></head><body dir=3D"auto">Aurora doesn=E2=80=99t use a typical file s=
ystem so the RAM usually reserved for the OS file system bufffer cache is in=
stead used for shared_buffers.&nbsp;<div><br></div><div>We run multiple Auro=
ra/ PG instances and they work quite well. &nbsp;There are limitations in su=
peruser private, so be aware of that, but generally speaking Aurora/PG works=
 well.&nbsp;</div><div><br></div><div>Bob Lunney</div><div><br><div id=3D"Ap=
pleMailSignature" dir=3D"ltr">Sent from my PDP11</div><div dir=3D"ltr"><br>O=
n Dec 8, 2018, at 2:03 PM, Square Bob &lt;<a href=3D"mailto:square_bob@yahoo=
.com">square_bob@yahoo.com</a>&gt; wrote:<br><br></div><blockquote type=3D"c=
ite"><div dir=3D"ltr">
 =20

    <meta http-equiv=3D"content-type" content=3D"text/html; charset=3DUTF-8"=
>
 =20
 =20
    <p>This question is probably more of a fit for the performance list,
      sorry for the cross post<br>
    </p>
    <div class=3D"moz-forward-container"><br>
      <br>
      -------- Forwarded Message --------
      <table class=3D"moz-email-headers-table" cellspacing=3D"0" cellpadding=
=3D"0" border=3D"0">
        <tbody>
          <tr>
            <th valign=3D"BASELINE" nowrap=3D"nowrap" align=3D"RIGHT">Subjec=
t:
            </th>
            <td>amazon aroura config - seriously overcommited defaults?
              (May be Off Topic)</td>
          </tr>
          <tr>
            <th valign=3D"BASELINE" nowrap=3D"nowrap" align=3D"RIGHT">Date: <=
/th>
            <td>Sat, 8 Dec 2018 12:00:33 -0700</td>
          </tr>
          <tr>
            <th valign=3D"BASELINE" nowrap=3D"nowrap" align=3D"RIGHT">From: <=
/th>
            <td>Square Bob <a class=3D"moz-txt-link-rfc2396E" href=3D"mailto=
:square_bob@yahoo.com">&lt;square_bob@yahoo.com&gt;</a></td>
          </tr>
          <tr>
            <th valign=3D"BASELINE" nowrap=3D"nowrap" align=3D"RIGHT">To: </=
th>
            <td><a class=3D"moz-txt-link-abbreviated" href=3D"mailto:pgsql-g=
eneral@lists.postgresql.org">pgsql-general@lists.postgresql.org</a></td>
          </tr>
        </tbody>
      </table>
      <br>
      <br>
      All;<br>
      <br>
      <br>
      My apologies if this is off topic.<br>
      <br>
      <br>
      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)<br>
      <br>
      <br>
      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:<br>
      <br>
      <br>
      The instance size is db.r4.xlarge<br>
      <br>
      <br>
      this instance size is listed as having 30.5GB of ram<br>
      <br>
      <br>
      Here's the default settings:<br>
      <br>
      <br>
      shared_buffers: {DBInstanceClassMemory/10922}<br>
      <br>
      which equates to 24GB<br>
      <br>
      <br>
      work_mem:&nbsp;&nbsp; 64000 (kb)<br>
      <br>
      which equates to 65.5MB<br>
      <br>
      <br>
      maintenance_work_mem:
      GREATEST({DBInstanceClassMemory/63963136*1024},65536)<br>
      <br>
      which equates to 4.2GB<br>
      <br>
      <br>
      max_connections: LEAST({DBInstanceClassMemory/9531392},5000)<br>
      <br>
      which equates to 3,380<br>
      <br>
      <br>
      According to my math (If I got it right)&nbsp; in a worst case
      scenario,<br>
      <br>
      if we maxed out max_connections, work_mem and maintenance_work_mem
      limits<br>
      <br>
      the db would request 247GB of memory<br>
      <br>
      <br>
      Additionally amazon has set effective_cache_size =3D<br>
      {DBInstanceClassMemory/10922}<br>
      <br>
      which equates to about 2.9MB (which given the other outlandish
      setting may be the only appropriate setting in the system)<br>
      <br>
      <br>
      <br>
      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?<br>
      <br>
      <br>
      Thanks in advance<br>
      <br>
      <br>
      <br>
    </div>
 =20

</div></blockquote></div></body></html>=

--Apple-Mail-5CA664DD-7DBA-47DD-8E24-10EB10927178--