PostgreSQL 如何查找TOP SQL (例如IO消耗最高的SQL)

postgresql , pg_stat_statements , top sql

資料庫是較大型的應用，對于繁忙的資料庫，需要消耗大量的記憶體、cpu、io、網絡資源。

sql優化是資料庫優化的手段之一，優化什麼sql效果最佳呢？首先要了解最耗費資源的sql，即top sql。

從哪裡可以了解資料庫的資源都被哪些sql消耗掉了呢？

資源分為多個次元，cpu，記憶體，io等。如何能了解各個次元層面的top sql呢？

pg_stat_statements插件可以用于統計資料庫的資源開銷，分析top sql。

pg_stat_statements是postgresql的核心插件之一。可以在編譯postgresql時安裝，也可以單獨安裝。

編譯時安裝

單獨安裝

如果要跟蹤io消耗的時間，還需要打開如下參數

設定單條sql的最長長度，超過被截斷顯示（可選）

重新開機資料庫

在需要查詢top sql的資料庫中，建立extension

查詢pg_stat_statements視圖，可以得到統計資訊

sql語句中的一些過濾條件在pg_stat_statements中會被替換成變量，減少重複顯示的問題。

pg_stat_statements視圖包含了一些重要的資訊，例如：

1. sql的調用次數，總的耗時，最快執行時間，最慢執行時間，平均執行時間，執行時間的方差（看出抖動），總共掃描或傳回或處理了多少行；

2. shared buffer的使用情況，命中，未命中，産生髒塊，驅逐髒塊。

3. local buffer的使用情況，命中，未命中，産生髒塊，驅逐髒塊。

4. temp buffer的使用情況，讀了多少髒塊，驅逐髒塊。

5. 資料塊的讀寫時間。

name

type

references

description

userid

oid

pg_authid.oid

oid of user who executed the statement

dbid

pg_database.oid

oid of database in which the statement was executed

queryid

bigint

-

internal hash code, computed from the statement's parse tree

query

text

text of a representative statement

calls

number of times executed

total_time

double precision

total time spent in the statement, in milliseconds

min_time

minimum time spent in the statement, in milliseconds

max_time

maximum time spent in the statement, in milliseconds

mean_time

mean time spent in the statement, in milliseconds

stddev_time

population standard deviation of time spent in the statement, in milliseconds

rows

total number of rows retrieved or affected by the statement

shared_blks_hit

total number of shared block cache hits by the statement

shared_blks_read

total number of shared blocks read by the statement

shared_blks_dirtied

total number of shared blocks dirtied by the statement

shared_blks_written

total number of shared blocks written by the statement

local_blks_hit

total number of local block cache hits by the statement

local_blks_read

total number of local blocks read by the statement

local_blks_dirtied

total number of local blocks dirtied by the statement

local_blks_written

total number of local blocks written by the statement

temp_blks_read

total number of temp blocks read by the statement

temp_blks_written

total number of temp blocks written by the statement

blk_read_time

total time the statement spent reading blocks, in milliseconds (if track_io_timing is enabled, otherwise zero)

blk_write_time

total time the statement spent writing blocks, in milliseconds (if track_io_timing is enabled, otherwise zero)

單次調用最耗io sql top 5

總最耗io sql top 5

單次調用最耗時 sql top 5

總最耗時 sql top 5

pg_stat_statements是累積的統計，如果要檢視某個時間段的統計，需要打快照，建議參考

<a href="https://github.com/digoal/blog/blob/master/201611/20161123_01.md">《postgresql awr報告(for 阿裡雲apsaradb pgsql)》</a>

使用者也可以定期清理曆史的統計資訊，通過調用如下sql

<a href="https://www.postgresql.org/docs/9.6/static/pgstatstatements.html">https://www.postgresql.org/docs/9.6/static/pgstatstatements.html</a>