使用pg_stat_statement监控pgsql遇到的问题

最新推荐文章于 2025-05-31 23:46:47 发布

最新推荐文章于 2025-05-31 23:46:47 发布 · 299 阅读

文章标签：

#数据库

数据库内核，pgsql 专栏收录该内容

4 篇文章

订阅专栏

本文介绍了pg_stat_statements插件的使用方法及原理，并详细解释了如何安装此插件、配置参数以及它如何通过哈希表存储SQL统计信息。同时探讨了在实际应用中遇到的一些常见问题及其解决办法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

pg_stat_statements是PG中监控数据库活动的重要插件，通过它可以获得SQL的统计信息，例如该SQL被调用了多少次，返回了多少记录，在读写数据上花了多少时间，这个对于监控数据库很有帮助。默认情况下，这个插件可以统计5000个SQL，如果不够可以调整pg_stat_statements.max这个GUC。

一般情况下，可以通过源码安装该插件：

1，先编译安装pgsql

2，在pg源码的目录下执行 make install -C contrib/pg_stat_statements

3，修改pg的配置文件postgres.conf，在其中加上一行

shared_preload_libraries = 'pg_stat_statements'

4，启动数据库，执行下面的SQL添加该插件：

CREATE EXTENSION pg_stat_statements

该语句会在当前的数据库中创建一个视图pg_stat_statements,这个视图中包含了很多有用的监控信息.简单的说来，这个插件会在PG数据库初始化的时候从共享内存中申请一块区域，这块区域主要是用作一个hash表，这个hash表将用来存储SQL的统计信息，默认是5000个不同的SQL。

前面说到这个插件默认统计5000个SQL，那么如果新执行了一个SQL，该插件则会以SQL的查询计划为输入来计算hash码，（这个hash码就是pg_stat_statements视图中的queryid），然后去插件的哈希表中查找，如果发现该hash码和某个已经存在的SQL的queryid相同，则将统计结果累加到这个SQL的统计结果中；没有发现，则会添加到插件的hash表中，或是满了5000条了就通过类似LRU的算法替换掉某个SQL（这点细节需要看代码）。

因此，这个插件在比对SQL时，智能程度还是很高的，只有语义上相同才会当作相同的SQL。例如下面三个SQL:

select * from t1 where a =1;
select * from t1 where a =2;
select * from t1 where a =3;

会被这个插件当作一个SQL（严格上说是一类）。

但是，有时候太智能也不太好：今天早上一个开发的同事来找我，说从pg_stat_statements中获得了很多重复的结果。登上他的测试环境，结果的确让人很奇怪：

很明显，结果中出现了三种重复的结果：一种是执行pg_xlog_location_diff函数的SQL；一种是执行drop操作；最后一种就是执行insert 操作。

第一眼看上去很吃惊，但是查看了文档http://www.postgresql.org/docs/9.4/static/pgstatstatements.html，再结合下SQL的处理流程，可以回答为什么前会有前两种重复的结果：

1，对于执行select pg_xlog_location_diff() 这样的SQL，主要是因为执行SQL的用户不一样，所以插件认为是不同的SQL。并且，如果同一个用户连接不同的数据库去执行同一个SQL，插件也会认为是不同的SQL。从查询计划的角度来看，用户不同或是连接的数据库不同，即使其他内容相同，在数据库看来，也是不同的查询计划了。

2，对于drop 操作的SQL重复出现，其实可以从文档里面就知道了，因为文档里面说的很明确了“Plannable queries (that is, SELECT, INSERT, UPDATE, and DELETE) are combined into a single pg_stat_statements entry whenever they have identical query structures according to an internal hash calculation.” 换言之，drop操作是没有查询计划的，因此没法判断一个drop操作之间是否相同，所以干脆当作都不同的

最后，对于insert 操作的SQL重复出现，刚开始怎么也无法解释，后来问了问开发的同学怎么操作数据库的，就恍然大悟了：因为他每次连数据库都会建立一个心跳表，这是个临时表，然后执行那一堆insert 操作来判断数据库是否活着。

一说临时表，就明白了七八分：因为他的临时表是session级别的，一个session连上来建立的临时表在断开session时，pg会自动删除临时表。虽然每次建立的临时表都是同一个表，一模一样，但是从数据库的角度来说，就是一个不同的表的了。所以每次session 第一次insert 这个表的SQL都会和前一次session执行的insert SQL是不同的，虽然他们字面上是一模一样的。当然，他们生成的查询计划的语义也是完全不一样的了。而且考虑到pg_stat_statements的容量有限，最好还是建立一个非临时表。