使用pg_stat_statement監控pgsql遇到的問題

沃趣科技發表於2016-06-20
SQL
作者：沃趣科技開發工程師  餘鵬

pg_stat_statements是PG中監控資料庫活動的重要外掛，通過它可以獲得SQL的統計資訊，例如該SQL被呼叫了多少次，返回了多少記錄，在讀寫資料上花了多少時間，這個對於監控資料庫很有幫助。預設情況下，這個外掛可以統計5000個SQL，如果不夠可以調整pg_stat_statements.max這個GUC。

一般情況下，可以通過原始碼安裝該外掛：

       1， 先編譯安裝pgsql

       2，在pg原始碼的目錄下執行 make install -C contrib/pg_stat_statements

       3，修改pg的配置檔案postgres.conf，在其中加上一行

shared_preload_libraries = 'pg_stat_statements'

        4，啟動資料庫，執行下面的SQL新增該外掛：

CREATE EXTENSION pg_stat_statements

該語句會在當前的資料庫中建立一個檢視pg_stat_statements,這個檢視中包含了很多有用的監控資訊.簡單的說來，這個外掛會在PG資料庫初始化的時候從共享記憶體中申請一塊區域，這塊區域主要是用作一個hash表，這個hash表將用來儲存SQL的統計資訊，預設是5000個不同的SQL。

前面說到這個外掛預設統計5000個SQL，那麼如果新執行了一個SQL，該外掛則會以SQL的查詢計劃為輸入來計算hash碼，（這個hash碼就是pg_stat_statements檢視中的queryid），然後去外掛的hash表中查詢，如果發現該hash碼和某個已經存在的SQL的queryid相同，則將統計結果累加到這個SQL的統計結果中；沒有發現，則會新增到外掛的hash表中，或是滿了5000條了就通過類似LRU的演算法替換掉某個SQL（這點細節需要看程式碼）。    

因此，這個外掛在比對SQL時，智慧程度還是很高的，只有語義上相同才會當作相同的SQL。例如下面三個SQL:

select * from t1 where a =1;
select * from t1 where a =2;
select * from t1 where a =3;

會被這個外掛當作一個SQL（嚴格上說是一類）。

但是，有時候太智慧也不太好：今天早上一個開發的同事來找我，說從pg_stat_statements中獲得了很多重複的結果。登上他的測試環境，結果的確讓人很奇怪：



 

很明顯，結果中出現了三種重複的結果：一個是使用sql來執行pg_xlog_location_diff這個函式，一類是執行drop操作，最後一個就是執行insert 操作。

第一眼看上去很吃驚，但是檢視了文件http://www.postgresql.org/docs/9.4/static/pgstatstatements.html，再結合下SQL的處理流程，可以回答為什麼前會有前兩種重複的結果：

1，對於執行select pg_xlog_location_diff() 這樣的SQL，主要是因為執行SQL的使用者不一樣，所以外掛認為是不同的SQL。並且，如果同一個使用者連線不同的資料庫去執行同一個SQL，外掛也會認為是不同的SQL。從查詢計劃的角度來看，使用者不同或是連線的資料庫不同，即使其他內容相同，在資料庫看來，也是不同的查詢計劃了。

2，對於drop 操作的SQL重複出現，其實可以從文件裡面就知道了，因為文件裡面說的很明確了“Plannable queries (that is, SELECT, INSERT, UPDATE, and DELETE) are combined into a single pg_stat_statements entry whenever they have identical query structures according to an internal hash calculation.” 換言之，drop操作是沒有查詢計劃的，因此沒法判斷一個drop操作之間是否相同，所以乾脆當作都不同的。

最後，對於insert 操作的SQL重複出現，剛開始怎麼也無法解釋，後來問了問開發的同學怎麼運算元據庫的，就恍然大悟了：因為他每次連資料庫都會建立一個心跳錶，這是個臨時表，然後執行那一堆insert 操作來判斷資料庫是否活著。


一說臨時表，就明白了七八分：因為他的臨時表是session級別的，一個session連上來建立的臨時表在斷開session時，pg會自動刪除臨時表。雖然每次建立的臨時表都是同一個表，一模一樣，但是從資料庫的角度來說，就是一個不同的表的了。所以每次session 第一次insert 這個表的SQL都會和前一次session執行的insert SQL是不同的，雖然他們字面上是一模一樣的。當然，他們生成的查詢計劃的語義也是完全不一樣的了。 而且考慮到pg_stat_statements的容量有限，最好還是建立一個非臨時表。


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28218939/viewspace-2120441/，如需轉載，請註明出處，否則將追究法律責任。

相關文章