PostgreSQL/pgsql监控--慢查询插件pg_stat_statements

从天而降的扣篮

已于 2022-12-07 18:05:41 修改

阅读量2.3k

点赞数

分类专栏： # PostgreSQL 数据库文章标签： postgresql 数据库

于 2022-07-14 19:44:31 首次发布

原文链接：https://www.modb.pro/db/102710

版权

PostgreSQL 同时被 2 个专栏收录

12 篇文章

订阅专栏

数据库

10 篇文章

订阅专栏

本文转载自公众号【新运维新数据】

文章来源：PostgreSQL监控（一）---pg_stat_statements - 墨天轮

PS：为方便新人阅读，本人对部分内容做了修改，如【扩展】改为【扩展插件】，因为在日常习惯上，【扩展】是动词，但此文中其实它是名词。

PS：更多内容请阅读“postgresql公司”的英文手册以及“PgSQL中文社区”翻译的中文手册对应章节：

postgresql公司-PgSQL14-英文手册pg_stat_statements对应章节https://www.postgresql.org/docs/14/pgstatstatements.html postgresql公司-PgSQL15-英文手册pg_stat_statements对应章节https://www.postgresql.org/docs/15/pgstatstatements.html

postgresql中文社区-PgSQL14-中文手册pg_stat_statements对应章节http://www.postgres.cn/docs/14/pgstatstatements.html

文章开始：

#1 pg_stat_statements

在日常运维工作中，我们会经常需要定位一些SQL引起的性能问题，包括分析执行SQL的统计信息、定位抓取慢SQL语句等等。

本期就为大家介绍一款监控SQL语句的扩展插件pg_stat_statements。

pg_stat_statements模块提供了一个方法用来追踪服务器所执行的所有SQL语句执行统计信息，也可以记录数据库的资源开销，得益于该模块的作用，使得PostgreSQL在一些性能分析的问题上也获得了横向的扩展，直观且快捷地展现出SQL语句对于数据库的性能影响。

#2 在Linux中安装配置pg_stat_statements扩展插件

下面就介绍一下如何安装并使用这个扩展插件，如果在安装数据库服务的时候已经编译好了，那就可以在数据库内直接创建扩展即可。

当然也可以对其进行单独的编译安装，找到源码目录，进入到pg_stat_statements目录下。

$ cd src/contrib/pg_stat_statements/
$ make && make install

完成了扩展的编译安装，我们还需要修改配置文件中的相应参数' shared_preload_libraries '，这个变量指定一个或者多个要在服务器启动时预载入的共享库，使用pg_stat_statements扩展插件需要将其加入共享库中（同时使用多个扩展插件时，使用英文逗号分隔）

$ vi postgresql.conf
shared_preload_libraries='pg_stat_statements'

可以根据自己的需要，配置以下几个常用的参数

pg_stat_statements.max = 10000      #保留多少条统计信息，默认值5000
pg_stat_statements.track = all        #记录所有sql
pg_stat_statements.track_utility = off   #是否跟踪非DML语句
pg_stat_statements.save = on    #重启之后是否保留统计信息

配置完成后需要重启数据库服务，然后登录数据库，创建扩展

postgres=# create extension pg_stat_statements;

至此，扩展插件安装完成。

可以在数据库的public模式中看到，我们获得了一个新的视图【pg_stat_statements】，SQL执行的统计信息都可以从该视图获取信息，文末附录了该视图各字段的解释

#3 常用统计SQL

以下列出了几个常用的统计SQL供参考

--最耗时的TOP 5 SQL
select dbid, query from pg_stat_statements 
 order by total_time desc limit 5;

--最消耗IO的TOP 5 SQL
select dbid, query from pg_stat_statements 
 order by  (blk_read_time+blk_write_time) desc limit 5;

--响应时间抖动最严重的TOP 5 SQL
select dbid, query from pg_stat_statements 
 order by  stddev_time desc limit 5;

快速、直观的SQL统计信息，能够帮助运维工程师在日常维护工作中更迅速地定位一些因为执行SQL引起的数据库性能问题，pg_stat_statements插件无疑提供了一个极为高效的方法和途径！

#4附录

pg_stat_statements视图中各字段释义（注：新版本的PgSQL提供的字段会有调整，详见上面链接里相应版本的中文手册或英文手册）