How do you monitor slow query in AWS RDS Postgres

Question

My RDS Postgres has 5 read replica and it's the maximum number of read replica and cannot scale out by increasing read replica. In this situation, some of the read replicas occur too much high CPU utilization.

I think one of the solution against it is query tunining. But we cannot login RDS postgres with ssh connect, so to read Postgres Error log we need to download and analyze it. This way is too inefficient and very depend on who do this analysis work.

So I want to build up the system of slow query analysis in RDS PostgreSQL environment. And I want to hear what sort of approach or what middleware is recommend.

My plan is - Using RDS REST API, download db log file every hour to local storage. ( https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/RESTReference.html ) - Using fluend, build up log analysis system

Have you tried pg_stat_statements? if not postgresql.org/docs/10/static/pgstatstatements.html and maybe The Statistics Collector postgresql.org/docs/10/static/monitoring-stats.html Also a guide for enabling this in RDS -> pganalyze.com/docs/install/amazon_rds/01_configure_rds_instance — Imanol Y., Mar 15, 2018 at 16:03

Jakub Jindra · Accepted Answer · 2019-02-11 21:13:26Z

1

I would reconfigure parameter group settings to create only one logfile per day which is much easier to download and analyze than many smaller files. And configure slow query logging based on your requirements.

Since You're AWS RDS user you can enable performance insights which might be helpful in Your case.

answered Feb 11, 2019 at 21:13

Jakub Jindra

1115 bronze badges

Add a comment |

Stack Exchange Network

How do you monitor slow query in AWS RDS Postgres

1 Answer 1

Your Answer

Hot Network Questions

How do you monitor slow query in AWS RDS Postgres

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions