RDS Database with 70GB of data takes 3-4 hours to stop...

Question

Hi,

We've got a RDS database that is taking ridiculously long to stop, it starts back up within 4-5 mins~

* Instance Class: db.t4g.large
* Engine: PostgreSQL
* Engine Version: 12.11
* Multi-AZ: No
* Storage Type: SSD (gp2)
* Storage: 150GB (70GB used)
* Performance Insights: Disabled
* Cloudwatch: Enabled

A backup of the instance takes 45 mins to complete, so even with the "hidden snapshot" it should only take 50~ mins max... why is it taking so long to stop an RDS instance?

I'd greatly appreciate suggestions and advice.

Thanks

Answer

1) Analyze [pg_stat_activity](https://aws.amazon.com/premiumsupport/knowledge-center/rds-postgresql-running-queries/), [pg_stat_statements](https://www.postgresql.org/docs/12/pgstatstatements.html) for the DB activity and long running queries during that time
2) Take [pg_badger](https://aws.amazon.com/blogs/database/optimizing-and-tuning-queries-in-amazon-rds-postgresql-based-on-native-and-external-tools/) report during the window and analyze the session/queries 
3) If possible, [enable Performance insights ](https://aws.amazon.com/about-aws/whats-new/2018/04/rds-performance-insights-on-rds-for-postgresql/)and check what is top events and top SQLs during the window

RDS Database with 70GB of data takes 3-4 hours to stop...

Relevanter Inhalt