PostgreSQL Aurora只读节点仅出现慢查询问题排查求助

问题描述 #

使用顶配规格的PostgreSQL Aurora集群（引擎版本13.3），主库（Writer Cluster Endpoint）执行所有查询均无性能问题，但将读请求切换至只读节点后，部分查询出现严重延迟：首次查询耗时超10秒，主库仅需毫秒级；第二次执行相同查询时，只读节点性能恢复至主库水平。

• 主库与只读节点执行完全相同的查询计划
• AWS监控显示只读节点缓存命中率未下降

执行计划详情 #

主库首次查询 #

Node Type   Index Scan
Parent Relationship Outer
Parallel Aware  true
Scan Direction  Forward
Index Name  trace_idx09
Schema  public
Alias   trace_
Startup Cost    0.69
Total Cost  15609.54
Plan Rows   1610
Plan Width  24
Actual Startup Time 5.936
Actual Total Time   50.491
Actual Rows 2902
Actual Loops    3
Rows Removed by Index Recheck   0
Shared Hit Blocks   3917
Shared Read Blocks  396
Shared Dirtied Blocks   0
Shared Written Blocks   0
Local Hit Blocks    0
Local Read Blocks   0
Local Dirtied Blocks    0
Local Written Blocks    0
Temp Read Blocks    0
Temp Written Blocks 0
I/O Read Time   870.529
I/O Write Time  0
Workers [object Object],[object Object]
*Planner Row Estimate Factor    1.8024844720496895
*Planner Row Estimate Direction 1
*Actual Duration    151.473
*Actual Cost    15609.54
*Slowest Node (by duration) false
*Largest Node (by rows) true
*Costiest Node (by cost)    true

只读节点首次查询 #

Node Type   Index Scan
Parent Relationship Outer
Parallel Aware  true
Scan Direction  Forward
Index Name  trace_idx09
Schema  public
Alias   trace_
Startup Cost    0.69
Total Cost  15609.54
Plan Rows   1610
Plan Width  24
Actual Startup Time 3046.454
Actual Total Time   3387.273
Actual Rows 2902
Actual Loops    3
Rows Removed by Index Recheck   0
Shared Hit Blocks   1096
Shared Read Blocks  4191
Shared Dirtied Blocks   0
Shared Written Blocks   0
Local Hit Blocks    0
Local Read Blocks   0
Local Dirtied Blocks    0
Local Written Blocks    0
Temp Read Blocks    0
Temp Written Blocks 0
I/O Read Time   8763.617
I/O Write Time  0
Workers [object Object],[object Object]
*Planner Row Estimate Factor    1.8024844720496895
*Planner Row Estimate Direction 1
*Actual Duration    10161.819
*Actual Cost    15609.54
*Slowest Node (by duration) true
*Largest Node (by rows) true
*Costiest Node (by cost)    true

只读节点第二次查询 #

Node Type   Index Scan
Parent Relationship Outer
Parallel Aware  true
Scan Direction  Forward
Index Name  trace_idx09
Schema  public
Alias   trace_
Startup Cost    0.69
Total Cost  15609.54
Plan Rows   1610
Plan Width  24
Actual Startup Time 3.734
Actual Total Time   8.375
Actual Rows 2902
Actual Loops    3
Rows Removed by Index Recheck   0
Shared Hit Blocks   4039
Shared Read Blocks  2
Shared Dirtied Blocks   0
Shared Written Blocks   0
Local Hit Blocks    0
Local Read Blocks   0
Local Dirtied Blocks    0
Local Written Blocks    0
Temp Read Blocks    0
Temp Written Blocks 0
I/O Read Time   3.538
I/O Write Time  0
Workers [object Object],[object Object]
*Planner Row Estimate Factor    1.8024844720496895
*Planner Row Estimate Direction 1
*Actual Duration    25.125
*Actual Cost    15609.54
*Slowest Node (by duration) false
*Largest Node (by rows) true
*Costiest Node (by cost)    true

核心差异分析 #

从执行计划可明确：

• 只读节点首次查询时，缓存命中块数仅为主库的28%，磁盘读取块数是主库的10倍多，I/O读取时间是主库的10倍
• 只读节点二次查询时，缓存命中块数接近主库水平，磁盘读取几乎可以忽略，性能恢复正常

排查与修复要点 #

1. 只读节点缓存预热 #

Aurora只读节点的shared_buffers不会自动从主库同步缓存数据，首次访问热点数据时需从存储层加载：

• 执行SELECT * FROM pg_stat_bgwriter;查看缓存加载统计，确认buffers_alloc（从磁盘分配的缓冲区）是否过高
• 针对核心查询/索引，编写预热脚本（例如SELECT * FROM trace_ WHERE ... LIMIT 0;或SELECT * FROM pg_prewarm('trace_idx09');），在只读节点启动后执行，提前将热点数据加载到缓存

2. 存储层数据本地化 #

Aurora采用分布式存储，只读节点可能未在本地存储节点缓存所需数据块，导致远程读取延迟：

• 查看CloudWatch指标AuroraVolumeReadLatency，对比主库与只读节点的平均读取延迟
• 尝试重启只读节点，触发存储层重新调度数据块到节点本地存储（部分场景下可缓解本地化不足问题）

3. 并行查询资源配置 #

查询启用了并行执行，需确认只读节点与主库的并行参数配置一致：

• 检查max_parallel_workers_per_gather、max_worker_processes、parallel_setup_cost等参数，确保只读节点与主库配置相同
• 监控只读节点CPUUtilization指标，确认并行执行时是否存在CPU资源瓶颈

4. 复制延迟与数据同步 #

虽然二次查询正常，但仍需排除复制延迟导致的临时阻塞：

• 查看CloudWatch指标ReplicaLag，确认只读节点与主库的同步延迟
• 执行SELECT * FROM pg_stat_replication;检查复制进程状态，确认replay_lag是否正常

5. 索引与表的维护状态 #

主库的索引/表可能经过VACUUM、ANALYZE优化，只读节点虽同步数据，但部分优化状态可能未实时同步：

• 在主库执行VACUUM ANALYZE trace_;后，重新测试只读节点查询性能
• 对比主库与只读节点的pg_stat_user_indexes视图，查看idx_scan、idx_tup_read等统计是否一致

6. 实例资源可用性 #

即使是顶配实例，也可能存在内存、CPU资源被其他进程占用的情况：

• 查看CloudWatch指标FreeableMemory、SwapUsage，确认内存是否充足
• 执行SELECT * FROM pg_stat_activity WHERE state = 'active';，排查只读节点是否有其他长时间运行的查询占用资源

内容的提问来源于stack exchange，提问作者prospective developer