通过下面查询可以计算缓存命中率,应该非常接近1,否则应该调整shared_buffers的配置,低于99%,可以尝试调大它的值
postgres=# select blks_hit::float/(blks_read + blks_hit) as cache_hit_ratio from pg_stat_database where datname=current_database(); cache_hit_ratio ------------------- 0.997730115836991 (1 row)
查询计算事务提交率,应该非常接近1,否则死锁或其他超时太多
postgres=# select xact_commit::float/(xact_commit + xact_rollback) as successful_xact_ratio from pg_stat_database where datname = current_database(); successful_xact_ratio ----------------------- 0.986184210526316 (1 row)
从性能角度看最有意义的数据是与索引vs顺序扫描有关的统计信息,以下是计算索引扫描与整个数据库的所有扫描的比率可以计算如下:
postgres=# select sum(idx_scan)/(sum(idx_scan) + sum(seq_scan)) as idx_scan_ratio from pg_stat_all_tables where schemaname ='public'; idx_scan_ratio ------------------------ 0.03305785123966942149
postgres=# 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; relname | idx_scan_ratio -----------------+------------------- test_json1 | 0 log_ins_201702 | 0 log_par201703 | 0 log_ins_201704 | 0 log_par201702 | 0 log_par201701 | 0 log_ins_201707 | 0 log_ins_201708 | 0 log_par_his | 0 log_ins_201706 | 0 log_ins_201703 | 0
关闭数据库, 开启pg_stat_statements
vi postgresql.conf #shared_preload_libraries = '' # (change requires restart) shared_preload_libraries = 'pg_stat_statements' pg_stat_statements.track = all
准备开启数据库报错
找到pg_stat_statements安装目录
/root/postgresql-10.0/contrib/pg_stat_statements 执行 make && make install
之后重启数据库成功
安装插件成功
查看sql的执行计划
在分析INSERT,UPDATE,delete,create tbase as 或者execute的时候,应该开启一个事务来执行,执行完之后进行回滚,避免因为用analyze对数据进行修改;
postgres=# begin ; BEGIN postgres=# create table ly(id int); CREATE TABLE postgres=# insert into ly values (1); INSERT 0 1 postgres=# explain select * from ly ; QUERY PLAN ------------------------------------------------------ Seq Scan on ly (cost=0.00..35.50 rows=2550 width=4) (1 row) postgres=# explain update ly set idid=2 wherr id=1; id postgres=# explain update ly set idid=2 wherr id=1; id postgres=# explain update ly set id=2 wherr id=1; 2021-10-19 06:22:49.383 EDT [3638] ERROR: syntax error at or near "wherr" at character 28 2021-10-19 06:22:49.383 EDT [3638] STATEMENT: explain update ly set id=2 wherr id=1; ERROR: syntax error at or near "wherr" LINE 1: explain update ly set id=2 wherr id=1; ^ postgres=# explain update ly set id=2 where id=1; 2021-10-19 06:22:54.384 EDT [3638] ERROR: current transaction is aborted, commands ignored until end of transaction block 2021-10-19 06:22:54.384 EDT [3638] STATEMENT: explain update ly set id=2 where id=1; ERROR: current transaction is aborted, commands ignored until end of transaction block
以上报错原因,begin没有提交
这样就可以了