MIME-Version: 1.0
References: <CA+FDo=uz3+5vbQZDXN5SBAiZMnR0_w7YRZW+FS-VVuD3_XwB2g@mail.gmail.com>
 <CAM8SmLUMREu3MOSpfh58EoV9ysgWi8sFkbj_BnBJQjeJn3C+7Q@mail.gmail.com> <CA+FDo=v7kUwMAMF61gr=gQS8S3oedpGooTuZiNgTTXt7oEyZzA@mail.gmail.com>
In-Reply-To: <CA+FDo=v7kUwMAMF61gr=gQS8S3oedpGooTuZiNgTTXt7oEyZzA@mail.gmail.com>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Fri, 7 Jun 2024 08:13:55 -0400
Message-ID: <CANzqJaDta4gnZdB86DzMuSB4vTrd568S-=QiYwozj9PCpd1Www@mail.gmail.com>
Subject: Re: Poor performance after restoring database from snapshot on AWS RDS
To: "pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000006ba438061a4bbc52"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaDta4gnZdB86DzMuSB4vTrd568S-%3DQiYwozj9PCpd1Www%40mail.gmail.com>
Precedence: bulk

--0000000000006ba438061a4bbc52
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Fri, Jun 7, 2024 at 4:36=E2=80=AFAM Sam Kidman <sam@fresho.com> wrote:

> > This is due to the way that RDS restores snapshots.
>
> Thanks, I never would have guessed. Would vacuum analyze be sufficient
> to defeat the lazy loading or would we need to do something more
> specific to our application? (for example. select(*) on some commonly
> used tables)
>

https://www.postgresql.org/docs/14/pgprewarm.html

pg_prewarm is probably what you want.  Don't know if RDS Postgresql
supports it or not, though.


>
> I think vacuum full would certainly defeat the lazy loading since it
> would copy all of the table data, but that may take a very long time
> to run. I think vacuum analyze only scans a subset of rows but I might
> be wrong about that.
>
> Best, Sam
>
> On Wed, Jun 5, 2024 at 10:09=E2=80=AFPM Jeremy Smith <jeremy@musicsmith.n=
et>
> wrote:
> >
> > On Wed, Jun 5, 2024 at 4:23=E2=80=AFAM Sam Kidman <sam@fresho.com> wrot=
e:
> >
> > > We get very poor performance in the staging environment after this
> > > restore takes place - after some usage it seems to get better perhaps
> > > because of caching.
> > >
> >
> > This is due to the way that RDS restores snapshots.
> >
> > From the docs (
> https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_RestoreFromSn=
apshot.html
> ):
> >
> > You can use the restored DB instance as soon as its status is
> > available. The DB instance continues to load data in the background.
> > This is known as lazy loading.
> >
> > If you access data that hasn't been loaded yet, the DB instance
> > immediately downloads the requested data from Amazon S3, and then
> > continues loading the rest of the data in the background.
> >
> >
> >
> >   -Jeremy
>
>
>

--0000000000006ba438061a4bbc52
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Fri, Jun 7, 2024 at 4:36=E2=80=AFAM Sa=
m Kidman &lt;<a href=3D"mailto:sam@fresho.com">sam@fresho.com</a>&gt; wrote=
:<br></div><div class=3D"gmail_quote"><blockquote class=3D"gmail_quote" sty=
le=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);paddi=
ng-left:1ex">&gt; This is due to the way that RDS restores snapshots.<br>
<br>
Thanks, I never would have guessed. Would vacuum analyze be sufficient<br>
to defeat the lazy loading or would we need to do something more<br>
specific to our application? (for example. select(*) on some commonly<br>
used tables)<br></blockquote><div><br></div><div><a href=3D"https://www.pos=
tgresql.org/docs/14/pgprewarm.html">https://www.postgresql.org/docs/14/pgpr=
ewarm.html</a><br></div><div><br></div><div>pg_prewarm is probably what you=
 want.=C2=A0 Don&#39;t know if RDS Postgresql supports it or not, though.</=
div><div>=C2=A0</div><blockquote class=3D"gmail_quote" style=3D"margin:0px =
0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
<br>
I think vacuum full would certainly defeat the lazy loading since it<br>
would copy all of the table data, but that may take a very long time<br>
to run. I think vacuum analyze only scans a subset of rows but I might<br>
be wrong about that.<br>
<br>
Best, Sam<br>
<br>
On Wed, Jun 5, 2024 at 10:09=E2=80=AFPM Jeremy Smith &lt;<a href=3D"mailto:=
jeremy@musicsmith.net" target=3D"_blank">jeremy@musicsmith.net</a>&gt; wrot=
e:<br>
&gt;<br>
&gt; On Wed, Jun 5, 2024 at 4:23=E2=80=AFAM Sam Kidman &lt;<a href=3D"mailt=
o:sam@fresho.com" target=3D"_blank">sam@fresho.com</a>&gt; wrote:<br>
&gt;<br>
&gt; &gt; We get very poor performance in the staging environment after thi=
s<br>
&gt; &gt; restore takes place - after some usage it seems to get better per=
haps<br>
&gt; &gt; because of caching.<br>
&gt; &gt;<br>
&gt;<br>
&gt; This is due to the way that RDS restores snapshots.<br>
&gt;<br>
&gt; From the docs (<a href=3D"https://docs.aws.amazon.com/AmazonRDS/latest=
/UserGuide/USER_RestoreFromSnapshot.html" rel=3D"noreferrer" target=3D"_bla=
nk">https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_RestoreFrom=
Snapshot.html</a>):<br>
&gt;<br>
&gt; You can use the restored DB instance as soon as its status is<br>
&gt; available. The DB instance continues to load data in the background.<b=
r>
&gt; This is known as lazy loading.<br>
&gt;<br>
&gt; If you access data that hasn&#39;t been loaded yet, the DB instance<br=
>
&gt; immediately downloads the requested data from Amazon S3, and then<br>
&gt; continues loading the rest of the data in the background.<br>
&gt;<br>
&gt;<br>
&gt;<br>
&gt;=C2=A0 =C2=A0-Jeremy<br>
<br>
<br>
</blockquote></div></div>

--0000000000006ba438061a4bbc52--