High CPU Usage on Aurora PostgreSQL Reader Instance After Migration from RDS

Hi,

some possible causes could be:

• Query Routing and Load Distribution

• With RDS, all queries went to a single instance, allowing PostgreSQL to better optimize its query cache and execution plans.
• In Aurora, the read/write split might be sending more queries to the reader than optimal
• The reader instance might be receiving queries that could be more efficiently handled by the writer

• Different Instance Types:

• You moved from m6g (general purpose) to r6g/r6gd (memory optimized)
• While r6g instances have more RAM, they might have slightly different CPU characteristics
• The r6gd reader instance uses local NVMe storage, which could affect I/O patterns
• Version Difference

The upgrade from PostgreSQL 14 to 16 might have introduced different query planning strategies

Some queries might be using different execution plans in v16

Aurora Architecture

• Aurora's storage architecture differs from RDS
• Reader instances maintain their own buffer cache
• More CPU might be used for maintaining consistency between writer and reader

I would recommend you to optimize query distribution

-- Check query patterns on reader SELECT query, calls, total_time, mean_time FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10;

-- Identify heavy queries that might be better on writer SELECT * FROM pg_stat_activity WHERE state = 'active' AND wait_event_type IS NOT NULL;

Also review:

a. Vertical Scaling:

• Consider upgrading to r6g.4xlarge for the reader
• Or switch back to m6g series if memory optimization isn't crucial

b. Horizontal Scaling:

• Add additional reader instances

• Distribute read traffic across multiple readers

Query Optimization

-- Add relevant indexes CREATE INDEX idx_name ON table_name(column_name);

-- Update table statistics ANALYZE table_name;

-- Review and update materialized views if used REFRESH MATERIALIZED VIEW mv_name;

Aurora Configuration Tuning

Parameters to consider adjusting:

• shared_buffers
• effective_cache_size
• work_mem
• maintenance_work_mem

Immediate Steps to Take:

Analyze Query Patterns

SELECT userid, dbid, query, calls, total_time, rows, shared_blks_hit, shared_blks_read FROM pg_stat_statements ORDER BY total_time DESC;

Monitor Resource Usage

SELECT state, wait_event_type, wait_event, count() FROM pg_stat_activity GROUP BY 1, 2, 3 ORDER BY count() DESC;

Check Buffer Cache Efficiency

SELECT sum(heap_blks_read) as heap_read, sum(heap_blks_hit) as heap_hit, sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read))::float as ratio FROM pg_statio_user_tables;

Some long-term recommendations:

• Implement query caching at the application level for frequently accessed read-only data
• Consider using Aurora Global Database if your reads are geographically distributed
• Implement proper query timeouts and retry mechanisms in your application
• Set up proper monitoring and alerting for CPU usage patterns
• Regular review and optimization of frequently executed queries
• Implement more reader instances

Monitor these changes closely after implementation and adjust based on your specific workload patterns. You might need to try various combinations of these solutions to find the optimal configuration for your use case.

It is also always recommended to first test your production workload in lower environment with the desired version to upgrade if you are going to upgrade postgresql version or even when changing your workload to another engine type.