How do I use CloudWatch Logs to analyze log data for my Amazon RDS for PostgreSQL database?

Short description

To analyze your database logs, you must first configure RDS for PostgreSQL logging parameters and export the logs to Amazon CloudWatch. Then, use CloudWatch Logs Insights to query and analyze your log data.

Resolution

Configure RDS for PostgreSQL log parameters

Create a custom parameter group with the following logging parameters:

• log_statement: Set to all, mod, or ddl to control statement logging.
• log_min_duration_statement: Set the millisecond threshold to log long-running queries.
• log_autovacuum_min_duration: Set the millisecond threshold to log autovacuum actions.
• log_lock_waits: Set to 1 to log sessions in locked states.
• log_connections and log_disconnections: Set both to 1 to track session activity.
• log_temp_files: Set the minimum size of temporary files that you want to log.

To modify an existing parameter group, see Modifying parameters in a DB parameter group in Amazon RDS.

Export logs to CloudWatch

Configure your RDS for PostgreSQL database (DB) instance to export logs to CloudWatch.

Analyze logs with CloudWatch Logs Insights

To analyze specific database activities, use CloudWatch Logs Insights.

Track user disconnections

To show the session duration for each user connection and IP address of the connecting host, database name, and connection port, run the following query:

fields @timestamp, @message
parse @message /session time: (?<sessionTime>.*?) user=(?<user>.*?) database=(?<database>.*?) host=(?<host>.*?) port=(?<port>.*?)$/ 
| filter user like "postgres"

Note: Replace postgres with the username that you want to analyze.

Use the output to identify short sessions that might show connection issues and unexpected connection patterns from unknown IP addresses. You can also track user disconnections to monitor database access patterns across different times.

Track user connections

To monitor the applications that connect to your database and the connection patterns at specific times, run the following query:

fields @timestamp, @message
| filter @message like /connection authorized: user=postgres/
| parse @message "*:*(*):*@*:[*]:LOG:  connection authorized: user=postgres database=* application_name=* SSL" as date_time, ip_address, port, user, db, pid, database, app_name
| display @timestamp, ip_address, database, app_name

Note: Replace postgres with the username that you want to analyze.

You can also use the output to identify unexpected application connections and check SSL usage for connections.

Monitor temporary file usage

To identify queries that exceed work_mem and create temporary files, run the following query:

fields @timestamp, @message
| filter @message like /temporary file:/
| parse @message /temporary file: path "(?<filepath>.*?)", size (?<filesize>\d+)/ 
| display @timestamp, filepath, filesize

Note: You can also use the preceding query to view the disk space usage from temporary operations or find queries that require optimization.

If the output shows frequent large temporary files, then take one or more of the following actions:

• If your DB instance has available memory, then increase work_mem.
• Review and optimize queries that create large temporary files.
• Check whether table partitions can reduce temporary file usage.

Identify deadlocks

To determine the process IDs of deadlocked sessions, run the following query:

fields @timestamp, @message
| filter @message like /ShareLock|while|process|lock/
| parse @message /Process (?<blockingProcess>\d+) waits for ShareLock on transaction (?<transactionId>\d+); blocked by process (?<blockedByProcess>\d+)/
| filter  blockedByProcess > 0
| display @timestamp, blockingProcess, transactionId, blockedByProcess

Note: The output also shows the transaction IDs that cause the locks and the timestamp of when a deadlock occurred.

If you identify frequent deadlocks, then take one or more of the following actions:

• Review application transaction patterns.
• Check for long-running transactions that hold locks.
• Add row-level locks where appropriate.
• Analyze transaction isolation levels.

Identify long-running queries

To view SQL statements that exceed your specified duration and the execution time for each statement, run the following query:

fields @timestamp, @message
| parse @message /duration: (?<duration>.*?) ms\s+statement: (?<statement>.*?)$/ 
| filter duration > 1000
| sort duration desc

Note: Replace 1000 with your preferred duration threshold in milliseconds.

If you find slow queries, then take one or more of the following actions:

• Run EXPLAIN ANALYZE on the statements to view their execution plans.
• Check whether the table statistics are up to date.
• Verify index usage.
• Optimize your queries or use table partitions.
• Review resource allocation for your DB instance.

Analyze autovacuum statistics

To track autovacuum operations and monitor the rate of tuple removal, run the following query:

fields @timestamp, @message
| parse @message "tuples: " as @tuples_temp
| parse @tuples_temp "* removed," as @tuples_removed
| parse @tuples_temp "remain, * are dead but not yet removable, " as @tuples_not_removable
| filter @message like / automatic vacuum /
| sort @timestamp
| stats avg(@tuples_removed) as avg_tuples_removed, 
        avg(@tuples_not_removable) as avg_tuples_not_removable 
  by bin(5m)

Note: The output can also help you identify tables with high numbers of dead tuples.

If you can't use autovacuum, then take one or more of the following actions:

• Adjust the autovacuum_vacuum_scale_factor.
• Review the autovacuum_vacuum_threshold settings.
• Check for long-running transactions that block vacuum operations.
• Use manual VACUUM operations during low-traffic periods.

Set up monitoring dashboards

Create CloudWatch dashboards to visualize your log analysis.

Add widgets for the following values:

• Execution times for queries over time
• Deadlock frequency
• Temporary file usage patterns
• Autovacuum effectiveness

You can also set up notifications for database log errors.

Related information

Best practices for Amazon RDS

Using Amazon CloudWatch alarms

Working with RDS and Amazon Aurora PostgreSQL-Compatible Edition logs: Part 1