Re: AWS RDS PostgreSQL CPU Spiking to 100%

[aditya desai]

Thanks, I'll check it out.

On Mon, Sep 28, 2020 at 9:40 PM Prince Pathria 
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  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.

SSL connection getting rejected on AWS RDS

[aditya desai]

Hi,
We have AWS RDS and we are trying to connect to DB remotely from EC2
instance.as client connection using psql. We are trying to set up IAM
roles. We did all the necessary settings but got below error. Could you
please advise?

Password for user lmp_cloud_dev:

psql: FATAL:  PAM authentication failed for user "testuser"

FATAL:  pg_hba.conf rejects connection for host "192.168.1.xxx", user
"testuser", database "testdb", SSL off


Regards,

Aditya.

Re: SSL connection getting rejected on AWS RDS

[Hannah Huang]

> On 30 Sep 2020, at 5:19 pm, aditya desai  wrote:
> 
> Hi,
> We have AWS RDS and we are trying to connect to DB remotely from EC2 
> instance.as  client connection using psql. We are trying 
> to set up IAM roles. We did all the necessary settings but got below error. 
> Could you please advise?
> 
> Password for user lmp_cloud_dev:
> psql: FATAL:  PAM authentication failed for user "testuser"
> FATAL:  pg_hba.conf rejects connection for host "192.168.1.xxx", user 
> "testuser", database "testdb", SSL off
> 
> Regards,
> Aditya.
> 

Hi Aditya,

See the below example of me connecting to RDS from an EC2 instance:

You need to change the $RDSHOST value
you need to replace my “app_user” to your “testuser” and database “postgres” to 
your “testdb”

[ec2-user@ip-172-31-13-121 ~]$ export 
RDSHOST="mypg.cfvvs1nh3f7i.ap-southeast-2.rds.amazonaws.com"

[ec2-user@ip-172-31-13-121 ~]$ export PGPASSWORD="$(aws rds 
generate-db-auth-token \
--hostname $RDSHOST \
--port 5432 \
--username app_user)”

[ec2-user@ip-172-31-13-121 ~]$ psql "host=$RDSHOST port=5432 sslmode=require 
dbname=postgres user= app_user"

psql (11.5, server 12.3)
WARNING: psql major version 11, server major version 12.
Some psql features might not work.
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 
256, compression: off)
Type "help" for help.
postgres=>

Thanks,
Hannah

Re: SSL connection getting rejected on AWS RDS

[aditya desai]

Hi Hannah,
Thank you very much!! this is really helpful. Do we need to pass
'sslrootcert" as mentioned in the doc below? I see that you have not used
it in  your command.

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.IAMDBAuth.Connecting.AWSCLI.PostgreSQL.html

Also do we have to grant the role below to the user?

grant rds_iam to app_user;


If you have any document/Steps to set this up from scratch,could you please
forward? That would be really helpful.

Regards,
Aditya.


On Wed, Sep 30, 2020 at 4:47 PM Hannah Huang 
wrote:

>
>
> On 30 Sep 2020, at 5:19 pm, aditya desai  wrote:
>
> Hi,
> We have AWS RDS and we are trying to connect to DB remotely from EC2
> instance.as client connection using psql. We are trying to set up IAM
> roles. We did all the necessary settings but got below error. Could you
> please advise?
>
> Password for user lmp_cloud_dev:
>
> psql: FATAL:  PAM authentication failed for user "testuser"
>
> FATAL:  pg_hba.conf rejects connection for host "192.168.1.xxx", user
> "testuser", database "testdb", SSL off
>
>
> Regards,
>
> Aditya.
>
>
> Hi Aditya,
>
> See the below example of me connecting to RDS from an EC2 instance:
>
> You need to change the $RDSHOST value
> you need to replace my “app_user” to your “testuser” and database
> “postgres” to your “testdb”
>
> [ec2-user@ip-172-31-13-121 ~]$ export RDSHOST="mypg.cfvvs1nh3f7i.ap-
> southeast-2.rds.amazonaws.com"
>
> [ec2-user@ip-172-31-13-121 ~]$ export PGPASSWORD="$(aws rds
> generate-db-auth-token \
> --hostname $RDSHOST \
> --port 5432 \
> --username app_user)”
>
> [ec2-user@ip-172-31-13-121 ~]$ psql "host=$RDSHOST port=5432
> sslmode=require dbname=postgres user= app_user"
>
> psql (11.5, server 12.3)
> WARNING: psql major version 11, server major version 12.
> Some psql features might not work.
> SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384,
> bits: 256, compression: off)
> Type "help" for help.
> postgres=>
>
> Thanks,
> Hannah
>

Re: SSL connection getting rejected on AWS RDS

[Hannah Huang]

Hi Aditya,

Yes, you need to grant the role to the user inside PostgreSQL database.

Please checkout this article: 
https://suyahuang.wordpress.com/2020/10/01/hands-on-lab-access-rds-postgresql-from-ec2-instance-without-password-how-to-configure-iam-db-authentication/

Let me know if you have any problem following through.

Thanks,
Hannah

> On 1 Oct 2020, at 1:50 am, aditya desai  wrote:
> 
> Hi Hannah,
> Thank you very much!! this is really helpful. Do we need to pass 
> 'sslrootcert" as mentioned in the doc below? I see that you have not used it 
> in  your command. 
> 
> https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.IAMDBAuth.Connecting.AWSCLI.PostgreSQL.html
>  
> 
> 
> Also do we have to grant the role below to the user?
> 
> grant rds_iam to app_user;
> 
> 
> If you have any document/Steps to set this up from scratch,could you please 
> forward? That would be really helpful.
> 
> Regards,
> Aditya.
> 
> 
> On Wed, Sep 30, 2020 at 4:47 PM Hannah Huang  > wrote:
> 
> 
>> On 30 Sep 2020, at 5:19 pm, aditya desai > > wrote:
>> 
>> Hi,
>> We have AWS RDS and we are trying to connect to DB remotely from EC2 
>> instance.as  client connection using psql. We are 
>> trying to set up IAM roles. We did all the necessary settings but got below 
>> error. Could you please advise?
>> 
>> Password for user lmp_cloud_dev:
>> psql: FATAL:  PAM authentication failed for user "testuser"
>> FATAL:  pg_hba.conf rejects connection for host "192.168.1.xxx", user 
>> "testuser", database "testdb", SSL off
>> 
>> Regards,
>> Aditya.
>> 
> 
> Hi Aditya,
> 
> See the below example of me connecting to RDS from an EC2 instance:
> 
> You need to change the $RDSHOST value
> you need to replace my “app_user” to your “testuser” and database “postgres” 
> to your “testdb”
> 
> [ec2-user@ip-172-31-13-121 ~]$ export 
> RDSHOST="mypg.cfvvs1nh3f7i.ap-southeast-2.rds.amazonaws.com 
> "
> 
> [ec2-user@ip-172-31-13-121 ~]$ export PGPASSWORD="$(aws rds 
> generate-db-auth-token \
> --hostname $RDSHOST \
> --port 5432 \
> --username app_user)”
> 
> [ec2-user@ip-172-31-13-121 ~]$ psql "host=$RDSHOST port=5432 sslmode=require 
> dbname=postgres user= app_user"
> 
> psql (11.5, server 12.3)
> WARNING: psql major version 11, server major version 12.
> Some psql features might not work.
> SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 
> 256, compression: off)
> Type "help" for help.
> postgres=>
> 
> Thanks,
> Hannah