PostgreSQL数据库调优

ienki

（1）通过统计信息pg_catalog查看当前运行状况
①数据库大小
SELECT pg_size_pretty(pg_database_size('data_base_name'));

②表大小
SELECT pg_size_pretty(pg_relation_size('table_name'));

③当前链接数
SELECT count(*) FROM pg_stat_activity;

④整体缓存命中率（不低于99%）
SELECT
sum(heap_blks_read) as heap_read,
sum(heap_blks_hit)  as heap_hit,
sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as ratio
FROM
pg_statio_user_tables;

⑤整体索引缓存命中率（不低于99%）
SELECT
sum(idx_blks_read) as idx_read,
sum(idx_blks_hit)  as idx_hit,
(sum(idx_blks_hit) - sum(idx_blks_read)) / sum(idx_blks_hit) as ratio
FROM
pg_statio_user_indexes;

⑥IO次数/缓存命中率
SELECT
*,
(heap_blks_hit*100) / (heap_blks_read+heap_blks_hit) as ratio
FROM
pg_statio_all_tables
WHERE
heap_blks_hit >= 1 AND
schemaname = 'public'
ORDER BY
ratio;

⑦频繁被访问的表
SELECT
relname,
coalesce(seq_tup_read,0)+coalesce(idx_tup_fetch,0)+coalesce(n_tup_ins,0)+coalesce(n_tup_upd,0)+coalesce(n_tup_del,0) as total,
coalesce(seq_tup_read,0)+coalesce(idx_tup_fetch,0) as select,
coalesce(n_tup_ins,0) as insert,
coalesce(n_tup_upd,0) as update,
coalesce(n_tup_del,0) as delete
FROM
pg_stat_user_tables
ORDER BY
total desc;

⑧索引的使用（「idx_scan」「idx_tup_read」「inx_tup_fetch」为0的索引就没有必要）
SELECT  * FROM pg_stat_user_indexes;

⑨清除所有统计信息
SELECT pg_stat_reset();

（2）配置信息postgresql.conf
①连接
max_connections 最大链接数
②内存
shared_buffers 不要少于物理内存的1/4
work_mem 每个进程可以使用的工作内存空间，数据库的排序操作和哈希表使用的内存缓冲区的大小，如何work_mem指定的内存被耗尽，数据库将使用磁盘文件进行完成操作，速度会慢很多。
maintenance_work_mem VACUUM或者REINDEX处理可以使用的内存空间。
③WAL
max_fsm_pages FSM最大页数
wal_sync_method  向磁盘强制更新 WAL 数据的方法
checkpoint_segments 定量，写入大小。一个是16MB，默认是3，所以就是48MB。到了这个量就会调用写入处理。
checkpoint_timeout 定时，写入间隔。默认是5min（五分种）。过了5分钟就调入写入处理。
④查询
effective_cache_size  最大缓存
random_page_cost
⑤日志
log_min_duration_statement = 3s 会将执行时间超过3秒的SQL文输出到PostgreSQL的Log
⑥垃圾清理
autovacuum = on
***具体数据应该设置成多少，应该根据服务器的配置多次尝试后设置。
（3）其他
①VACUUM
postgresql中用于维护数据库磁盘空间的工具是VACUUM，其重要的作用是删除那些已经标示为删除的数据并释放空间。
②vacuumdb
vacuumdb是VACUUM 的封装，用户切换到postgres用户，在postgres\bin下执行：
引用
./vacuumdb  -a -z

常用参数
-a/--all            vacuum所有的数据库
-d dbname        只vacuum dbname这个数据库
-f/--full           执行full的vacuum
-t table             只vacuum table这个数据表
-z/--analyze      生成用于查询优化器的内部统计数据
PostgreSQL的更新处理是先DELETE后INSERT，所以大量UPDATE会生成垃圾，所以每天执行一回。
引用
vacuumdb -f -a -z

③SQL语句执行计划分析
引用
EXPLAIN ANALYZE select * from tbl;

④重建索引REINDEX
引用
psql -d dbname -c "REINDEX TABLE tbl;" username

⑤定期做Dump和Restore
参考：PostgreSQL常用命令
PostgreSQL Benchmarking Toolset ： pgbench-tools
PostgreSQL configuration wizard ： pgtune