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