642
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
pg_stat_statements是PostgreSQL的核心插件之一。可以在編譯PostgreSQL時安裝，也可以單獨安裝。
編譯時安裝
make world
make install-world
單獨安裝
cd src/contrib/pg_stat_statements/
make; make install
二、加載pg_stat_statements模塊
vi $PGDATA/postgresql.conf
shared_preload_libraries='pg_stat_statements'
如果要跟蹤IO消耗的時間，還需要打開如下參數
track_io_timing = on
設置單條SQL的最長長度，超過被截斷顯示（可選）
track_activity_query_size = 2048
三、配置pg_stat_statements采樣參數
vi $PGDATA/postgresql.conf
pg_stat_statements.max = 10000 # 在pg_stat_statements中最多保留多少條統計信息，通過LRU算法，覆蓋老的記錄。
pg_stat_statements.track = all # all - (所有SQL包括函數內嵌套的SQL), top - 直接執行的SQL(函數內的sql不被跟蹤), none - (不跟蹤)
pg_stat_statements.track_utility = off # 是否跟蹤非DML語句 (例如DDL，DCL)， on表示跟蹤, off表示不跟蹤
pg_stat_statements.save = on # 重啟後是否保留統計信息
重啟數據庫
pg_ctl restart -m fast
四、創建pg_stat_statements extension
在需要查詢TOP SQL的數據庫中，創建extension
create extension pg_stat_statements;
五、分析TOP SQL
pg_stat_statements輸出內容介紹
查詢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 | oid | 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 | bigint | - | Number of times executed |
| total_time | double precision | - | Total time spent in the statement, in milliseconds |
| min_time | double precision | - | Minimum time spent in the statement, in milliseconds |
| max_time | double precision | - | Maximum time spent in the statement, in milliseconds |
| mean_time | double precision | - | Mean time spent in the statement, in milliseconds |
| stddev_time | double precision | - | Population standard deviation of time spent in the statement, in milliseconds |
| rows | bigint | - | Total number of rows retrieved or affected by the statement |
| shared_blks_hit | bigint | - | Total number of shared block cache hits by the statement |
| shared_blks_read | bigint | - | Total number of shared blocks read by the statement |
| shared_blks_dirtied | bigint | - | Total number of shared blocks dirtied by the statement |
| shared_blks_written | bigint | - | Total number of shared blocks written by the statement |
| local_blks_hit | bigint | - | Total number of local block cache hits by the statement |
| local_blks_read | bigint | - | Total number of local blocks read by the statement |
| local_blks_dirtied | bigint | - | Total number of local blocks dirtied by the statement |
| local_blks_written | bigint | - | Total number of local blocks written by the statement |
| temp_blks_read | bigint | - | Total number of temp blocks read by the statement |
| temp_blks_written | bigint | - | Total number of temp blocks written by the statement |
| blk_read_time | double precision | - | Total time the statement spent reading blocks, in milliseconds (if track_io_timing is enabled, otherwise zero) |
| blk_write_time | double precision | - | Total time the statement spent writing blocks, in milliseconds (if track_io_timing is enabled, otherwise zero) |
最耗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
select userid::regrole, dbid, query from pg_stat_statements order by stddev_time desc limit 5;
最耗共享內存 SQL
select userid::regrole, dbid, query from pg_stat_statements order by (shared_blks_hit+shared_blks_dirtied) desc limit 5;
最耗臨時空間 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)》
用戶也可以定期清理曆史的統計信息，通過調用如下SQL
select pg_stat_statements_reset();
參考
https://www.postgresql.org/docs/9.6/static/pgstatstatements.html
最後更新：2017-04-24 21:32:59