印刻数据库

外观

PostgreSQL 高CPU内存使用处理 ​

CPU 使用率高问题诊断 ​

1. 实时监控 CPU 使用情况 ​

sql
-- 查看当前 PostgreSQL 进程的 CPU 使用率
SELECT 
    pid, 
    usename, 
    datname, 
    application_name, 
    client_addr, 
    backend_start, 
    state, 
    query_start, 
    now() - query_start AS query_duration,
    substr(query, 1, 200) AS query_sample
FROM 
    pg_stat_activity
WHERE 
    state != 'idle'
ORDER BY 
    query_duration DESC;

-- 查看系统级 CPU 使用情况（需要安装 pg_stat_statements 扩展）
SELECT 
    userid::regrole, 
    dbid, 
    queryid, 
    substr(query, 1, 200) AS query, 
    calls, 
    total_time, 
    mean_time, 
    stddev_time,
    rows
FROM 
    pg_stat_statements
ORDER BY 
    total_time DESC
LIMIT 10;

2. 识别高 CPU 消耗的查询 ​

sql
-- 查看当前正在执行的高 CPU 查询
SELECT 
    p.pid, 
    p.usename, 
    p.datname, 
    p.application_name, 
    p.state, 
    p.query, 
    p.query_start, 
    now() - p.query_start AS duration,
    EXTRACT(EPOCH FROM (now() - p.query_start)) AS duration_seconds,
    pg_blocking_pids(p.pid) AS blocking_pids,
    psa.wait_event_type, 
    psa.wait_event
FROM 
    pg_stat_activity p
JOIN 
    pg_stat_activity psa ON p.pid = psa.pid
WHERE 
    p.state = 'active'
ORDER BY 
    duration DESC;

-- 查看历史高 CPU 查询（使用 pg_stat_statements）
SELECT 
    queryid, 
    substr(query, 1, 200) AS query, 
    calls, 
    total_time / 1000 AS total_time_seconds,
    mean_time / 1000 AS mean_time_seconds,
    rows,
    100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM 
    pg_stat_statements
ORDER BY 
    total_time DESC
LIMIT 20;

内存使用率高问题诊断 ​

1. 查看内存使用情况 ​

sql
-- 查看 PostgreSQL 内存配置参数
SELECT 
    name, 
    setting, 
    unit, 
    short_desc
FROM 
    pg_settings
WHERE 
    name IN ('shared_buffers', 'work_mem', 'maintenance_work_mem', 
             'effective_cache_size', 'wal_buffers', 'max_connections');

-- 计算理论最大内存使用量
SELECT 
    current_setting('max_connections')::int AS max_connections,
    current_setting('shared_buffers') AS shared_buffers,
    current_setting('work_mem') AS work_mem,
    current_setting('maintenance_work_mem') AS maintenance_work_mem,
    (current_setting('max_connections')::int * current_setting('work_mem')::int) / 1024 AS total_work_mem_mb,
    current_setting('maintenance_work_mem')::int / 1024 AS maintenance_work_mem_mb
FROM 
    pg_settings;

2. 识别内存泄漏和异常使用 ​

sql
-- 查看每个进程的内存使用情况
SELECT 
    pid, 
    usename, 
    datname, 
    application_name, 
    backend_start, 
    state,
    substr(query, 1, 100) AS query_sample,
    pg_size_pretty(pg_total_relation_size(relid)) AS relation_size
FROM 
    pg_stat_activity
WHERE 
    relid IS NOT NULL
ORDER BY 
    pg_total_relation_size(relid) DESC;

-- 查看临时文件使用情况（可能指示内存不足）
SELECT 
    datname, 
    temp_files, 
    temp_bytes, 
    pg_size_pretty(temp_bytes) AS temp_size
FROM 
    pg_stat_database
ORDER BY 
    temp_bytes DESC;

高 CPU 使用率问题处理 ​

1. 查询优化 ​

sql
-- 分析慢查询执行计划
EXPLAIN ANALYZE 
SELECT * FROM large_table WHERE complex_condition;

