postgresql之性能优化

2021-11-09 14:47:14

通过下面查询可以计算缓存命中率，应该非常接近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，ＵＰＤＡＴＥ，ｄｅｌｅｔｅ，ｃｒｅａｔｅ　ｔｂａｓｅ　ａｓ　或者ｅｘｅｃｕｔｅ的时候，应该开启一个事务来执行，执行完之后进行回滚，避免因为用ａｎａｌｙｚｅ对数据进行修改；

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

以上报错原因，ｂｅｇｉｎ没有提交

这样就可以了

码农公寓

相关文章