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_r0
gem install pghero_logs
It 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_logs
First, 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 download
Once logs are downloaded, run
cat logs/postgresql.log.* | pghero_logs
To analyze with PgBadger, install
brew install pgbadger
And run
pgbadger --prefix "%t:%r:%u@%d:[%p]:" --outfile pgbadger.html logs/* && open pgbadger.html
Thanks to RDS PgBadger for the prefix.