postgresql|数据库|插件学习（一）---postgresql-12的内置插件pg_stat_statements的启用和使用

news/2024/11/8 15:33:37/

前言：

        插件就是原软件的扩展功能。postgresql有非常多的各种各样的插件，当然了，插件不安装对于我们使用数据库并没有什么太多的影响，可能只是不舒服一些而已，但有一些插件我们如果有安装，那么，对于数据库的维护，管理工作可能会更加的方便，快捷，pg_stat_statements这个插件就是这样的一个插件，概括起来，这个插件的功能就是提供postgresql监控的，提供了一种跟踪执行的所有SQL语句的统计信息的方法。

        这里多说一句，不像MySQL，开启binlog日志就可以统计慢查询了，postgresql需要启用这个插件来统计慢查询。（通常，postgresql安装完毕后，默认是不启用这个插件的，因为这个插件会消耗系统的内存，因此。如果数据库负载平时就比较重的情况下，谨慎开启此插件）

        OK，下面将就如何编译安装此插件并开启插件以及最后的使用此插件达到我们的数据库监控的目的做一个简单的介绍。

本文使用的数据库示例的版本是postgresql-12.5，安装方式为源码编译安装，操作系统是centos7

一，

源码安装包和安装目录

源码包指的是postgresql-12.5.tar.bz2

安装目录如下：

PGDATA=/opt/pgsql/data
export PGDATA
PGHOME=/opt/pgsql
export PGHOME
PATH=$PATH:$PGHOME/bin:$PGDATA
export PATH PGDATA

二，

编译安装

源码包内有如下目录：

[root@centos61 postgresql-12.5]# ll
total 1216
-rw-r--r--.  1 1107 1107    522 Nov 10  2020 aclocal.m4
drwxrwxrwx.  2 1107 1107   4096 Nov 10  2020 config
-rw-r--r--.  1 root root 434063 May 20 20:17 config.log
-rwxr-xr-x.  1 root root  39995 May 20 20:17 config.status
-rwxr-xr-x.  1 1107 1107 575329 Nov 10  2020 configure
-rw-r--r--.  1 1107 1107  84108 Nov 10  2020 configure.in
drwxrwxrwx. 56 1107 1107   4096 Nov 10  2020 contrib
-rw-r--r--.  1 1107 1107   1192 Nov 10  2020 COPYRIGHT
drwxrwxrwx.  3 1107 1107     87 Nov 10  2020 doc
-rw-r--r--.  1 root root   3998 May 20 20:17 GNUmakefile
-rw-r--r--.  1 1107 1107   3998 Nov 10  2020 GNUmakefile.in
-rw-r--r--.  1 1107 1107    284 Nov 10  2020 HISTORY
-rw-r--r--.  1 1107 1107  61479 Nov 10  2020 INSTALL
-rw-r--r--.  1 1107 1107   1665 Nov 10  2020 Makefile
-rw-r--r--.  1 1107 1107   1212 Nov 10  2020 README
drwxrwxrwx. 16 1107 1107   4096 May 20 20:17 src

contrib目录就是插件目录，进入该目录编译安装即可（make和make install）：

[root@centos61 pg_stat_statements]# pwd
/root/postgresql-12.5/contrib/pg_stat_statements
[root@centos61 pg_stat_statements]# ll
total 240
drwxrwxrwx. 2 1107 1107    36 Nov 10  2020 expected
-rw-r--r--. 1 1107 1107  1128 Nov 10  2020 Makefile
-rw-r--r--. 1 1107 1107  1246 Nov 10  2020 pg_stat_statements--1.0--1.1.sql
-rw-r--r--. 1 1107 1107  1336 Nov 10  2020 pg_stat_statements--1.1--1.2.sql
-rw-r--r--. 1 1107 1107  1454 Nov 10  2020 pg_stat_statements--1.2--1.3.sql
-rw-r--r--. 1 1107 1107   345 Nov 10  2020 pg_stat_statements--1.3--1.4.sql
-rw-r--r--. 1 1107 1107   305 Nov 10  2020 pg_stat_statements--1.4--1.5.sql
-rw-r--r--. 1 1107 1107  1427 Nov 10  2020 pg_stat_statements--1.4.sql
-rw-r--r--. 1 1107 1107   376 Nov 10  2020 pg_stat_statements--1.5--1.6.sql
-rw-r--r--. 1 1107 1107   806 Nov 10  2020 pg_stat_statements--1.6--1.7.sql
-rw-r--r--. 1 1107 1107 92975 Nov 10  2020 pg_stat_statements.c
-rw-r--r--. 1 1107 1107    48 Nov 10  2020 pg_stat_statements.conf
-rw-r--r--. 1 1107 1107   191 Nov 10  2020 pg_stat_statements.control
-rw-r--r--. 1 root root 53528 May 20 20:28 pg_stat_statements.o
-rwxr-xr-x. 1 root root 43992 May 20 20:28 pg_stat_statements.so
-rw-r--r--. 1 1107 1107   449 Nov 10  2020 pg_stat_statements--unpackaged--1.0.sql
drwxrwxrwx. 2 1107 1107    36 Nov 10  2020 sql

