postgresql模塊——pg_stat_statements詳解和安裝測試

其實很簡單，最近可能需要對postgresql進行監控，所以接觸了很多相關的監控命令和工具，這邊文章主要是記錄下工作過程，怕之后會忘記。

轉載注明出處：http://blog.csdn.net/lengzijian/article/details/8133471

我想要的功能：記錄每條sql的執行時間，能夠查詢每天執行最慢的top10。

下面先介紹下pg_stat_statements：(翻譯)

引文原文地址：http://www.postgresql.org/docs/9.0/interactive/pgstatstatements.html

pg_stat_statements模塊提供了一種跟蹤執行的所有SQL語句的統計信息的方法。

這個模塊必須改寫配置文件postgresql.conf中的shared_preload_libraries變量(之后講解如何配置)，這是因為他需要額外的共享內存。同時也意味著需要重啟服務。

1-先看下pg_stat_statements視圖

字段名

類型

引用

說明

userid

oid

pg_authid.oid

執行者id

dbid

oid

pg_database.oid

執行數據庫id

query

text

執行的語句

calls

bigint

執行次數

total_time

double precision

執行總時間 (平均值=total_time/calls )

rows

bigint

影響的總行數

shared_blks_hit

bigint

共享塊命中數量

shared_blks_read

bigint

共享塊讀數量

shared_blks_written

bigint

共享塊寫數量

local_blks_hit

bigint

本地塊命中數量

local_blks_read

bigint

本地塊讀數量

local_blks_written

bigint

本地塊寫數量

如上視圖和函數pg_stat_statements_reset只有在數據庫已經正確安裝，并且已經執行pg_stat_statements.sql腳本后才會生效。只要pg_stat_statements成功添加，就會跟蹤服務器上所有的數據庫操作。

處于安全的原因，普通用戶不允許查看其他用戶執行的語句信息(query)，如果視圖安裝到他的數據庫，那么就可以查看相關的統計信息(子健做的實驗實在超級用戶下：postgres用戶postgres庫)

注意，如果語句信息(query)一樣，不論任何out-of-line變量的值被使用，都會認為這幾條聲明是相同的。使用out-of-line變量有助于組織語句并且可能回事統計數據更加有用

2---函數

pg_stat_statements_reset() returns void

pg_stat_statements_reset丟棄目前由pg_stat_statements統計的所有信息，默認情況下，這個函數只能運行在超級用戶下。

3-配置變量

pg_stat_statements.max(integer)

pg_stat_statements.max是最大追蹤的統計數據數量(即，視圖中的最大行數)。如果數據量大于最大值，那么執行最少的語句將會被丟棄(本人測試，如果語句執行次數都為1時，其次是時間久的數據被丟棄)，這個值默認是1000，這個變量在服務啟動前設置。

pg_stat_statements.track(enum)

pg_stat_statements.track控制統計數據規則，top用于追蹤top-level statement(直接由客戶端方發送的)，all還會追蹤嵌套的statements(例如在函數中調用的statements)

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中或者命令行設置。

該模塊需要額外的共享內存，內存大小大致為pg_stat_statements.max* track_activity_query_size。要注意的是，一旦模塊被加載，即使pg_stat_statements.track設置為none，共享內存都會被消耗。

上面的都是一些需要掌握的知識，下面開始真正配置pg_stat_statements并且運行

首先要編寫postgresql.conf

#postgresql.conf

#------------------------------------------------------------------------------

# PG_STAT_STATEMENTS OPTIONS

#------------------------------------------------------------------------------

shared_preload_libraries = 'pg_stat_statements'

custom_variable_classes = 'pg_stat_statements'

pg_stat_statements.max = 1000

pg_stat_statements.track = all

4-編譯安裝pg_stat_statements模塊

進入postgresql的源碼目錄：

cd /home/proxy_pg/postgresql-9.1.3/contrib/pg_stat_statements

make

make install

#如果$pgpath/share/extension目錄下存在pg_stat_statements--1.0.sql，說明安裝成功了

5-加載pg_stat_statements模塊

#啟動postgresql服務

bin/pg_ctl start -D stat_date/

#加載sql文件

[postgres@slave2 pgsql]$ bin/psql -f share/extension/pg_stat_statements--1.0.sql -p 5499

Use "CREATE EXTENSION pg_stat_statements" to load this file.

#進入數據庫做，如下操作：

[postgres@slave2 pgsql]$ bin/psql -p 5499

psql (9.1.3)

Type "help" for help.

postgres=# create extension pg_stat_statements;?????????#創建pg_stat_statements

CREATE EXTENSION

postgres=# SELECT pg_stat_statements_reset();??????????#清空pg_stat_statements(可以不做)

pg_stat_statements_reset

--------------------------

(1 row)

#我們手動插入10條數據：(這里是自己寫的腳本，可以通過http://blog.csdn.net/lengzijian/article/details/7729465下載)

./a.out 1 10

#執行如下命令

SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit /nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5;

#可以看到最耗時的5條數據，最后一列表示命中率

雖然成功了，但這也是監控系統中很小的一部分，接下來我的挑戰是把各種監控系統融合在意思，做出可視化界面，使監控更友好。

最耗 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;

重置統計信息

pg_stat_statements 是累積的統計，如果要查看某個時間段的統計，需要打快照，建議您參見文檔《PostgreSQL AWR報告(for 阿里云ApsaraDB PgSQL)》。

您也可以通過執行如下命令，來定期清理歷史統計信息。

select pg_stat_statements_reset();

參考文檔

PostgreSQL 9.6.2 Documentation — F.29. pg_stat_statements

PostgreSQL SQL

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。