> 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.meneghe...@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 >
