Postgresql性能优化

如果想监控，需要安装一个组件

CREATE EXTENSION pg_stat_statements;

对于docker安装上面没问题，非docker的恐怕有些其他配置

想查看则执行下面sql

SELECT  query, calls, total_time, (total_time/calls) as average ,rows, 
        100.0 * shared_blks_hit /nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent 
FROM    pg_stat_statements 
ORDER   BY average DESC LIMIT 10;

会报错pg_stat_statements must be loaded via shared_preload_libraries

需要改一下配置文件默认配置文件是在/var/lib/postgresql/data/postgresql.conf，具体可以执行下面sql查看配置文件地址：

show config_file;

如果是docker安装需要将配置文件拷出来修改shared_preload_libraries加上pg_stat_statements

docker cp postgres:/var/lib/postgresql/data/postgresql.conf postgresql.conf
vi postgresql.conf
docker cp postgresql.conf postgres:/var/lib/postgresql/data/postgresql.conf
docker restart postgres

如果清除历史监控信息

select pg_stat_statements_reset() ;

更详细的信息more-on-postgres-performance

pg_stat_statements must be loaded via shared_preload_libraries