[root@centos61 pg_stat_statements]# make
make -C ../../src/backend generated-headers
make[1]: Entering directory `/root/postgresql-12.5/src/backend'
make -C catalog distprep generated-header-symlinks
make[2]: Entering directory `/root/postgresql-12.5/src/backend/catalog'
make[2]: Nothing to be done for `distprep'.
make[2]: Nothing to be done for `generated-header-symlinks'.
make[2]: Leaving directory `/root/postgresql-12.5/src/backend/catalog'
make -C utils distprep generated-header-symlinks
make[2]: Entering directory `/root/postgresql-12.5/src/backend/utils'
make[2]: Nothing to be done for `distprep'.
make[2]: Nothing to be done for `generated-header-symlinks'.
make[2]: Leaving directory `/root/postgresql-12.5/src/backend/utils'
make[1]: Leaving directory `/root/postgresql-12.5/src/backend'
[root@centos61 pg_stat_statements]# make install
make -C ../../src/backend generated-headers
make[1]: Entering directory `/root/postgresql-12.5/src/backend'
make -C catalog distprep generated-header-symlinks
make[2]: Entering directory `/root/postgresql-12.5/src/backend/catalog'
make[2]: Nothing to be done for `distprep'.
make[2]: Nothing to be done for `generated-header-symlinks'.
make[2]: Leaving directory `/root/postgresql-12.5/src/backend/catalog'
make -C utils distprep generated-header-symlinks
make[2]: Entering directory `/root/postgresql-12.5/src/backend/utils'
make[2]: Nothing to be done for `distprep'.
make[2]: Nothing to be done for `generated-header-symlinks'.
make[2]: Leaving directory `/root/postgresql-12.5/src/backend/utils'
make[1]: Leaving directory `/root/postgresql-12.5/src/backend'
/usr/bin/mkdir -p '/usr/local/pgsql/lib'
/usr/bin/mkdir -p '/usr/local/pgsql/share/extension'
/usr/bin/mkdir -p '/usr/local/pgsql/share/extension'
/usr/bin/install -c -m 755  pg_stat_statements.so '/usr/local/pgsql/lib/pg_stat_statements.so'
/usr/bin/install -c -m 644 ./pg_stat_statements.control '/usr/local/pgsql/share/extension/'
/usr/bin/install -c -m 644 ./pg_stat_statements--1.4.sql ./pg_stat_statements--1.6--1.7.sql ./pg_stat_statements--1.5--1.6.sql ./pg_stat_statements--1.4--1.5.sql ./pg_stat_statements--1.3--1.4.sql ./pg_stat_statements--1.2--1.3.sql ./pg_stat_statements--1.1--1.2.sql ./pg_stat_statements--1.0--1.1.sql ./pg_stat_statements--unpackaged--1.0.sql  '/usr/local/pgsql/share/extension/'

观察发现，编译的内容在/usr/local/pgsql目录下，因此，将以下文件拷贝到/opt/pgsql目录下即可：

