當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

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

發布時間：2024/1/18 数据库 36 豆豆

生活随笔收集整理的這篇文章主要介紹了 postgresql|数据库|插件学习（一）---postgresql-12的内置插件pg_stat_statements的启用和使用小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

前言：

????????插件就是原軟件的擴展功能。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

?四，

使用此插件

當然了，在使用以下查詢的時候，最好是有一些實際的查詢SQL語句執行，否則會看不出來效果。

最耗 IO SQL

執行如下命令，查詢單次調用最耗 IO SQL TOP 5。

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。

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

最耗時 SQL

執行如下命令，查詢單次調用最耗時 SQL TOP 5。

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

執行如下命令，查詢總最耗時 SQL TOP 5。

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

響應時間抖動最嚴重 SQL

執行如下命令，查詢響應時間抖動最嚴重 SQL。

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

最耗共享內存 SQL

執行如下命令，查詢最耗共享內存 SQL。

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

最耗臨時空間 SQL

執行如下命令，查詢最耗臨時空間 SQL。

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

小結：

postgresql的插件種類非常多，但pg_stat_statements這個插件是非常有用的基礎插件，此插件安裝是比較簡單的，但需要注意，postgresql.conf?文件內的關于此插件的任何改動都需要重啟數據庫才可以生效。

總結

以上是生活随笔為你收集整理的postgresql|数据库|插件学习（一）---postgresql-12的内置插件pg_stat_statements的启用和使用的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。

上一篇：【题解】洛谷P2114 [NOI2014
下一篇： ISO体系文件控制程序