PostgreSQL性能

PostgreSQL性能优化。

性能

关注几个方面:

  • 索引使用
  • IO尽量使用缓存: cache hit ratio = blks_hit /(blks_hit+blks_read)
  • 并发连接
  • 避免死锁
  • 长时间查询

主要参数调优

参考 官方wiki 或Performance Tuning PostgreSQL

## postgres.conf
# 通常设置为几百~1000连接
max_connections = <num>

# 1/4 ~ 1/3内存
shared_buffers = <num>

# 其他可选参数
# 1/20~1/10的shared_buffers
work_mem=200MB
#推荐 1/4 shared_buffers
effective_cache_size = 1280MB

Explain

  • Explain SQL STATEMENT
    • cost 建议控制在300 以内
    • 重点关注缺少索引的大表
  • EXPLAIN ANALYZE 检查规划器预估值的准确性
    • Startup Cost
    • Max Time
    • Rows

监控

pg_top

yum install pg_top
# 使用
pg_top -W -U xulz -d testdb
? # 帮助命令
# 其他主机
pg_top -r -h 192.168.1.10 -p 5432 -d testdb -U postgres

监控性能数据

  • 内部使用 pg_stats

  • 监控用途

    • 数据库: pg_stat_database
    • 表: pg_stat_all_tables tup : rows
    • 查询 : pg_stat_statements 需要先enable plugin

    编辑postgres.conf

    shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all

表监控

SELECT sum(idx_scan)/(sum(idx_scan) + sum(seq_scan)) as idx_scan_ratio
FROM pg_stat_all_tables
WHERE schemaname=‘public';

SELECT relname,idx_scan::float/(idx_scan+seq_scan+1) as idx_scan_ratio
FROM pg_stat_all_tables
WHERE schemaname='public'
ORDER BY idx_scan_ratio ASC;

Troubleshooting

  1. 死锁问题检查 PG后台进程会以1秒的频率检测是否存在死锁，如果有死锁发生，可在postgresql-xxx.log发现关键字：

ERROR: deadlock detected

-- show deadlock_timeout

-- 锁等待的超时时间默认0，表示发生锁等待时永不超时
-- show lock_timeout

SELECT * FROM pg_stat_activity WHERE datname = 'deadlock database ID’;
-- 找到waiting字段, procpid找到对应的列值
SELECT pg_cancel_backend ('死锁的procpid值');

资源

参考