PostgreSQL 启用 auto_explain 自动解析慢 SQL

PostgreSQL 性能优化 About 1,212 words

auto_explain 介绍

auto_explainPostgreSQL内置的插件。

备注:如果使用make worldmakr install-world编译安装的PostgreSQL,默认安装了该插件。

编译安装

进入插件目录(源码包中自带)

cd /home/postgres/postgresql-16.1/contrib/auto_explain

编译安装

make & make install

加载依赖库

查看预加载依赖库

show shared_preload_libraries;

修改配置,预加载依赖库

alter system set shared_preload_libraries = auto_explain;

如果有多个插件,可以使用以下命令

alter system set shared_preload_libraries = pg_stat_statements, auto_explain, "pg_jieba.so";

重启数据库

对于shared_preload_libraries的修改,需要重启才能生效。

2024-06-11 17:49:47.849 CST [24115] LOG:  received SIGHUP, reloading configuration files
2024-06-11 17:49:47.850 CST [24115] LOG:  parameter "shared_preload_libraries" cannot be changed without restarting the server
2024-06-11 17:49:47.850 CST [24115] LOG:  configuration file "/home/postgres/16.1/data/postgresql.auto.conf" contains errors; unaffected changes were applied

修改配置

重启后可以设置auto_explain的值,否则会报错。

alter system set auto_explain.log_min_duration = '250ms';
alter system set auto_explain.log_analyze = on;
alter system set auto_explain.log_verbose = on;

查看相关配置

show auto_explain.log_min_duration;
show auto_explain.log_analyze;
show auto_explain.log_verbose;

更多配置

https://www.postgresql.org/docs/16/auto-explain.html

Views: 163 · Posted: 2024-06-17

————        END        ————

Give me a Star, Thanks:)

https://github.com/fendoudebb/LiteNote

扫描下方二维码关注公众号和小程序↓↓↓

扫描下方二维码关注公众号和小程序↓↓↓
Today On History
Browsing Refresh