Slow query log parser for Postgres
Total Avg Count Query
(min) (ms)
20 4381 283 SELECT DISTINCT "orders"."id" AS t0_r0, "orders"
7 3574 120 SELECT "visits".* FROM "visits" WHERE ("visits".
4 12621 20 SELECT DISTINCT "order_deliveries"."id" AS t0_r0gem install pghero_logsIt can take 10 minutes or more to compile the query parser 🕑
Tell Postgres to log slow queries in postgresql.conf
log_min_duration_statement = 20 # ms
Analyze the logs
cat /usr/local/var/postgres/server.log | pghero_logsFirst, download the logs. Create an IAM user with the policy below
{
"Statement": [
{
"Sid": "Stmt1410669817271",
"Action": [
"rds:DescribeDBLogFiles",
"rds:DownloadDBLogFilePortion"
],
"Effect": "Allow",
"Resource": "*"
}
]
}And run
export AWS_ACCESS_KEY_ID=test123
export AWS_SECRET_ACCESS_KEY=secret123
export AWS_DB_INSTANCE_IDENTIFIER=production
pghero_logs downloadOnce logs are downloaded, run
cat logs/postgresql.log.* | pghero_logsTo analyze with PgBadger, install
brew install pgbadgerAnd run
pgbadger --prefix "%t:%r:%u@%d:[%p]:" --outfile pgbadger.html logs/* && open pgbadger.htmlThanks to RDS PgBadger for the prefix.