Thread

1. Postgres performance comparing GCP and AWS

   Maurici Meneghetti <maurici.meneghetti@bixtecnologia.com.br> — 2021-02-23T18:12:19Z

   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" 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:Query explain for Postgres on GCP VM:*Bitmap Heap Scan on
   SignalRecordsBlobs SignalRecordsBlobs  (cost=18.80..2480.65 rows=799
   width=70) (actual time=216.766..776.032 rows=5122 loops=1)
       Filter: (("DateTime" >= \'2019-11-28 14:00:12.5402\'::timestamp without
   time zone) AND ("DateTime" <= \'2020-07-23 21:12:32.249\'::timestamp
   without time zone))
       Heap Blocks: exact=5223
       Buffers: shared hit=423 read=4821
     ->  Bitmap Index Scan on IDX_SignalRecordsBlobs_SignalSettingId
    (cost=0.00..18.61 rows=824 width=0) (actual time=109.000..109.001
   rows=5228 loops=1)
             Index Cond: ("SignalSettingId" = 103)
             Buffers: shared hit=3 read=18
   Planning time: 456.315 ms
   Execution time: 776.976 ms
   
   
   *Query explain for Postgres on AWS RDS:*Bitmap Heap Scan on
   SignalRecordsBlobs SignalRecordsBlobs  (cost=190.02..13204.28 rows=6213
   width=69) (actual time=2.215..14.505 rows=5122 loops=1)
       Filter: (("DateTime" >= \'2019-11-28 14:00:12.5402\'::timestamp without
   time zone) AND ("DateTime" <= \'2020-07-23 21:12:32.249\'::timestamp
   without time zone))
       Heap Blocks: exact=5209
       Buffers: shared hit=3290 read=1948
     ->  Bitmap Index Scan on IDX_SignalRecordsBlobs_SignalSettingId
    (cost=0.00..188.46 rows=6405 width=0) (actual time=1.159..1.159 rows=5228
   loops=1)
             Index Cond: ("SignalSettingId" = 103)
             Buffers: shared hit=3 read=26
   Planning time: 0.407 ms
   Execution time: 14.87 ms
   
   
   *PostgreSQL version number running:• VM on GCP*: PostgreSQL 11.10 (Debian
   11.10-0+deb10u1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6)
   8.3.0, 64-bit
   *• Managed by RDS on AWS:* PostgreSQL 11.10 on x86_64-pc-linux-gnu,
   compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit
   
   
   *How PostgreSQL was installed:• VM on GCP*: Already installed when created
   VM running Debian on Google Console.
   *• Managed by RDS on AWS:* RDS managed the installation.
   
   
   *Changes made to the settings in the postgresql.conf file:*Here are some
   postgres parameters that might be useful:
   *Instance on VM on GCP (2 vCPUs, 2 GB memory, 800 GB disk):*
   • effective_cache_size: 1496MB
   • maintenance_work_mem: 255462kB (close to 249MB)
   • max_wal_size: 1GB
   • min_wal_size: 512MB
   • shared_buffers: 510920kB (close to 499MB)
   • max_locks_per_transaction 1000
   • wal_buffers: 15320kB (close to 15MB)
   • work_mem: 2554kB
   • effective_io_concurrency: 200
   • dynamic_shared_memory_type: posix
   On this instance we installed a postgres extension called timescaledb to
   gain performance on other tables. Some of these parameters were set using
   recommendations from that extension.
   
   *Instance managed by RDS (2 vCPUs, 2 GiB RAM, 250GB disk, 750 de IOPS):*
   • effective_cache_size: 1887792kB (close to 1844MB)
   • maintenance_work_mem: 64MB
   • max_wal_size: 2GB
   • min_wal_size: 192MB
   • shared_buffers: 943896kB (close to 922MB)
   • max_locks_per_transaction 64
   
   
   *Operating system and version by runing "uname -a":• VM on GCP:* Linux
   {{{my instance name}}} 4.19.0-14-cloud-amd64 #1 SMP Debian 4.19.171-2
   (2021-01-30) x86_64 GNU/Linux
   *• Managed by AWS RDS:* Aparently Red Hay as shown using SELECT version();
   
   *Program used to connect to PostgreSQL:* Python psycopg2.connect() to
   create the connection and pandas read_sql_query() to query using that
   connection.
   
   Thanks in advance
   

2. Re: Postgres performance comparing GCP and AWS

   Gunther <raj@gusw.net> — 2021-02-24T13:10:20Z

   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 in, I 
   see the throughput spike to 100-200 MB/s.
   
   You may have to enable the parallel workers in your postgresql.conf
   
   So, to me, this is what you want to check first. While the query runs, 
   have both iostat and top running, with top -j or -c or -a or whatever it 
   is on that particular OS to see the detail info about the process. 
   Perhaps even -H to see threads.
   
   Then you should see good flow with high read speed and reasonable CPU 
   load %. If you get low read speed and low CPU that is a sign of IO 
   blockage somewhere. If you get high CPU and low IO, that's a planning 
   mistake (the nested loop trap). You don't have that here apparently. But 
   index scans I have seen with much worse IO throughput than seq table 
   scans. Not sure.
   
   Also, on AWS you need to be sure you have enough IOPS provisioned on 
   your EBS (I use gp3 now where you can have up to 10k IOPS) and also 
   check bus throughput of the EC2 instance. Needless to say you don't want 
   a t* instance where you have a limited burst CPU capacity only.
   
   regards,
   -Gunther
   
   On 2/23/2021 1:12 PM, Maurici Meneghetti wrote:
   > 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" 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:
   > Query explain for Postgres on GCP VM:
   > *Bitmap Heap Scan on SignalRecordsBlobs SignalRecordsBlobs 
   >  (cost=18.80..2480.65 rows=799 width=70) (actual time=216.766..776.032 
   > rows=5122 loops=1)
   >     Filter: (("DateTime" >= \'2019-11-28 14:00:12.5402\'::timestamp 
   > without time zone) AND ("DateTime" <= \'2020-07-23 
   > 21:12:32.249\'::timestamp without time zone))
   >     Heap Blocks: exact=5223
   >     Buffers: shared hit=423 read=4821
   >   ->  Bitmap Index Scan on IDX_SignalRecordsBlobs_SignalSettingId 
   >  (cost=0.00..18.61 rows=824 width=0) (actual time=109.000..109.001 
   > rows=5228 loops=1)
   >           Index Cond: ("SignalSettingId" = 103)
   >           Buffers: shared hit=3 read=18
   > Planning time: 456.315 ms
   > Execution time: 776.976 ms
   >
   > *Query explain for Postgres on AWS RDS:
   > *Bitmap Heap Scan on SignalRecordsBlobs SignalRecordsBlobs 
   >  (cost=190.02..13204.28 rows=6213 width=69) (actual time=2.215..14.505 
   > rows=5122 loops=1)
   >     Filter: (("DateTime" >= \'2019-11-28 14:00:12.5402\'::timestamp 
   > without time zone) AND ("DateTime" <= \'2020-07-23 
   > 21:12:32.249\'::timestamp without time zone))
   >     Heap Blocks: exact=5209
   >     Buffers: shared hit=3290 read=1948
   >   ->  Bitmap Index Scan on IDX_SignalRecordsBlobs_SignalSettingId 
   >  (cost=0.00..188.46 rows=6405 width=0) (actual time=1.159..1.159 
   > rows=5228 loops=1)
   >           Index Cond: ("SignalSettingId" = 103)
   >           Buffers: shared hit=3 read=26
   > Planning time: 0.407 ms
   > Execution time: 14.87 ms
   >
   > *PostgreSQL version number running:
   > • VM on GCP*: PostgreSQL 11.10 (Debian 11.10-0+deb10u1) on 
   > x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
   > *• Managed by RDS on AWS:* PostgreSQL 11.10 on x86_64-pc-linux-gnu, 
   > compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit
   >
   > *How PostgreSQL was installed:
   > • VM on GCP*: Already installed when created VM running Debian on 
   > Google Console.
   > *• Managed by RDS on AWS:* RDS managed the installation.
   >
   > *Changes made to the settings in the postgresql.conf file:
   > *Here are some postgres parameters that might be useful:
   > *Instance on VM on GCP (2 vCPUs, 2 GB memory, 800 GB disk):*
   > • effective_cache_size: 1496MB
   > • maintenance_work_mem: 255462kB (close to 249MB)
   > • max_wal_size: 1GB
   > • min_wal_size: 512MB
   > • shared_buffers: 510920kB (close to 499MB)
   > • max_locks_per_transaction 1000
   > • wal_buffers: 15320kB (close to 15MB)
   > • work_mem: 2554kB
   > • effective_io_concurrency: 200
   > • dynamic_shared_memory_type: posix
   > On this instance we installed a postgres extension called timescaledb 
   > to gain performance on other tables. Some of these parameters were set 
   > using recommendations from that extension.
   >
   > *Instance managed by RDS (2 vCPUs, 2 GiB RAM, 250GB disk, 750 de IOPS):*
   > • effective_cache_size: 1887792kB (close to 1844MB)
   > • maintenance_work_mem: 64MB
   > • max_wal_size: 2GB
   > • min_wal_size: 192MB
   > • shared_buffers: 943896kB (close to 922MB)
   > • max_locks_per_transaction 64
   >
   > *Operating system and version by runing "uname -a":
   > • VM on GCP:* Linux {{{my instance name}}} 4.19.0-14-cloud-amd64 #1 
   > SMP Debian 4.19.171-2 (2021-01-30) x86_64 GNU/Linux
   > *• Managed by AWS RDS:* Aparently Red Hay as shown using SELECT version();
   >
   > *Program used to connect to PostgreSQL:* Python psycopg2.connect() to 
   > create the connection and pandas read_sql_query() to query using that 
   > connection.
   >
   > Thanks in advance
   

3. Re: Postgres performance comparing GCP and AWS

   Milos Babic <milos.babic@gmail.com> — 2021-02-24T13:15:36Z

   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.meneghetti@bixtecnologia.com.br> wrote:
   
   > 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" 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:Query explain for Postgres on GCP VM:*Bitmap Heap Scan on
   > SignalRecordsBlobs SignalRecordsBlobs  (cost=18.80..2480.65 rows=799
   > width=70) (actual time=216.766..776.032 rows=5122 loops=1)
   >     Filter: (("DateTime" >= \'2019-11-28 14:00:12.5402\'::timestamp
   > without time zone) AND ("DateTime" <= \'2020-07-23
   > 21:12:32.249\'::timestamp without time zone))
   >     Heap Blocks: exact=5223
   >     Buffers: shared hit=423 read=4821
   >   ->  Bitmap Index Scan on IDX_SignalRecordsBlobs_SignalSettingId
   >  (cost=0.00..18.61 rows=824 width=0) (actual time=109.000..109.001
   > rows=5228 loops=1)
   >           Index Cond: ("SignalSettingId" = 103)
   >           Buffers: shared hit=3 read=18
   > Planning time: 456.315 ms
   > Execution time: 776.976 ms
   >
   >
   > *Query explain for Postgres on AWS RDS:*Bitmap Heap Scan on
   > SignalRecordsBlobs SignalRecordsBlobs  (cost=190.02..13204.28 rows=6213
   > width=69) (actual time=2.215..14.505 rows=5122 loops=1)
   >     Filter: (("DateTime" >= \'2019-11-28 14:00:12.5402\'::timestamp
   > without time zone) AND ("DateTime" <= \'2020-07-23
   > 21:12:32.249\'::timestamp without time zone))
   >     Heap Blocks: exact=5209
   >     Buffers: shared hit=3290 read=1948
   >   ->  Bitmap Index Scan on IDX_SignalRecordsBlobs_SignalSettingId
   >  (cost=0.00..188.46 rows=6405 width=0) (actual time=1.159..1.159 rows=5228
   > loops=1)
   >           Index Cond: ("SignalSettingId" = 103)
   >           Buffers: shared hit=3 read=26
   > Planning time: 0.407 ms
   > Execution time: 14.87 ms
   >
   >
   > *PostgreSQL version number running:• VM on GCP*: PostgreSQL 11.10 (Debian
   > 11.10-0+deb10u1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6)
   > 8.3.0, 64-bit
   > *• Managed by RDS on AWS:* PostgreSQL 11.10 on x86_64-pc-linux-gnu,
   > compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit
   >
   >
   > *How PostgreSQL was installed:• VM on GCP*: Already installed when
   > created VM running Debian on Google Console.
   > *• Managed by RDS on AWS:* RDS managed the installation.
   >
   >
   > *Changes made to the settings in the postgresql.conf file:*Here are some
   > postgres parameters that might be useful:
   > *Instance on VM on GCP (2 vCPUs, 2 GB memory, 800 GB disk):*
   > • effective_cache_size: 1496MB
   > • maintenance_work_mem: 255462kB (close to 249MB)
   > • max_wal_size: 1GB
   > • min_wal_size: 512MB
   > • shared_buffers: 510920kB (close to 499MB)
   > • max_locks_per_transaction 1000
   > • wal_buffers: 15320kB (close to 15MB)
   > • work_mem: 2554kB
   > • effective_io_concurrency: 200
   > • dynamic_shared_memory_type: posix
   > On this instance we installed a postgres extension called timescaledb to
   > gain performance on other tables. Some of these parameters were set using
   > recommendations from that extension.
   >
   > *Instance managed by RDS (2 vCPUs, 2 GiB RAM, 250GB disk, 750 de IOPS):*
   > • effective_cache_size: 1887792kB (close to 1844MB)
   > • maintenance_work_mem: 64MB
   > • max_wal_size: 2GB
   > • min_wal_size: 192MB
   > • shared_buffers: 943896kB (close to 922MB)
   > • max_locks_per_transaction 64
   >
   >
   > *Operating system and version by runing "uname -a":• VM on GCP:* Linux
   > {{{my instance name}}} 4.19.0-14-cloud-amd64 #1 SMP Debian 4.19.171-2
   > (2021-01-30) x86_64 GNU/Linux
   > *• Managed by AWS RDS:* Aparently Red Hay as shown using SELECT version();
   >
   > *Program used to connect to PostgreSQL:* Python psycopg2.connect() to
   > create the connection and pandas read_sql_query() to query using that
   > connection.
   >
   > Thanks in advance
   >
   

4. Re: Postgres performance comparing GCP and AWS

   Julien Rouhaud <rjuju123@gmail.com> — 2021-02-24T13:35:12Z

   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.
   
   
   
   

5. Re: Postgres performance comparing GCP and AWS

   Igor Gois <igor@bixtecnologia.com.br> — 2021-02-24T15:11:30Z

   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 advance
   
   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  <https://bixtecnologia.com.br/>| Blog
   <https://www.bixtecnologia.com.br/blog/> | LinkedIn
   <https://www.linkedin.com/company/bixtecnologia/>| Facebook
   <https://www.facebook.com/bix.tecnologia>| Instagram
   <https://www.instagram.com/bixtecnologia/>
   

6. Re: Postgres performance comparing GCP and AWS

   Imre Samu <pella.samu@gmail.com> — 2021-02-24T15:16:25Z

   > 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 ;
            and it is a complex task
   
   imho:  select the perfect disk types for the postgresql data  ( and create
   a fast RAID )
   https://cloud.google.com/compute/docs/disks
   
   *Compute Engine offers several types of storage options for your instances.
   Each of the following storage options has unique price and performance
   characteristics:*
   
   *- Zonal persistent disk: Efficient, reliable block storage.*
   
   *- Regional persistent disk: Regional block storage replicated in two
   zones.*
   *- Local SSD: High performance, transient, local block storage.*
   *- Cloud Storage buckets: Affordable object storage.*
   *- Filestore: High performance file storage for Google Cloud users.*
   
   
   regards,
    Imre
   
   
   Maurici Meneghetti <maurici.meneghetti@bixtecnologia.com.br> ezt írta
   (időpont: 2021. febr. 23., K, 23:14):
   
   > 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" 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:Query explain for Postgres on GCP VM:*Bitmap Heap Scan on
   > SignalRecordsBlobs SignalRecordsBlobs  (cost=18.80..2480.65 rows=799
   > width=70) (actual time=216.766..776.032 rows=5122 loops=1)
   >     Filter: (("DateTime" >= \'2019-11-28 14:00:12.5402\'::timestamp
   > without time zone) AND ("DateTime" <= \'2020-07-23
   > 21:12:32.249\'::timestamp without time zone))
   >     Heap Blocks: exact=5223
   >     Buffers: shared hit=423 read=4821
   >   ->  Bitmap Index Scan on IDX_SignalRecordsBlobs_SignalSettingId
   >  (cost=0.00..18.61 rows=824 width=0) (actual time=109.000..109.001
   > rows=5228 loops=1)
   >           Index Cond: ("SignalSettingId" = 103)
   >           Buffers: shared hit=3 read=18
   > Planning time: 456.315 ms
   > Execution time: 776.976 ms
   >
   >
   > *Query explain for Postgres on AWS RDS:*Bitmap Heap Scan on
   > SignalRecordsBlobs SignalRecordsBlobs  (cost=190.02..13204.28 rows=6213
   > width=69) (actual time=2.215..14.505 rows=5122 loops=1)
   >     Filter: (("DateTime" >= \'2019-11-28 14:00:12.5402\'::timestamp
   > without time zone) AND ("DateTime" <= \'2020-07-23
   > 21:12:32.249\'::timestamp without time zone))
   >     Heap Blocks: exact=5209
   >     Buffers: shared hit=3290 read=1948
   >   ->  Bitmap Index Scan on IDX_SignalRecordsBlobs_SignalSettingId
   >  (cost=0.00..188.46 rows=6405 width=0) (actual time=1.159..1.159 rows=5228
   > loops=1)
   >           Index Cond: ("SignalSettingId" = 103)
   >           Buffers: shared hit=3 read=26
   > Planning time: 0.407 ms
   > Execution time: 14.87 ms
   >
   >
   > *PostgreSQL version number running:• VM on GCP*: PostgreSQL 11.10 (Debian
   > 11.10-0+deb10u1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6)
   > 8.3.0, 64-bit
   > *• Managed by RDS on AWS:* PostgreSQL 11.10 on x86_64-pc-linux-gnu,
   > compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit
   >
   >
   > *How PostgreSQL was installed:• VM on GCP*: Already installed when
   > created VM running Debian on Google Console.
   > *• Managed by RDS on AWS:* RDS managed the installation.
   >
   >
   > *Changes made to the settings in the postgresql.conf file:*Here are some
   > postgres parameters that might be useful:
   > *Instance on VM on GCP (2 vCPUs, 2 GB memory, 800 GB disk):*
   > • effective_cache_size: 1496MB
   > • maintenance_work_mem: 255462kB (close to 249MB)
   > • max_wal_size: 1GB
   > • min_wal_size: 512MB
   > • shared_buffers: 510920kB (close to 499MB)
   > • max_locks_per_transaction 1000
   > • wal_buffers: 15320kB (close to 15MB)
   > • work_mem: 2554kB
   > • effective_io_concurrency: 200
   > • dynamic_shared_memory_type: posix
   > On this instance we installed a postgres extension called timescaledb to
   > gain performance on other tables. Some of these parameters were set using
   > recommendations from that extension.
   >
   > *Instance managed by RDS (2 vCPUs, 2 GiB RAM, 250GB disk, 750 de IOPS):*
   > • effective_cache_size: 1887792kB (close to 1844MB)
   > • maintenance_work_mem: 64MB
   > • max_wal_size: 2GB
   > • min_wal_size: 192MB
   > • shared_buffers: 943896kB (close to 922MB)
   > • max_locks_per_transaction 64
   >
   >
   > *Operating system and version by runing "uname -a":• VM on GCP:* Linux
   > {{{my instance name}}} 4.19.0-14-cloud-amd64 #1 SMP Debian 4.19.171-2
   > (2021-01-30) x86_64 GNU/Linux
   > *• Managed by AWS RDS:* Aparently Red Hay as shown using SELECT version();
   >
   > *Program used to connect to PostgreSQL:* Python psycopg2.connect() to
   > create the connection and pandas read_sql_query() to query using that
   > connection.
   >
   > Thanks in advance
   >
   

7. Re: Postgres performance comparing GCP and AWS

   Philip Semanchuk <philip@americanefficient.com> — 2021-02-25T18:13:33Z

   
   > 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
   > 
   >   
   
   
   
   
   

8. Re: Postgres performance comparing GCP and AWS

   Igor Gois <igor@bixtecnologia.com.br> — 2021-02-25T20:46:58Z

   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.540200000' AND
   '2020-07-23T21:12:32.249000000';
   
   but it was really fast. I think the results were discarded.
   
   AWS Execution time select without explain: 24.96505s (calculated in python
   client)
   AWS Execution time select with explain but without analyze: 0.03876s
   (calculated in python client)
   
   https://explain.depesz.com/s/5HRO
   
   Thanks in advance
   
   Em qui., 25 de fev. de 2021 às 15:13, Philip Semanchuk <
   philip@americanefficient.com> escreveu:
   
   >
   >
   > > 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
   > >
   > >
   >
   >
   
   -- 
   *Att,*
   
   *Igor Gois | Sócio Consultor*
   (48) 99169-9889 | Skype: igor_msg
   Site  <https://bixtecnologia.com.br/>| Blog
   <https://www.bixtecnologia.com.br/blog/> | LinkedIn
   <https://www.linkedin.com/company/bixtecnologia/>| Facebook
   <https://www.facebook.com/bix.tecnologia>| Instagram
   <https://www.instagram.com/bixtecnologia/>
   

9. Re: Postgres performance comparing GCP and AWS

   Philip Semanchuk <philip@americanefficient.com> — 2021-02-25T20:53:13Z

   
   > On Feb 25, 2021, at 3:46 PM, Igor Gois <igor@bixtecnologia.com.br> 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.540200000' AND '2020-07-23T21:12:32.249000000';
   > 
   > but it was really fast. I think the results were discarded.
   
   EXPLAIN and EXPLAIN ANALYZE are different in an important way. EXPLAIN merely plans the query, EXPLAIN ANALYZE plans *and executes* the query. From the doc —
   
   "The ANALYZE option causes the statement to be actually executed, not only planned....Keep in mind that the statement is actually executed when the ANALYZE option is used. Although EXPLAIN will discard any output that a SELECT would return, other side effects of the statement will happen as usual. “
   
   https://www.postgresql.org/docs/12/sql-explain.html
   
   
   > 
   > AWS Execution time select without explain: 24.96505s (calculated in python client)
   > AWS Execution time select with explain but without analyze: 0.03876s (calculated in python client)
   > 
   > https://explain.depesz.com/s/5HRO
   > 
   > Thanks in advance
   > 
   > 
   > Em qui., 25 de fev. de 2021 às 15:13, Philip Semanchuk <philip@americanefficient.com> escreveu:
   > 
   > 
   > > 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
   > > 
   > >   
   > 
   > 
   > 
   > -- 
   > Att,
   > 
   > Igor Gois | Sócio Consultor
   > (48) 99169-9889 | Skype: igor_msg
   > Site | Blog | LinkedIn | Facebook | Instagram
   > 
   >   
   
   
   
   
   

10. Re: Postgres performance comparing GCP and AWS

    Igor Gois <igor@bixtecnologia.com.br> — 2021-02-25T21:04:50Z

    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 (aws) and 300+ seconds in gcp
    
    Thank you
    
    Em qui., 25 de fev. de 2021 às 17:53, Philip Semanchuk <
    philip@americanefficient.com> escreveu:
    
    >
    >
    > > On Feb 25, 2021, at 3:46 PM, Igor Gois <igor@bixtecnologia.com.br>
    > 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.540200000' AND
    > '2020-07-23T21:12:32.249000000';
    > >
    > > but it was really fast. I think the results were discarded.
    >
    > EXPLAIN and EXPLAIN ANALYZE are different in an important way. EXPLAIN
    > merely plans the query, EXPLAIN ANALYZE plans *and executes* the query.
    > From the doc —
    >
    > "The ANALYZE option causes the statement to be actually executed, not only
    > planned....Keep in mind that the statement is actually executed when the
    > ANALYZE option is used. Although EXPLAIN will discard any output that a
    > SELECT would return, other side effects of the statement will happen as
    > usual. “
    >
    > https://www.postgresql.org/docs/12/sql-explain.html
    >
    >
    > >
    > > AWS Execution time select without explain: 24.96505s (calculated in
    > python client)
    > > AWS Execution time select with explain but without analyze: 0.03876s
    > (calculated in python client)
    > >
    > > https://explain.depesz.com/s/5HRO
    > >
    > > Thanks in advance
    > >
    > >
    > > Em qui., 25 de fev. de 2021 às 15:13, Philip Semanchuk <
    > philip@americanefficient.com> escreveu:
    > >
    > >
    > > > 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
    > > >
    > > >
    > >
    > >
    > >
    > > --
    > > Att,
    > >
    > > Igor Gois | Sócio Consultor
    > > (48) 99169-9889 | Skype: igor_msg
    > > Site | Blog | LinkedIn | Facebook | Instagram
    > >
    > >
    >
    >
    
    -- 
    *Att,*
    
    *Igor Gois | Sócio Consultor*
    (48) 99169-9889 | Skype: igor_msg
    Site  <https://bixtecnologia.com.br/>| Blog
    <https://www.bixtecnologia.com.br/blog/> | LinkedIn
    <https://www.linkedin.com/company/bixtecnologia/>| Facebook
    <https://www.facebook.com/bix.tecnologia>| Instagram
    <https://www.instagram.com/bixtecnologia/>
    

11. Re: Postgres performance comparing GCP and AWS

    Philip Semanchuk <philip@americanefficient.com> — 2021-02-25T22:32:32Z

    
    > On Feb 25, 2021, at 4:04 PM, Igor Gois <igor@bixtecnologia.com.br> 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 actual execution time (calculated from the python client) is 24 seconds (aws) and 300+ seconds in gcp
    
    Oh OK, sorry, I wasn’t following. Yes, network speed sounds like the source of the problem. 
    
    Under AWS sometimes we log into an EC2 instance if we have to run a query that generates a lot of data so that both server and client are inside AWS. If GCP has something similar to EC2, it might be an interesting experiment to run your query from there and see how much, if any, that changes the time it takes to get results.
    
    Hope this helps
    Philip
    
    
    
    > 
    > Em qui., 25 de fev. de 2021 às 17:53, Philip Semanchuk <philip@americanefficient.com> escreveu:
    > 
    > 
    > > On Feb 25, 2021, at 3:46 PM, Igor Gois <igor@bixtecnologia.com.br> 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.540200000' AND '2020-07-23T21:12:32.249000000';
    > > 
    > > but it was really fast. I think the results were discarded.
    > 
    > EXPLAIN and EXPLAIN ANALYZE are different in an important way. EXPLAIN merely plans the query, EXPLAIN ANALYZE plans *and executes* the query. From the doc —
    > 
    > "The ANALYZE option causes the statement to be actually executed, not only planned....Keep in mind that the statement is actually executed when the ANALYZE option is used. Although EXPLAIN will discard any output that a SELECT would return, other side effects of the statement will happen as usual. “
    > 
    > https://www.postgresql.org/docs/12/sql-explain.html
    > 
    > 
    > > 
    > > AWS Execution time select without explain: 24.96505s (calculated in python client)
    > > AWS Execution time select with explain but without analyze: 0.03876s (calculated in python client)
    > > 
    > > https://explain.depesz.com/s/5HRO
    > > 
    > > Thanks in advance
    > > 
    > > 
    > > Em qui., 25 de fev. de 2021 às 15:13, Philip Semanchuk <philip@americanefficient.com> escreveu:
    > > 
    > > 
    > > > 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
    > > > 
    > > >   
    > > 
    > > 
    > > 
    > > -- 
    > > Att,
    > > 
    > > Igor Gois | Sócio Consultor
    > > (48) 99169-9889 | Skype: igor_msg
    > > Site | Blog | LinkedIn | Facebook | Instagram
    > > 
    > >   
    > 
    > 
    > 
    > -- 
    > Att,
    > 
    > Igor Gois | Sócio Consultor
    > (48) 99169-9889 | Skype: igor_msg
    > Site | Blog | LinkedIn | Facebook | Instagram
    > 
    >   
    
    
    
    
    

12. Re: Postgres performance comparing GCP and AWS

    Justin Pitts <justinpitts@gmail.com> — 2021-02-26T01:06:15Z

    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 should plan to do the same for the GCP comparison.
    
    What is the machine size you are using for your RDS instance? Each machine
    size will specify CPU and RAM along with disk and network IO capacity.
    
    Is your GCP VM where you are running PG ( a GCP VM is the equivalent of an
    EC2 instance, by the way ) roughly equivalent to that RDS instance?
    
    Finally, is the network topology roughly equivalent? Are you performing
    these tests with the same region and/or availability zone?
    
    
    
    On Thu, Feb 25, 2021 at 3:32 PM Philip Semanchuk <
    philip@americanefficient.com> wrote:
    
    >
    >
    > > On Feb 25, 2021, at 4:04 PM, Igor Gois <igor@bixtecnologia.com.br>
    > 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 actual execution time (calculated from the python client)
    > is 24 seconds (aws) and 300+ seconds in gcp
    >
    > Oh OK, sorry, I wasn’t following. Yes, network speed sounds like the
    > source of the problem.
    >
    > Under AWS sometimes we log into an EC2 instance if we have to run a query
    > that generates a lot of data so that both server and client are inside AWS.
    > If GCP has something similar to EC2, it might be an interesting experiment
    > to run your query from there and see how much, if any, that changes the
    > time it takes to get results.
    >
    > Hope this helps
    > Philip
    >
    >
    >
    > >
    > > Em qui., 25 de fev. de 2021 às 17:53, Philip Semanchuk <
    > philip@americanefficient.com> escreveu:
    > >
    > >
    > > > On Feb 25, 2021, at 3:46 PM, Igor Gois <igor@bixtecnologia.com.br>
    > 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.540200000' AND
    > '2020-07-23T21:12:32.249000000';
    > > >
    > > > but it was really fast. I think the results were discarded.
    > >
    > > EXPLAIN and EXPLAIN ANALYZE are different in an important way. EXPLAIN
    > merely plans the query, EXPLAIN ANALYZE plans *and executes* the query.
    > From the doc —
    > >
    > > "The ANALYZE option causes the statement to be actually executed, not
    > only planned....Keep in mind that the statement is actually executed when
    > the ANALYZE option is used. Although EXPLAIN will discard any output that a
    > SELECT would return, other side effects of the statement will happen as
    > usual. “
    > >
    > > https://www.postgresql.org/docs/12/sql-explain.html
    > >
    > >
    > > >
    > > > AWS Execution time select without explain: 24.96505s (calculated in
    > python client)
    > > > AWS Execution time select with explain but without analyze: 0.03876s
    > (calculated in python client)
    > > >
    > > > https://explain.depesz.com/s/5HRO
    > > >
    > > > Thanks in advance
    > > >
    > > >
    > > > Em qui., 25 de fev. de 2021 às 15:13, Philip Semanchuk <
    > philip@americanefficient.com> escreveu:
    > > >
    > > >
    > > > > 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
    > > > >
    > > > >
    > > >
    > > >
    > > >
    > > > --
    > > > Att,
    > > >
    > > > Igor Gois | Sócio Consultor
    > > > (48) 99169-9889 | Skype: igor_msg
    > > > Site | Blog | LinkedIn | Facebook | Instagram
    > > >
    > > >
    > >
    > >
    > >
    > > --
    > > Att,
    > >
    > > Igor Gois | Sócio Consultor
    > > (48) 99169-9889 | Skype: igor_msg
    > > Site | Blog | LinkedIn | Facebook | Instagram
    > >
    > >
    >
    >
    >
    >
    

13. Re: Postgres performance comparing GCP and AWS

    Hannu Krosing <hannuk@google.com> — 2021-03-01T16:01:06Z

    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 on GCP VM:
        Buffers: shared hit=423 read=4821
    
    Query explain for Postgres on AWS RDS:
        Buffers: shared hit=3290 read=1948
    
    and the configuration :
    
    Instance on VM on GCP (2 vCPUs, 2 GB memory, 800 GB disk):
    • shared_buffers: 510920kB (close to 499MB)
    
    Instance managed by RDS (2 vCPUs, 2 GiB RAM, 250GB disk, 750 de IOPS):
    • shared_buffers: 943896kB (close to 922MB)
    
    
    Cheers
    Hannu
    
    On Fri, Feb 26, 2021 at 9:16 AM Justin Pitts <justinpitts@gmail.com> wrote:
    >
    > 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 should plan to do the same for the GCP comparison.
    >
    > What is the machine size you are using for your RDS instance? Each machine size will specify CPU and RAM along with disk and network IO capacity.
    >
    > Is your GCP VM where you are running PG ( a GCP VM is the equivalent of an EC2 instance, by the way ) roughly equivalent to that RDS instance?
    >
    > Finally, is the network topology roughly equivalent? Are you performing these tests with the same region and/or availability zone?
    >
    >
    >
    > On Thu, Feb 25, 2021 at 3:32 PM Philip Semanchuk <philip@americanefficient.com> wrote:
    >>
    >>
    >>
    >> > On Feb 25, 2021, at 4:04 PM, Igor Gois <igor@bixtecnologia.com.br> 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 actual execution time (calculated from the python client) is 24 seconds (aws) and 300+ seconds in gcp
    >>
    >> Oh OK, sorry, I wasn’t following. Yes, network speed sounds like the source of the problem.
    >>
    >> Under AWS sometimes we log into an EC2 instance if we have to run a query that generates a lot of data so that both server and client are inside AWS. If GCP has something similar to EC2, it might be an interesting experiment to run your query from there and see how much, if any, that changes the time it takes to get results.
    >>
    >> Hope this helps
    >> Philip
    >>
    >>
    >>
    >> >
    >> > Em qui., 25 de fev. de 2021 às 17:53, Philip Semanchuk <philip@americanefficient.com> escreveu:
    >> >
    >> >
    >> > > On Feb 25, 2021, at 3:46 PM, Igor Gois <igor@bixtecnologia.com.br> 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.540200000' AND '2020-07-23T21:12:32.249000000';
    >> > >
    >> > > but it was really fast. I think the results were discarded.
    >> >
    >> > EXPLAIN and EXPLAIN ANALYZE are different in an important way. EXPLAIN merely plans the query, EXPLAIN ANALYZE plans *and executes* the query. From the doc —
    >> >
    >> > "The ANALYZE option causes the statement to be actually executed, not only planned....Keep in mind that the statement is actually executed when the ANALYZE option is used. Although EXPLAIN will discard any output that a SELECT would return, other side effects of the statement will happen as usual. “
    >> >
    >> > https://www.postgresql.org/docs/12/sql-explain.html
    >> >
    >> >
    >> > >
    >> > > AWS Execution time select without explain: 24.96505s (calculated in python client)
    >> > > AWS Execution time select with explain but without analyze: 0.03876s (calculated in python client)
    >> > >
    >> > > https://explain.depesz.com/s/5HRO
    >> > >
    >> > > Thanks in advance
    >> > >
    >> > >
    >> > > Em qui., 25 de fev. de 2021 às 15:13, Philip Semanchuk <philip@americanefficient.com> escreveu:
    >> > >
    >> > >
    >> > > > 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
    >> > > >
    >> > > >
    >> > >
    >> > >
    >> > >
    >> > > --
    >> > > Att,
    >> > >
    >> > > Igor Gois | Sócio Consultor
    >> > > (48) 99169-9889 | Skype: igor_msg
    >> > > Site | Blog | LinkedIn | Facebook | Instagram
    >> > >
    >> > >
    >> >
    >> >
    >> >
    >> > --
    >> > Att,
    >> >
    >> > Igor Gois | Sócio Consultor
    >> > (48) 99169-9889 | Skype: igor_msg
    >> > Site | Blog | LinkedIn | Facebook | Instagram
    >> >
    >> >
    >>
    >>
    >>