-- 为慢查询添加索引
CREATE INDEX idx_large_table_column ON large_table(column);

-- 重新统计表格信息
ANALYZE large_table;

2. 配置优化 ​

sql
-- 调整查询优化器参数
ALTER SYSTEM SET random_page_cost = '1.1'; -- SSD 存储建议值
ALTER SYSTEM SET effective_cache_size = '16GB'; -- 建议设置为系统内存的 50-75%

-- 调整并行查询参数
ALTER SYSTEM SET max_parallel_workers_per_gather = '4';
ALTER SYSTEM SET max_parallel_workers = '8';

-- 重新加载配置
SELECT pg_reload_conf();

3. 终止异常查询 ​

sql
-- 终止长时间运行的查询
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'active'
  AND now() - query_start > INTERVAL '5 minutes';

-- 或使用 pg_cancel_backend 尝试优雅终止
SELECT pg_cancel_backend(pid)
FROM pg_stat_activity
WHERE state = 'active'
  AND now() - query_start > INTERVAL '5 minutes';

高内存使用率问题处理 ​

1. 内存配置优化 ​

sql
-- 调整 shared_buffers（建议为系统内存的 25%）
ALTER SYSTEM SET shared_buffers = '8GB';

-- 调整 work_mem（根据查询复杂度调整，默认 4MB）
ALTER SYSTEM SET work_mem = '16MB';

-- 调整 maintenance_work_mem（建议为系统内存的 5-10%，最大 2GB）
ALTER SYSTEM SET maintenance_work_mem = '1GB';

-- 调整 max_connections（根据实际需要调整，避免过大）
ALTER SYSTEM SET max_connections = '200';

-- 重新加载配置（部分参数需要重启）
SELECT pg_reload_conf();

2. 优化查询内存使用 ​

sql
-- 识别使用大量临时内存的查询
SELECT 
    pid, 
    usename, 
    datname, 
    application_name, 
    state, 
    query,
    temp_blks_read + temp_blks_written AS temp_blocks,
    pg_size_pretty((temp_blks_read + temp_blks_written) * current_setting('block_size')::int) AS temp_size
FROM 
    pg_stat_activity
WHERE 
    temp_blks_read + temp_blks_written > 0
ORDER BY 
    temp_blocks DESC;

-- 优化使用大量内存的查询，如添加索引、重写查询
-- 示例：优化 GROUP BY 查询
CREATE INDEX idx_table_group_column ON large_table(group_column);

3. 处理内存泄漏 ​

sql
-- 查看长时间运行的连接
SELECT 
    pid, 
    usename, 
    datname, 
    application_name, 
    backend_start, 
    state, 
    now() - backend_start AS uptime
FROM 
    pg_stat_activity
ORDER BY 
    uptime DESC;

-- 重启长时间运行的连接
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE now() - backend_start > INTERVAL '7 days';

监控与告警配置 ​

1. CPU 监控告警 ​

yaml
# Prometheus CPU 告警规则
- alert: PostgreSQLHighCPUUsage
  expr: (rate(process_cpu_seconds_total{job="postgresql"}[5m]) * 100) > 80
  for: 5m
  labels:
    severity: warning
  annotations:
    summary: "PostgreSQL CPU 使用率过高"
    description: "PostgreSQL 实例 CPU 使用率超过 80%，当前值: {{ $value | printf "%.2f" }}%"

- alert: PostgreSQLCriticalCPUUsage
  expr: (rate(process_cpu_seconds_total{job="postgresql"}[5m]) * 100) > 95
  for: 2m
  labels:
    severity: critical
  annotations:
    summary: "PostgreSQL CPU 使用率严重过高"
    description: "PostgreSQL 实例 CPU 使用率超过 95%，当前值: {{ $value | printf "%.2f" }}%"

2. 内存监控告警 ​

