pg_stat_statement如何安裝部署使用

pg_stat_statements子產品提供一種跟蹤執行統計伺服器執行的所有SQL語句的手段。該子產品預設是不開啟的，如果需要開啟需要我們手動對其進進行編譯安裝，修改配置檔案并重新開機資料庫，并在使用前手動載入該子產品。

1、手動對pg_stat_statement子產品進行編譯安裝

-- 進入pgsql軟體的contrib目錄下，檢視是否有pg_stat_statements相關子產品
# pwd
/usr/local/postgresql-12.2/contrib
# ls | grep pg_stat_statements
pg_stat_statements

-- 進入pg_stat_statement子產品目錄下進行編譯安裝
# make && make install

-- 檢查pg_stat_statement是否安裝成功
# cd /usr/local/pgsql/share/extension/
# ll | grep pg_stat_statements
-rw-r--r-- 1 root root  1246 10月 25 15:46 pg_stat_statements--1.0--1.1.sql
-rw-r--r-- 1 root root  1336 10月 25 15:46 pg_stat_statements--1.1--1.2.sql
-rw-r--r-- 1 root root  1454 10月 25 15:46 pg_stat_statements--1.2--1.3.sql
-rw-r--r-- 1 root root   345 10月 25 15:46 pg_stat_statements--1.3--1.4.sql
-rw-r--r-- 1 root root   305 10月 25 15:46 pg_stat_statements--1.4--1.5.sql
-rw-r--r-- 1 root root  1427 10月 25 15:46 pg_stat_statements--1.4.sql
-rw-r--r-- 1 root root   376 10月 25 15:46 pg_stat_statements--1.5--1.6.sql
-rw-r--r-- 1 root root   806 10月 25 15:46 pg_stat_statements--1.6--1.7.sql
-rw-r--r-- 1 root root   191 10月 25 15:46 pg_stat_statements.control
-rw-r--r-- 1 root root   449 10月 25 15:46 pg_stat_statements--unpackaged--1.0.sql
           

2、修改資料庫配置檔案postgres，允許pg_stat_statements子產品加載到共享記憶體中

-- 修改配置檔案
# vi postgres.conf
shared_preload_libraries='pg_stat_statements,pg_pathman'
pg_stat_statements.max = 10000
pg_stat_statements.track = all
           

3、重新開機資料庫，登入資料庫後加載pg_stat_statement子產品

-- 重新開機資料庫
# su - postgres
$ pg_ctl -D /data/pgsql12/data restart

-- 登入資料庫，載入pg_stat_statement子產品
postgres=# SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_statements';               //檢視可用子產品
-[ RECORD 1 ]-----+----------------------------------------------------------
name              | pg_stat_statements
default_version   | 1.7
installed_version |
comment           | track execution statistics of all SQL statements executed

postgres=# create extension pg_stat_statements;         //載入子產品，載入後pg_stat_statements表可正常使用
CREATE EXTENSION

           

4、pg_stat_statements表的使用

postgres=# select * from  pg_stat_statements limit 1;
-[ RECORD 1 ]-------+------------------------------------------------------------------------
userid              | 10                        //使用者id
dbid                | 13547                     //資料庫oid
queryid             | 1194713979                //查詢id
query               | SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_statements'   //查詢SQL
calls               | 1                         //調用次數
total_time          | 53.363875                 //SQL總共執行時間
min_time            | 53.363875                 //SQL最小執行時間
max_time            | 53.363875                 //SQL最大執行時間
mean_time           | 53.363875                 //SQL平均執行時間
stddev_time         | 0                         //SQL花費時間的表中偏差
rows                | 1                         //SQL傳回或者影響的行數
shared_blks_hit     | 1                         //SQL在在shared_buffer中命中的塊數
shared_blks_read    | 0                         //SQL從page cache或者磁盤中讀取的塊數
shared_blks_dirtied | 0                         //SQL語句弄髒的shared_buffer的塊數
shared_blks_written | 0                         //SQL語句寫入的塊數
local_blks_hit      | 0                         //臨時表中命中的塊數
local_blks_read     | 0                         //臨時表需要讀的塊數
local_blks_dirtied  | 0                         //臨時表弄髒的塊數
local_blks_written  | 0                         //臨時表寫入的塊數
temp_blks_read      | 0                         //從臨時檔案讀取的塊數
temp_blks_written   | 0                         //從臨時檔案寫入的資料塊數
blk_read_time       | 0                         //從磁盤或者讀取花費的時間
blk_write_time      | 0                         //從磁盤寫入花費的時間