pg_stat_kcache

最新推荐文章于 2025-07-31 14:04:12 发布

喝醉酒的小白

最新推荐文章于 2025-07-31 14:04:12 发布

阅读量670

点赞数 10

CC 4.0 BY-SA版权

分类专栏： PG 文章标签：运维

本文链接：https://blog.youkuaiyun.com/hezuijiudexiaobai/article/details/148426303

PG 专栏收录该内容

305 篇文章

订阅专栏

1. 核心组件功能

插件	监控维度	关键指标
`pg_stat_statements`	SQL执行统计	`总耗时`、`执行次数`、`内存使用`、`临时文件`
`pg_stat_kcache`	系统资源消耗	`CPU周期`、`内存页读写次数`、`文件系统I/O次数`

二者结合可定位查询的物理资源消耗瓶颈，突破单一SQL层级的分析局限。

2. 配置与部署

(1) 启用插件

-- 修改postgresql.conf
shared_preload_libraries = 'pg_stat_statements, pg_stat_kcache'

-- 必要参数扩展
pg_stat_statements.track = all       -- 跟踪所有SQL
pg_stat_kcache.track = top           -- 跟踪top级查询（含函数调用）
pg_stat_kcache.track_planning = on   -- 监控执行计划阶段消耗（v13+）

(2) 初始化扩展

CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION pg_stat_kcache;

3. 高级分析方法

(1) 关联分析视图

SELECT 
  s.queryid,
  s.query,
  s.total_exec_time,
  k.reads AS fs_reads,    -- 文件系统读取次数
  k.writes AS fs_writes,  -- 文件系统写入次数
  k.cpu_sys,              -- 内核态CPU周期
  k.cpu_user              -- 用户态CPU周期
FROM pg_stat_statements s
JOIN pg_stat_kcache() k ON s.queryid = k.queryid
WHERE s.calls > 1000      -- 高频查询
ORDER BY k.reads + k.writes DESC;  -- 按I/O负载排序

(2) 关键性能公式

I/O瓶颈识别
$\text{I/O负载比} = \frac{\text{文件系统读写次数}}{\text{执行次数}}$
若比值持续偏高，需优化索引或调整缓存策略。
CPU/IO消耗关联
$\frac{\text{CPU周期}}{\text{I/O次数}}$
当 $R$ 值极低时，表明查询受限于I/O而非CPU。

4. 查询缓存优化策略

(1) 共享缓冲区调整

-- 查看缓存命中率
SELECT 
  sum(heap_blks_read) AS disk_reads,
  sum(heap_blks_hit) AS cache_hits,
  CASE WHEN sum(heap_blks_read) > 0
    THEN round(sum(heap_blks_hit) / (sum(heap_blks_hit)+sum(heap_blks_read)), 2) 
    ELSE 1 END AS hit_ratio
FROM pg_statio_user_tables;

-- 若hit_ratio < 0.99，增加shared_buffers（建议设为内存的25%-40%）

(2) 操作系统层缓存优化

Linux页面缓存策略
通过pg_stat_kcache监控fs_reads：若该值显著高于shared_buffer reads，表明依赖OS缓存：
```
# 调整Linux脏页回写策略
vm.dirty_background_ratio = 5
vm.dirty_ratio = 10
```

(3) 热点数据预加载

-- 将高频查询表预载入缓存
CREATE EXTENSION pg_prewarm;
SELECT pg_prewarm('important_table'::regclass);

(4) 内存分配策略

Work Mem调优
针对排序/哈希操作：

-- 根据kcache.cpu_user值调整
SET work_mem = '64MB';  -- 避免临时文件写入（kcache.writes骤降）

5. 实战优化流程

定位高I/O查询
通过第3节视图筛选fs_reads/fs_writesTOP10语句。
分析资源配比
计算 $R$ 值，确认瓶颈属于CPU或I/O。
针对性优化
- 低 $R$ 值 → 扩大shared_buffers或增加索引
- 高work_mem需求 → 提升会话级内存配额
- 高频小查询 → 启用连接池+语句缓存（如PgBouncer）
验证效果
对比优化前后的kcache指标，重点观察fs_reads和cpu_user下降比例。