yaml
# Prometheus 内存告警规则
- alert: PostgreSQLHighMemoryUsage
  expr: (process_resident_memory_bytes{job="postgresql"} / 1024 / 1024 / 1024) > 16
  for: 5m
  labels:
    severity: warning
  annotations:
    summary: "PostgreSQL 内存使用率过高"
    description: "PostgreSQL 实例内存使用超过 16GB，当前值: {{ $value | printf "%.2f" }}GB"

- alert: PostgreSQLHighTempFileUsage
  expr: pg_stat_database_temp_bytes > 10737418240
  for: 5m
  labels:
    severity: warning
  annotations:
    summary: "PostgreSQL 临时文件过大"
    description: "数据库 {{ $labels.datname }} 临时文件超过 10GB，当前值: {{ $value | printf "%.2f" }}GB"

最佳实践 ​

1. 定期维护 ​

  • 分析表和索引：定期运行 ANALYZE 和 VACUUM 命令
  • 重建索引：定期重建碎片化严重的索引
  • 检查内存使用：定期检查内存配置和使用情况
  • 监控查询性能：定期分析慢查询日志和 pg_stat_statements 数据

2. 配置优化 ​

  • 根据硬件调整：根据服务器 CPU、内存配置调整 PostgreSQL 参数
  • 使用连接池：使用 PgBouncer 或 Pgpool-II 减少连接开销
  • 合理设置 work_mem：避免单个查询使用过多内存
  • 优化并行查询：根据 CPU 核心数调整并行查询参数

3. 查询优化 ​

  • 添加合适的索引：根据查询模式添加索引
  • 重写复杂查询：将复杂查询拆分为多个简单查询
  • 避免全表扫描：优化 WHERE 子句，使用索引
  • 限制返回行数：使用 LIMIT 子句限制返回数据量

常见问题（FAQ） ​

Q1：如何快速定位高 CPU 使用率的原因？ ​

A1：可以通过以下步骤快速定位：

  1. 使用 pg_stat_activity 查看当前正在执行的查询
  2. 使用 pg_stat_statements 查看历史高 CPU 查询
  3. 分析慢查询日志
  4. 检查系统级 CPU 使用情况

Q2：如何降低 PostgreSQL 的内存使用率？ ​

A2：降低内存使用率的方法包括：

  1. 调整 shared_buffers、work_mem 等内存参数
  2. 优化查询，减少临时文件使用
  3. 限制最大连接数
  4. 使用连接池
  5. 终止长时间运行的连接

Q3：如何优化高 CPU 消耗的查询？ ​

A3：优化高 CPU 查询的方法：

  1. 分析执行计划，添加合适的索引
  2. 重写查询，简化逻辑
  3. 避免在 WHERE 子句中使用函数
  4. 优化 JOIN 操作，确保连接字段有索引
  5. 考虑使用物化视图缓存复杂查询结果

Q4：PostgreSQL 内存配置的最佳实践是什么？ ​

A4：内存配置最佳实践：

  • shared_buffers：系统内存的 25%
  • effective_cache_size：系统内存的 50-75%
  • work_mem：根据查询复杂度调整，一般 4-16MB
  • maintenance_work_mem：系统内存的 5-10%，最大 2GB
  • max_connections：根据实际需要调整，避免过大

Q5：如何处理 PostgreSQL 内存泄漏？ ​

A5：处理内存泄漏的方法：

  1. 升级 PostgreSQL 到最新版本，修复已知内存泄漏
  2. 定期重启长时间运行的连接
  3. 检查应用代码，避免连接泄漏
  4. 监控内存使用趋势，及时发现问题
  5. 考虑使用连接池管理连接生命周期

Q6：如何监控 PostgreSQL 的 CPU 和内存使用情况？ ​

A6：监控方法包括：

  1. 使用内置视图：pg_stat_activity、pg_stat_statements
  2. 使用 Prometheus + Grafana 进行可视化监控
  3. 使用 pgAdmin 等管理工具
  4. 配置系统级监控：top、vmstat、iostat
  5. 启用慢查询日志，分析查询性能