Thanks, I'll check it out. On Mon, Sep 28, 2020 at 9:40 PM Prince Pathria <prince.path...@goevive.com> wrote:
> We faced a similar issue, adding RDS proxy in front of RDS Postgres can > help. > In our situation, there were a lot of connects/disconnects from Lambda > functions although concurrency of Lambda was 100 only. > And adding connection pooler(RDS proxy) helped us to reduce the CPU load > from 100% to 30% > > Happy to help :) > Prince Pathria Systems Engineer | Certified Kubernetes Administrator | > AWS Certified Solutions Architect Evive +91 9478670472 goevive.com > > > On Mon, Sep 28, 2020 at 9:21 PM aditya desai <admad...@gmail.com> wrote: > >> >>> Hi, >>> We have an application where one of the APIs calling queries(attached) >>> is spiking the CPU to 100% during load testing. >>> However, queries are making use of indexes(Bitmap Index and Bitmap Heap >>> scan though). When run separately on DB queries hardly take less than 200 >>> ms. Is CPU spiking due to Bitmap Heap Scan? >>> These queries are being called thousands of times. Application team says >>> they have handled connection pooling from the Application side. So there is >>> no connection pooling here from DB side. Current db instance size is >>> "db.m4.4xlarge" >>> 64 GB RAM 16 vCPU". >>> The Application dev team has primary keys and foreign keys on tables so >>> they are unable to partition the tables as well due to limitations of >>> postgres partitioning. Columns in WHERE clauses are not constant in all >>> queries to decide partition keys. >>> >>> 1. Does DB need more CPU considering this kind of load? >>> 2. Can the query be tuned further? It is already using indexes(Bitmap >>> though). >>> 3. Will connection pooling resolve the CPU Spike issues? >>> >>> Also pasting Query and plans below. >>> >>> ----------------------exampleCount 1. Without >>> internalexamplecode----------------------- >>> >>> lmp_examples=> explain analyze with exampleCount as ( select >>> examplestatuscode from example j where 1=1 and j.countrycode = 'AD' and >>> j.facilitycode in ('ABCD') and j.internalexamplecode in >>> ('005','006','007','005') and ((j.examplestartdatetime between '2020-05-18 >>> 00:00:00' and '2020-08-19 00:00:00' ) or j.examplestartdatetime IS NULL ) >>> group by j.examplestatuscode) >>> lmp_examples-> select js.examplestatuscode,COALESCE(count(*),0) >>> stat_count from exampleCount jc right outer join examplestatus js on >>> jc.examplestatuscode=js.examplestatuscode group by js.examplestatuscode ; >>> >>> >>> QUERY PLAN >>> >>> >>> >>> ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- >>> HashAggregate (cost=79353.80..79353.89 rows=9 width=12) (actual >>> time=88.847..88.850 rows=9 loops=1) >>> Group Key: js.examplestatuscode >>> CTE examplecount >>> -> HashAggregate (cost=79352.42..79352.46 rows=4 width=4) (actual >>> time=88.803..88.805 rows=5 loops=1) >>> Group Key: j.examplestatuscode >>> -> Bitmap Heap Scan on example j (cost=1547.81..79251.08 >>> rows=40538 width=4) (actual time=18.424..69.658 rows=62851 loops=1) >>> Recheck Cond: ((((countrycode)::text = 'AD'::text) AND >>> ((facilitycode)::text = 'ABCD'::text) AND ((internalexamplecode)::text = >>> ANY ('{005,006,007,005}'::text[])) AND (examplestartdatetime >= '2020-05-18 >>> 00:00:00'::timestamp without time zone) AND (examplestartdatetime <= >>> '2020-08-19 00:00:00'::timestamp without time zone)) OR >>> (examplestartdatetime IS NULL)) >>> Filter: (((countrycode)::text = 'AD'::text) AND >>> ((facilitycode)::text = 'ABCD'::text) AND ((internalexamplecode)::text = >>> ANY ('{005,006,007,005}'::text[]))) >>> Rows Removed by Filter: 3 >>> Heap Blocks: exact=18307 >>> -> BitmapOr (cost=1547.81..1547.81 rows=40538 >>> width=0) (actual time=15.707..15.707 rows=0 loops=1) >>> -> Bitmap Index Scan on example_list9_idx >>> (cost=0.00..1523.10 rows=40538 width=0) (actual time=15.702..15.702 >>> rows=62851 loops=1) >>> Index Cond: (((countrycode)::text = >>> 'AD'::text) AND ((facilitycode)::text = 'ABCD'::text) AND >>> ((internalexamplecode)::text = ANY ('{005,006,007,005}'::text[])) AND >>> (examplestartdatetime >= '2020-05-18 00:00:00'::timestamp without time >>> zone) AND (examplestartdatetime <= '2020-08-19 00:00:00'::timestamp without >>> time zone)) >>> -> Bitmap Index Scan on example_list10_idx >>> (cost=0.00..4.44 rows=1 width=0) (actual time=0.004..0.004 rows=3 loops=1) >>> Index Cond: (examplestartdatetime IS NULL) >>> -> Hash Left Join (cost=0.13..1.29 rows=9 width=4) (actual >>> time=88.831..88.840 rows=9 loops=1) >>> Hash Cond: ((js.examplestatuscode)::text = >>> (jc.examplestatuscode)::text) >>> -> Seq Scan on examplestatus js (cost=0.00..1.09 rows=9 >>> width=4) (actual time=0.004..0.007 rows=9 loops=1) >>> -> Hash (cost=0.08..0.08 rows=4 width=16) (actual >>> time=88.817..88.817 rows=5 loops=1) >>> Buckets: 1024 Batches: 1 Memory Usage: 9kB >>> -> CTE Scan on examplecount jc (cost=0.00..0.08 rows=4 >>> width=16) (actual time=88.807..88.812 rows=5 loops=1) >>> Planning Time: 0.979 ms >>> Execution Time: 89.036 ms >>> (23 rows) >>> >>> >>> ----------------exampleCount 2. With >>> internalexamplecode--------------------------------- >>> >>> >>> lmp_examples=> explain analyze with exampleCount as ( select >>> examplestatuscode,count(1) stat_count from example j where 1=1 and >>> j.countrycode = 'AD' and j.facilitycode in ('ABCD') and >>> j.internalexamplecode in ('005','006','007','005') and >>> ((j.examplestartdatetime between '2020-05-18 00:00:00' and '2020-08-19 >>> 00:00:00' ) or j.examplestartdatetime IS NULL ) group by >>> j.examplestatuscode) >>> lmp_examples-> select js.examplestatuscode,COALESCE(stat_count,0) >>> stat_count from exampleCount jc right outer join examplestatus js on >>> jc.examplestatuscode=js.examplestatuscode; >>> >>> >>> QUERY PLAN >>> >>> >>> >>> ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- >>> Hash Left Join (cost=79453.94..79455.10 rows=9 width=12) (actual >>> time=89.660..89.669 rows=9 loops=1) >>> Hash Cond: ((js.examplestatuscode)::text = >>> (jc.examplestatuscode)::text) >>> CTE examplecount >>> -> HashAggregate (cost=79453.77..79453.81 rows=4 width=12) >>> (actual time=89.638..89.640 rows=5 loops=1) >>> Group Key: j.examplestatuscode >>> -> Bitmap Heap Scan on example j (cost=1547.81..79251.08 >>> rows=40538 width=4) (actual time=18.193..69.710 rows=62851 loops=1) >>> Recheck Cond: ((((countrycode)::text = 'AD'::text) AND >>> ((facilitycode)::text = 'ABCD'::text) AND ((internalexamplecode)::text = >>> ANY ('{005,006,007,005}'::text[])) AND (examplestartdatetime >= '2020-05-18 >>> 00:00:00'::timestamp without time zone) AND (examplestartdatetime <= >>> '2020-08-19 00:00:00'::timestamp without time zone)) OR >>> (examplestartdatetime IS NULL)) >>> Filter: (((countrycode)::text = 'AD'::text) AND >>> ((facilitycode)::text = 'ABCD'::text) AND ((internalexamplecode)::text = >>> ANY ('{005,006,007,005}'::text[]))) >>> Rows Removed by Filter: 3 >>> Heap Blocks: exact=18307 >>> -> BitmapOr (cost=1547.81..1547.81 rows=40538 >>> width=0) (actual time=15.483..15.483 rows=0 loops=1) >>> -> Bitmap Index Scan on example_list9_idx >>> (cost=0.00..1523.10 rows=40538 width=0) (actual time=15.477..15.478 >>> rows=62851 loops=1) >>> Index Cond: (((countrycode)::text = >>> 'AD'::text) AND ((facilitycode)::text = 'ABCD'::text) AND >>> ((internalexamplecode)::text = ANY ('{005,006,007,005}'::text[])) AND >>> (examplestartdatetime >= '2020-05-18 00:00:00'::timestamp without time >>> zone) AND (examplestartdatetime <= '2020-08-19 00:00:00'::timestamp without >>> time zone)) >>> -> Bitmap Index Scan on example_list10_idx >>> (cost=0.00..4.44 rows=1 width=0) (actual time=0.004..0.004 rows=3 loops=1) >>> Index Cond: (examplestartdatetime IS NULL) >>> -> Seq Scan on examplestatus js (cost=0.00..1.09 rows=9 width=4) >>> (actual time=0.003..0.005 rows=9 loops=1) >>> -> Hash (cost=0.08..0.08 rows=4 width=24) (actual >>> time=89.650..89.651 rows=5 loops=1) >>> Buckets: 1024 Batches: 1 Memory Usage: 9kB >>> -> CTE Scan on examplecount jc (cost=0.00..0.08 rows=4 >>> width=24) (actual time=89.641..89.647 rows=5 loops=1) >>> Planning Time: 0.470 ms >>> Execution Time: 89.737 ms >>> >>> ------------------------exampleSelect----------------------------------- >>> >>> >>> lmp_examples=> explain analyze select j.id from example j where 1=1 >>> and j.countrycode = 'AD' and j.facilitycode in ('ABCD') and >>> j.examplestatuscode in ('101') and j.internalexamplecode in >>> ('005','006','007','005') and ((j.examplestartdatetime between '2020-05-18 >>> 00:00:00' and '2020-08-19 00:00:00') or j.examplestartdatetime IS NULL) >>> ORDER BY createddate DESC limit 10; >>> >>> QUERY PLAN >>> >>> >>> ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ >>> Limit (cost=71286.65..71286.68 rows=10 width=12) (actual >>> time=47.351..47.359 rows=10 loops=1) >>> -> Sort (cost=71286.65..71335.31 rows=19462 width=12) (actual >>> time=47.349..47.352 rows=10 loops=1) >>> Sort Key: createddate DESC >>> Sort Method: top-N heapsort Memory: 25kB >>> -> Bitmap Heap Scan on example j (cost=1176.77..70866.09 >>> rows=19462 width=12) (actual time=15.133..46.555 rows=2530 loops=1) >>> Recheck Cond: (((countrycode)::text = 'AD'::text) AND >>> ((facilitycode)::text = 'ABCD'::text) AND ((examplestatuscode)::text = >>> '101'::text) AND ((internalexamplecode)::text = ANY >>> ('{005,006,007,005}'::text[]))) >>> Filter: (((examplestartdatetime >= '2020-05-18 >>> 00:00:00'::timestamp without time zone) AND (examplestartdatetime <= >>> '2020-08-19 00:00:00'::timestamp without time zone)) OR >>> (examplestartdatetime IS NULL)) >>> Rows Removed by Filter: 38724 >>> Heap Blocks: exact=20923 >>> -> Bitmap Index Scan on example_list1_idx >>> (cost=0.00..1171.90 rows=33211 width=0) (actual time=9.938..9.939 >>> rows=41254 loops=1) >>> Index Cond: (((countrycode)::text = 'AD'::text) AND >>> ((facilitycode)::text = 'ABCD'::text) AND ((examplestatuscode)::text = >>> '101'::text) AND ((internalexamplecode)::text = ANY >>> ('{005,006,007,005}'::text[]))) >>> Planning Time: 0.398 ms >>> Execution Time: 47.416 ms >>> >>> Regards, >>> Aditya. >>> >>