[root@centos61 pgsql]# tree -a ./
./
├── lib
│   ├── dblink.so
│   └── pg_stat_statements.so
└── share└── extension├── dblink--1.0--1.1.sql├── dblink--1.1--1.2.sql├── dblink--1.2.sql├── dblink.control├── dblink--unpackaged--1.0.sql├── pg_stat_statements--1.0--1.1.sql├── pg_stat_statements--1.1--1.2.sql├── pg_stat_statements--1.2--1.3.sql├── pg_stat_statements--1.3--1.4.sql├── pg_stat_statements--1.4--1.5.sql├── pg_stat_statements--1.4.sql├── pg_stat_statements--1.5--1.6.sql├── pg_stat_statements--1.6--1.7.sql├── pg_stat_statements.control└── pg_stat_statements--unpackaged--1.0.sql3 directories, 17 files

[root@centos61 pgsql]# pwd
/usr/local/pgsql
[root@centos61 pgsql]# ls
lib  share
[root@centos61 pgsql]# cp lib/* /opt/pgsql/lib/
[root@centos61 pgsql]# cp share/* /opt/pgsql/share/

二，

激活pg_stat_statements插件的准备工作

编辑postgresql.conf文件，准备开启此插件：

shared_preload_libraries = 'pg_stat_statements' # (change requires restart)
pg_stat_statements.max = 10000
pg_stat_statements.track = all
track_activity_query_size = 4096

以上参数的说明：

  • pg_stat_statements.max(integer)

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

  • pg_stat_statements.track(enum)

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

  • pg_stat_statements.track_utility(boolen)

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

  • pg_stat_statements.save(boolean)

           pg_stat_statements.save指定在服务器关闭时，是否保存统计信息。如果设置off，服务关闭时，统计信息将不会保存。默认值是on。这个值只能够在postgresql.conf中或者命令行设置。（因为默认是开启，所以这里不做显式设置）

  • track_activity_query_size

该模块需要额外的共享内存，内存大小大致为pg_stat_statements.max* track_activity_query_size。要注意的是，一旦模块被加载，即使pg_stat_statements.track设置为none，共享内存都会被消耗。假如 pg_stat_statements.max 值为 10000, track_activity_query_size值为4096， 也就消耗了 40 M内存。（本例为40M共享内存）

三，

命令行激活插件

postgres=# create extension pg_stat_statements;
ERROR:  extension "pg_stat_statements" already exists
（已经激活过了）

那么，激活过后的会出现一个视图和两个函数：

#注，可以看到dx命令后的插件说明，此插件的版本是1.7


postgres=# \dxList of installed extensionsName        | Version |   Schema   |                         Description                          
--------------------+---------+------------+--------------------------------------------------------------pg_stat_statements | 1.7     | public     | track execution statistics of all SQL statements executedplpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural languagepostgres=# \dvList of relationsSchema |        Name        | Type |  Owner   
--------+--------------------+------+----------public | pg_stat_statements | view | postgres
(1 row)

 四，

使用此插件

当然了，在使用以下查询的时候，最好是有一些实际的查询SQL语句执行，否则会看不出来效果。

最耗 IO SQL

执行如下命令，查询单次调用最耗 IO SQL TOP 5。

 

  1. select userid::regrole, dbid, query from pg_stat_statements order by (blk_read_time+blk_write_time)/calls desc limit 5;
 

执行如下命令，查询总最耗 IO SQL TOP 5。
 

 

  1. select userid::regrole, dbid, query from pg_stat_statements order by (blk_read_time+blk_write_time) desc limit 5;
 

最耗时 SQL
 

执行如下命令，查询单次调用最耗时 SQL TOP 5。
 

 

  1. select userid::regrole, dbid, query from pg_stat_statements order by mean_time desc limit 5;
 

执行如下命令，查询总最耗时 SQL TOP 5。
 

 

  1. select userid::regrole, dbid, query from pg_stat_statements order by total_time desc limit 5;
 

响应时间抖动最严重 SQL
 

执行如下命令，查询响应时间抖动最严重 SQL。
 

 

  1. select userid::regrole, dbid, query from pg_stat_statements order by stddev_time desc limit 5;
 

最耗共享内存 SQL
 

执行如下命令，查询最耗共享内存 SQL。
 

 

  1. select userid::regrole, dbid, query from pg_stat_statements order by (shared_blks_hit+shared_blks_dirtied) desc limit 5;
 

