Have you tried to set the instance running on GCP to have similar
shared_buffers as the AWS database ?
What you described has a much lower cache hit rate on GCS and 2X the
shared buffers on AWS which could well explain much of the difference
in execution times.
DETAILS:
Query explain for Postgres
Since this is a comparison to RDS, and the goal presumably is to make the
test as even as possible, you will want to pay attention to the network IO
capacity for the client and the server in both tests.
For RDS, you will be unable to run the client software locally on the
server hardware, so you s
Philip,
The results in first email in this thread were using explain analyze.
I thought that you asked to run using only 'explain'. My bad.
The point is, the execution time with explain analyze is less the 1 second.
But the actual execution time (calculated from the python client) is 24
seconds
Hi, Philip
We ran: EXPLAIN (FORMAT JSON) SELECT "Id", "DateTime", "SignalRegisterId",
"Raw" FROM "SignalRecordsBlobs" WHERE "SignalSettingId" = 103 AND
"DateTime" BETWEEN '2019-11-28T14:00:12.54020' AND
'2020-07-23T21:12:32.24900';
but it was really fast. I think the results were discarde
> On Feb 25, 2021, at 4:04 PM, Igor Gois wrote:
>
> Philip,
>
> The results in first email in this thread were using explain analyze.
>
> I thought that you asked to run using only 'explain'. My bad.
>
> The point is, the execution time with explain analyze is less the 1 second.
> But the
> On Feb 25, 2021, at 3:46 PM, Igor Gois wrote:
>
> Hi, Philip
>
> We ran: EXPLAIN (FORMAT JSON) SELECT "Id", "DateTime", "SignalRegisterId",
> "Raw" FROM "SignalRecordsBlobs" WHERE "SignalSettingId" = 103 AND "DateTime"
> BETWEEN '2019-11-28T14:00:12.54020' AND '2020-07-23T21:12:32.249
> On Feb 24, 2021, at 10:11 AM, Igor Gois 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),
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.
[image: image.png]
Thanks in advanc
> I expect my postgres on GPC to be at least similar to the one managed by
AWS RDS
imho:
- on Google Cloud you can test with "Cloud SQL for Postgresql" (
https://cloud.google.com/sql/docs/postgres )
- on Google Compute Engine ( VM ): you have to tune the disks ; linux ;
file system ; scheduler
Hi,
On Wed, Feb 24, 2021 at 6:14 AM Maurici Meneghetti
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", "
Hi Maurici,
as a starting point: can you make sure your GPC instance is configured in
the same way AWS is?
Once you do it, repeat the tests, and post the outcome.
Thanks,
Milos
On Tue, Feb 23, 2021 at 11:14 PM Maurici Meneghetti <
maurici.meneghe...@bixtecnologia.com.br> wrote:
> Hi everyone,
Hi Maurici,
in my experience the key factor about speed in big queries is sequential
scan. There is a huge variance in how the system is tuned. In some cases
I cannot read more than 10 MB/s, in others I get to expect 20-40 MB/s.
But then, when things are tuned well and the parallel workers set
Hi everyone,
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"
13 matches
Mail list logo
