sql statements_Postgresql监控sql模块之pg_stat_statements模块--部署篇

概述

今天主要介绍一下PG一个很重要的模块--pg_stat_statements模块，对于做性能观察还是很不错的。

---

一、pg_state_statements模块简介

1、简介

pg_stat_statements模块提供一种方法追踪一个服务器所执行的所有 SQL 语句的执行统计信息，可以用于统计数据库的资源开销，分析TOP SQL。
该模块必须通过在postgresql.conf的shared_preload_libraries中增加pg_stat_statements来载入，因为它需要额外的共享内存。
这意味着增加或移除该模块需要一次服务器重启。
当pg_stat_statements被载入时，它会跟踪该服务器 的所有数据库的统计信息。
该模块提供了一个视图 pg_stat_statements以及函数pg_stat_statements_reset 和pg_stat_statements用于访问和操纵这些统计信息。
这些视图和函数不是全局可用的，但是可以用CREATE EXTENSION pg_stat_statements 为特定数据库启用它们。

2、字段含义

pg_stat_statements字段列含义如下

3、常用函数

 pg_stat_statements_reset() returns void

pg_stat_statements_reset丢弃目前由pg_stat_statements统计的所有信息，默认情况下，这个函数只能运行在超级用户下。

4、常用变量

1)pg_stat_statements.max(integer)

pg_stat_statements.max是最大追踪的统计数据数量(即，视图中的最大行数)。如果数据量大于最大值，那么执行最少的语句将会被丢弃(本人测试，如果语句执行次数都为1时，其次是时间久的数据被丢弃)，这个值默认是1000，这个变量在服务启动前设置。

2)pg_stat_statements.track(enum)

pg_stat_statements.track控制统计数据规则，top用于追踪top-level statement(直接由客户端方发送的)，all还会追踪嵌套的statements(例如在函数中调用的statements)

3)pg_stat_statements.track_utility(boolen)

pg_stat_statements.track_utility控制是否跟踪公共程序命令(utility commands)，公共程序命令是SELECT/INSERT/UPDATE/DELETE以外的命令，默认值是开启，只有超级用户可以更改此设置。

4)pg_stat_statements.save(boolean)

pg_stat_statements.save指定在服务器关闭时，是否保存统计信息。如果设置off，服务关闭时，统计信息将不会保存。默认值是on。这个值只能够在postgresql.conf中或者命令行设置。

该模块需要额外的共享内存，内存大小大致为pg_stat_statements.max* track_activity_query_size。要注意的是，一旦模块被加载，即使pg_stat_statements.track设置为none，共享内存都会被消耗。

---

二、安装pg_stat_statements

1、编译安装pg_stat_statements模块

pg_stat_statements是PostgreSQL的核心插件之一，可以在编译PostgreSQL时安装，也可以单独安装。如果$pgpath/share/extension目录下存在pg_stat_statements--1.0.sql，说明安装成功了

find / -name 'pg_stat_statements'cd /opt/software/postgresql-12.2/contrib/pg_stat_statementsmake && make install

2、修改配置文件postgresql.conf

$ vim /data/pgdata/data/postgresql.conf====================================================================================#------------------------------------------------------------------------------# PG_STAT_STATEMENTS OPTIONS#------------------------------------------------------------------------------shared_preload_libraries = 'pg_stat_statements' #加载pg_stat_statements模块track_io_timing = on              #跟踪IO消耗的时间pg_stat_statements.max = 10000    #最多保留多少条统计信息，通过LRU算法，覆盖老的记录。pg_stat_statements.track = all    #all：所有SQL包括函数内嵌套的SQL,top：直接执行的SQL(函数内的sql不被跟踪),none：不跟踪pg_stat_statements.track_utility=on # 是否跟踪非DML语句 (例如DDL，DCL)，on表示跟踪, off表示不跟踪pg_stat_statements.save = on      # 重启后是否保留统计信息 #track_activity_query_size = 2048  #设置单条SQL的最长长度，超过被截断显示

3、重启PG服务

cd /opt/postgres/bin./pg_ctl restart -m fast./pg_ctl status

4、加载pg_stat_statements模块

到这里就完成pg_stat_statements模块安装了。

./psqlcreate extension pg_stat_statements; dx

---

觉得有用的朋友多帮忙转发哦！后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注下~