最耗临时空间 SQL
 

执行如下命令，查询最耗临时空间 SQL。
 

 

  1. select userid::regrole, dbid, query from pg_stat_statements order by temp_blks_written desc limit 5;
 

小结：
 

postgresql的插件种类非常多，但pg_stat_statements这个插件是非常有用的基础插件，此插件安装是比较简单的，但需要注意，postgresql.conf 文件内的关于此插件的任何改动都需要重启数据库才可以生效。











http://www.ppmy.cn/news/76689.html












相关文章










python获取tx弹幕数据并制作词云图









python获取tx弹幕数据并制作词云图




前言 
大家早好、午好、晚好吖 ❤ ~欢迎光临本文章 知识点介绍: 爬虫基本思路流程  requests模块的使用  pandas读取表格数据  
环境介绍: 
开发环境: python 3.8 运行代码  pycharm 2022.3 辅助敲代码  
模块使用： requests >>> pip install requests  pa…




阅读更多...

















抖音矩阵系统开发分析（附代码部分）









抖音矩阵系统开发分析（附代码部分）




简介 
本文档旨在介绍抖音SEO源码开发相关内容，包括技术架构、关键模块、实现原理等方面的内容。 技术架构 
抖音SEO源码采用的是分布式架构。整个系统可以分为以下几个模块： 
数据抓取模块：负责从抖音服务器抓取数据，并进行去重和…




阅读更多...

















进程控制-进程的替换









进程控制-进程的替换




回顾 
对于进程，我们已经有了初步的了解，我们学会了进程的终止，进程的等待，还有如果创建子进程。 
这次，我们来学习如何把一个进程运行的代码和数据替换成其他的代码和数据。 
进程的替换 
为什么要进程替换&#xff0…




阅读更多...

















微信小程序全局路由拦截









微信小程序全局路由拦截




前言 
略 
微信小程序全局路由拦截方法1 
目前微信小程序没有全局路由拦截。要想实现全局路由拦截，需要自己进行扩充。具体参考这里：微信小程序–路由拦截器。 
实现思路： 
替换Page的参数对象的onShow或onLoad方法。在替换的onShow或onLoad方…




阅读更多...

















ChatGPT：你真的了解网络安全吗？浅谈攻击防御进行时之网络安全新总结









ChatGPT：你真的了解网络安全吗？浅谈攻击防御进行时之网络安全新总结




ChatGPT：你真的了解网络安全吗？浅谈网络安全攻击防御进行时 网络安全新总结总结 ChatGPT（全名：Chat Generative Pre-trained Transformer），美国OpenAI 研发的聊天机器人程序，是人工智能技术驱动…




阅读更多...

















直播和短视频美颜sdk的开发流程、代码分析









直播和短视频美颜sdk的开发流程、代码分析




目前，美颜技术是提高视频质量的重要手段之一，特别是短视频和直播两个行业。本文将介绍其开发流程和代码分析。 
一、美颜SDK的开发流程 
1.需求分析 
首先我们需要明确的一点就是“需求”，例如：美颜效果、美颜程度、性能要求等。同…




阅读更多...

















Windows系统数据结构——最小生成树、Prim算法和Kruskal算法









Windows系统数据结构——最小生成树、Prim算法和Kruskal算法




我是荔园微风，作为一名在IT界整整25年的老兵，今天总结一下Windows系统数据结构——最小生成树、Prim算法和Kruskal算法。 
我在各在论坛看了很多相关帖子，发现一个简单的问题都被复杂化了。最小生成树、Prim算法和Kruskal算法真的没有大家想的…




阅读更多...

















shopee虾皮跨境电商网站商品数据支持网站后缀（.com.my；.vn；.ph）









shopee虾皮跨境电商网站商品数据支持网站后缀（.com.my；.vn；.ph）




作为一名技术爱好者，我们总会遇到各种各样的技术问题，需要寻找合适的技术解决方案。而在互联网时代，我们可以快速通过搜索引擎获取丰富的技术资源和解决方案。然而，在不同的技术分享中，我们常常会遇到质量参差不齐的文…




阅读更多...















 
 
 
 










最新文章







 





















Copyright @ 2022~2023