2

TL;DR: Solution

The problem was that one instance hat magnetic storage attached and was therefore significantly slower.

Original Question

I have two identical AWS RDS PostgreSQL 10.6 databases running on separate t2.medium instances in different VPC subnets, but within the same availability zone. They represent our staging/production environment, respectively. The only difference between both instances I can see is that the production instance is multi-AZ, while the staging instance is not.

When I run the simple query

select * from auva_orders

on a table with ~18 mio. rows each, I see dramatic runtime differences:

  • staging: 1:30m - 1:50m
  • production: ~0:30m

Both tables are continuously populated with new entries by a Kafka consumer, contain nearly the same number of rows and have identical indices:

Indexes:
    "auva_order_pk" PRIMARY KEY, btree (topic_partition, topic_offset)
    "auvaOrderCompanyCountryOrderdate" btree (company_code, country_code, orderdate)

If I EXPLAIN (ANALYSE, BUFFERS) the queries for each environment, I get the following results:

  • staging:
EXPLAIN (ANALYZE, BUFFERS) select count(*) from auva_order;
                                                          QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=421029.42..421029.43 rows=1 width=8) (actual time=97541.306..97541.306 rows=1 loops=1)
   Buffers: shared hit=52389 read=273887 dirtied=129
   ->  Gather  (cost=421029.21..421029.42 rows=2 width=8) (actual time=97540.845..97542.098 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         Buffers: shared hit=52389 read=273887 dirtied=129
         ->  Partial Aggregate  (cost=420029.21..420029.22 rows=1 width=8) (actual time=97538.854..97538.855 rows=1 loops=3)
               Buffers: shared hit=52389 read=273887 dirtied=129
               ->  Parallel Seq Scan on auva_order  (cost=0.00..401278.57 rows=7500257 width=0) (actual time=4.692..95822.212.
. rows=6000544 loops=3)
                     Buffers: shared hit=52389 read=273887 dirtied=129
 Planning time: 0.069 ms
 Execution time: 97542.148 ms
(12 rows)
  • production:
EXPLAIN (ANALYZE, BUFFERS) select count(*) from auva_order;
                                                          QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=417818.52..417818.53 rows=1 width=8) (actual time=30899.683..30899.684 rows=1 loops=1)
   Buffers: shared hit=60023 read=263042 dirtied=1
   ->  Gather  (cost=417818.30..417818.51 rows=2 width=8) (actual time=30898.544..30900.360 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         Buffers: shared hit=60023 read=263042 dirtied=1
         ->  Partial Aggregate  (cost=416818.30..416818.31 rows=1 width=8) (actual time=30897.146..30897.147 rows=1 loops=3)
               Buffers: shared hit=60023 read=263042 dirtied=1
               ->  Parallel Seq Scan on auva_order  (cost=0.00..398067.64 rows=7500264 width=0) (actual time=0.312..29168.159.
. rows=6000545 loops=3)
                     Buffers: shared hit=60023 read=263042 dirtied=1
 Planning time: 0.070 ms
 Execution time: 30900.420 ms
(12 rows)

I have no idea what causes the differences in runtime. Our staging instance might be under heavier load during the current development phase, but I don't think this explains a factor of 3-4 in runtime. The CPU credits of the burstable AWS EC2 instances are "full", so there is no shortage of CPU power in any of the instances. Any ideas what might be the cause?

Improve this question
3
  • There could be a few possible resource issues. 1. Is the problem intermittent, or do you get repeatable results? 2. Compare I/O activity graph for both instances at the time of testing. There might be concurrent write operations which slow it down. Do the same for CPU usage. 3. Analyze concurrent locking (use pg_locks view).
    – filiprem
    Mar 14, 2019 at 15:00
  • Does RDS let you turn track_io_timing on? If so, do that and repeat the EXPLAIN (ANALYZE, BUFFERS). Also, just because your CPU credits are full, doesn't mean there isn't a co-tenant who also has their CPU credits full and is in the process of using them. If you want highly predictable performance, don't use t2 instances.
    – jjanes
    Mar 14, 2019 at 16:50
  • Now this is embarrassing. I just found out that my two instances are not in fact identical: The staging instance has magnetic storage attached, while the other works on SSDs. Thank you for your valuable hints, @filiprem and @jjanes!
    – jurgispods
    Mar 15, 2019 at 11:37

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.