From: Philip Semanchuk <philip@americanefficient.com>
To: Igor Gois <igor@bixtecnologia.com.br>
Cc: Julien Rouhaud <rjuju123@gmail.com>
Cc: Maurici Meneghetti <maurici.meneghetti@bixtecnologia.com.br>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Postgres performance comparing GCP and AWS
Date: Thu, 25 Feb 2021 13:13:33 -0500
Message-ID: <4BD5DB5D-972B-4683-8E2B-88EEDBDFDC7D@americanefficient.com> (raw)
In-Reply-To: <CAH1yB+XWBG9DVRFg825TxvNr6stTWJxZsAZ05EZ8GG3oMk47dQ@mail.gmail.com>
References: <CAMzrzcaeYtkJN+tQWpKhpdaAeD-EjxJ3Pfcpfy-QsOKnrdy17w@mail.gmail.com>
<CAOBaU_ZNF+uE8OED3GbLYEuJULJRAYcR1fkW=dFjB57HVr6P1g@mail.gmail.com>
<CAH1yB+XWBG9DVRFg825TxvNr6stTWJxZsAZ05EZ8GG3oMk47dQ@mail.gmail.com>
> On Feb 24, 2021, at 10:11 AM, Igor Gois <igor@bixtecnologia.com.br> wrote:
>
> Hi, Julien
>
> Your hypothesis about network transfer makes sense. The query returns a big size byte array blobs.
>
> Is there a way to test the network speed against the instances? I have access to the network speed in gcp (5 Mb/s), but don't have access in aws rds.
Perhaps what you should run is EXPLAIN ANALYZE SELECT...? My understanding is that EXPLAIN ANALYZE executes the query but discards the results. That doesn’t tell you the network speed of your AWS instance, but it does isolate the query execution speed (which is what I think you’re trying to measure) from the network speed.
Hope this is useful.
Cheers
Philip
>
>
> Em qua., 24 de fev. de 2021 às 10:35, Julien Rouhaud <rjuju123@gmail.com> escreveu:
> Hi,
>
> On Wed, Feb 24, 2021 at 6:14 AM Maurici Meneghetti
> <maurici.meneghetti@bixtecnologia.com.br> wrote:
> >
> > I have 2 postgres instances created from the same dump (backup), one on a GCP VM and the other on AWS RDS. The first instance takes 18 minutes and the second one takes less than 20s to run this simples query:
> > SELECT "Id", "DateTime", "SignalRegisterId", "Raw" FROM "SignalRecordsBlobs" WHERE "SignalSettingId" = 103 AND "DateTime" BETWEEN '2019-11-28T14:00:12.540200000' AND '2020-07-23T21:12:32.249000000';
> > I’ve run this query a few times to make sure both should be reading data from cache.
> > I expect my postgres on GPC to be at least similar to the one managed by AWS RDS so that I can work on improvements parallelly and compare.
> >
> > DETAILS:
> > [...]
> > Planning time: 456.315 ms
> > Execution time: 776.976 ms
> >
> > Query explain for Postgres on AWS RDS:
> > [...]
> > Planning time: 0.407 ms
> > Execution time: 14.87 ms
>
> Those queries were executed in respectively ~1s and ~15ms (one thing
> to note is that the slower one had less data in cache, which may or
> may note account for the difference). Does those plans reflect the
> reality of your slow executions? If yes it's likely due to quite slow
> network transfer. Otherwise we would need an explain plan from the
> slow execution, for which auto_explain can help you. See
> https://www.postgresql.org/docs/11/auto-explain.html for more details.
>
>
> --
> Att,
>
> Igor Gois | Sócio Consultor
> (48) 99169-9889 | Skype: igor_msg
> Site | Blog | LinkedIn | Facebook | Instagram
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: philip@americanefficient.com, igor@bixtecnologia.com.br, rjuju123@gmail.com, maurici.meneghetti@bixtecnologia.com.br, pgsql-performance@lists.postgresql.org
Subject: Re: Postgres performance comparing GCP and AWS
In-Reply-To: <4BD5DB5D-972B-4683-8E2B-88EEDBDFDC7D@americanefficient.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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