跳到主要内容

日志与监控

日志与监控用于定位连接失败、慢查询、锁等待、权限错误和资源瓶颈。CloudBase PostgreSQL 的具体监控入口以控制台实际展示为准。

需要关注的指标​

指标说明
CPU / 内存判断是否存在计算或内存压力
连接数判断连接池配置是否合理
QPS / TPS判断流量波动和写入压力
慢查询定位需要优化的 SQL
锁等待判断事务是否阻塞其他请求
存储空间判断数据和索引增长趋势

查看当前连接​

select state, count(*)
from pg_stat_activity
group by state
order by count(*) desc;

查看正在执行的 SQL：

select pid, usename, state, now() - query_start as duration, query
from pg_stat_activity
where state <> 'idle'
order by duration desc
limit 20;

查看锁等待​

select
  blocked.pid as blocked_pid,
  blocking.pid as blocking_pid,
  blocked.query as blocked_query,
  blocking.query as blocking_query
from pg_stat_activity blocked
join pg_locks blocked_locks on blocked_locks.pid = blocked.pid
join pg_locks blocking_locks
  on blocking_locks.locktype = blocked_locks.locktype
 and blocking_locks.database is not distinct from blocked_locks.database
 and blocking_locks.relation is not distinct from blocked_locks.relation
 and blocking_locks.transactionid is not distinct from blocked_locks.transactionid
 and blocking_locks.pid <> blocked_locks.pid
join pg_stat_activity blocking on blocking.pid = blocking_locks.pid
where not blocked_locks.granted;

如果频繁出现锁等待，应检查长事务、批量更新和 DDL 操作。

慢查询排查​

慢查询通常来自缺少索引、返回数据量过大、排序成本高、RLS 策略复杂或锁等待。

处理流程：

  1. 记录慢 SQL 和参数。
  2. 使用 EXPLAIN 分析执行计划。
  3. 检查索引、统计信息和查询条件。
  4. 优化 SQL 后在测试环境验证。
  5. 观察上线后的耗时和资源变化。

查询优化方法请参考 查询优化。

业务日志​

数据库日志只能说明 SQL 层面的状态。业务系统还应记录请求 ID、用户 ID、关键参数、数据库耗时和错误码，便于把前端报错、服务端日志和数据库行为串联起来。

不要在日志中记录密码、完整 Token、身份证号等敏感信息。