標(biāo)簽
PostgreSQL , 大屏指標(biāo) , qps , long query , locks , active , idle in transaction , long idle in transaction , 2PC
背景
最關(guān)鍵的一些數(shù)據(jù)庫健康指標(biāo)，趨勢監(jiān)測。
1 總連接數(shù)
主要看趨勢，直接與業(yè)務(wù)量掛鉤
如果連接數(shù)接近max_connection水位，需要注意。
同時(shí)連接數(shù)應(yīng)與數(shù)據(jù)庫主機(jī)可用內(nèi)存掛鉤，每個(gè)連接保守估計(jì)10MB內(nèi)存開銷（這里還未計(jì)算SYSCACHE,RELCACHE）。
select count(*) from pg_stat_activity ;
演示，打印每秒的總連接數(shù)。
psql
select count(*) from pg_stat_activity ;
\watch 1
2 N秒內(nèi)新建的連接數(shù)
主要看趨勢，直接與業(yè)務(wù)量掛鉤
如果突發(fā)大量連接，可能是新增了業(yè)務(wù)服務(wù)器，或者是性能抖動(dòng)過導(dǎo)致業(yè)務(wù)大量新建連接滿足并發(fā)的請求。
突然連接數(shù)下降，可能原因是業(yè)務(wù)服務(wù)器突然釋放連接，或者業(yè)務(wù)服務(wù)器掛了。
select count(*) from pg_stat_activity where now()-backend_start > '? second';
演示，打印每秒的5秒內(nèi)新建連接數(shù)。
psql
select count(*) from pg_stat_activity where now()-backend_start > '5 second';
\watch 1
3 SQL活躍統(tǒng)計(jì)
1、需要加載pg_stat_statements，如果需要跟蹤IO時(shí)間，需要開啟track_io_timing。
同時(shí)需要注意，由于pg_stat_statements跟蹤的SQL有限，最近未訪問過的SQL的跟蹤信息可能被拋棄。所以統(tǒng)計(jì)并不是非常的精準(zhǔn)。
postgres=# \d pg_stat_statements
View "public.pg_stat_statements"
Column | Type | Collation | Nullable | Default
---------------------+------------------+-----------+----------+---------
userid | oid | | |
dbid | oid | | |
queryid | bigint | | |
query | text | | |
calls | bigint | | |
total_time | double precision | | |
min_time | double precision | | |
max_time | double precision | | |
mean_time | double precision | | |
stddev_time | double precision | | |
rows | bigint | | |
shared_blks_hit | bigint | | |
shared_blks_read | bigint | | |
shared_blks_dirtied | bigint | | |
shared_blks_written | bigint | | |
local_blks_hit | bigint | | |
local_blks_read | bigint | | |
local_blks_dirtied | bigint | | |
local_blks_written | bigint | | |
temp_blks_read | bigint | | |
temp_blks_written | bigint | | |
blk_read_time | double precision | | |
blk_write_time | double precision | | |
3.1 QPS
QPS指標(biāo)來自pg_stat_statements，由于這個(gè)插件有一個(gè)STATEMENT采集上限，可配置，例如最多采集1000條SQL，如果有新的SQL被采集到時(shí)，并且1000已用完，則會(huì)踢掉最老的SQL。所以我們這里統(tǒng)計(jì)的QPS并不是完全精確，不過還好PG內(nèi)部會(huì)自動(dòng)合并SQL，把一些條件替換成變量，這樣即使不使用綁定變量，也能追蹤到很多SQL。
對于業(yè)務(wù)SQL非常繁多并且大多數(shù)都是活躍SQL的場景，可以適當(dāng)調(diào)大pg_stat_statements的track數(shù)，提高精準(zhǔn)度。
除此之外，可以改進(jìn)pg_stat_statements的功能，直接統(tǒng)計(jì)精準(zhǔn)的QPS。
主要看趨勢，直接與業(yè)務(wù)量掛鉤
with
a as (select sum(calls) s, sum(case when ltrim(query,' ') ~* '^select' then calls else 0 end) q from pg_stat_statements),
b as (select sum(calls) s, sum(case when ltrim(query,' ') ~* '^select' then calls else 0 end) q from pg_stat_statements , pg_sleep(1))
select
b.s-a.s, -- QPS
b.q-a.q, -- 讀QPS
b.s-b.q-a.s+a.q -- 寫QPS
from a,b;
如果只想看QPS，使用
with
a as (select sum(calls) s from pg_stat_statements),
b as (select sum(calls) s from pg_stat_statements , pg_sleep(1))
select
b.s-a.s -- QPS
from a,b;
演示，打印每秒的QPS。
psql
with
a as (select sum(calls) s from pg_stat_statements),
b as (select sum(calls) s from pg_stat_statements , pg_sleep(1))
select
b.s-a.s -- QPS
from a,b;
\watch 0.000001
3.2 每秒處理了多少行
每秒處理了多少行，包括寫入，讀取，更新，刪除等操作。
兩次快照相減除以時(shí)間間隔
sum(pg_stat_statements.rows)
3.3 共享緩沖區(qū)：每秒緩存命中、未命中讀
shared_blks_hit | bigint | | |
shared_blks_read | bigint | | |
3.4 共享緩沖區(qū)：每秒產(chǎn)生多少臟頁
shared_blks_dirtied | bigint | | |
3.5 共享緩沖區(qū)：每秒異步write多少臟頁
shared_blks_written | bigint | | |
3.6 進(jìn)程本地緩沖區(qū)：每秒緩存命中、未命中讀
local_blks_hit | bigint | | |
local_blks_read | bigint | | |
3.7 進(jìn)程本地緩沖區(qū)：每秒產(chǎn)生多少臟頁
local_blks_dirtied | bigint | | |
3.8 進(jìn)程本地緩沖區(qū)：每秒異步write多少臟頁
local_blks_written | bigint | | |
3.9 臨時(shí)文件每秒讀
temp_blks_read | bigint | | |
3.10 臨時(shí)文件每秒寫
temp_blks_written | bigint | | |
3.11 兩次快照之間的讀數(shù)據(jù)塊耗時(shí)
blk_read_time | double precision | | |
3.12 兩次快照之間的寫數(shù)據(jù)塊耗時(shí)
blk_write_time | double precision | | |
4 active session
主要看趨勢，直接與業(yè)務(wù)量掛鉤
如果活躍會(huì)話數(shù)長時(shí)間超過CPU核數(shù)時(shí)，說明數(shù)據(jù)庫響應(yīng)變慢了，需要深刻關(guān)注。
select count(*) from pg_stat_activity where state='active';
演示，打印每秒的活躍會(huì)話數(shù)。
psql
select count(*) from pg_stat_activity where state='active';
\watch 1
5 平均RT
活躍會(huì)話/qps = RT(秒)
6 long query
當(dāng)前系統(tǒng)中執(zhí)行時(shí)間超過N秒的SQL有多少條，LONG QUERY與活躍會(huì)話的比例說明當(dāng)前LONG SQL的占比。占比越高，說明該系統(tǒng)可能偏向OLAP，占比越低，說明該系統(tǒng)偏向OLTP業(yè)務(wù)。
select count(*) from pg_stat_activity where state='active' and now()-query_start > interval '? second';
演示，打印每秒系統(tǒng)中執(zhí)行時(shí)間超過5秒的SQL有多少條。
psql
select count(*) from pg_stat_activity where state='active' and now()-query_start > interval '5 second';
\watch 1
7 long transaction
當(dāng)前系統(tǒng)中N秒未結(jié)束的事務(wù)有多少條
select count(*) from pg_stat_activity where now()-xact_start > interval '? second';
演示，打印每秒系統(tǒng)中5秒未結(jié)束的事務(wù)有多少條
psql
select count(*) from pg_stat_activity where now()-xact_start > interval '5 second';
\watch 1
8 idle in transaction
當(dāng)前系統(tǒng)中在事務(wù)中并且處于空閑狀態(tài)的會(huì)話有多少，很多，說明業(yè)務(wù)端的處理可能比較慢，如果結(jié)合鎖等待發(fā)現(xiàn)有大量鎖等待，并且活躍會(huì)話數(shù)有突增，可能需要關(guān)注并排查業(yè)務(wù)邏輯的問題。
select count(*) from pg_stat_activity where state='idle in transaction';
演示，打印每秒系統(tǒng)中在事務(wù)中并且處于空閑狀態(tài)的會(huì)話有多少
psql
select count(*) from pg_stat_activity where state='idle in transaction';
\watch 1
9 long idle in transaction
當(dāng)前系統(tǒng)中，有多少長期（超過N秒）處于空閑的事務(wù)。如果有較多這樣的事務(wù)，說明業(yè)務(wù)端的處理時(shí)間超過N秒的情況非常普遍，應(yīng)該盡快排查業(yè)務(wù)。
比如前端開啟了游標(biāo)，等待用戶的翻頁動(dòng)作，用戶可能開小差了。又比如業(yè)務(wù)上使用了一些交互模式，等用戶的一些輸入等。
這種情況應(yīng)該盡量避免，否則長時(shí)間占用連接資源。
select count(*) from pg_stat_activity where state='idle in transaction' and now()-state_change > interval '? second';
演示，打印每秒系統(tǒng)中在事務(wù)中并且處于空閑狀態(tài)(超過5秒)的會(huì)話有多少
psql
select count(*) from pg_stat_activity where state='idle in transaction' and now()-state_change > interval '5 second';
\watch 1
10 waiting
當(dāng)前系統(tǒng)中，處于等待中的會(huì)話有多少。
如果很多，說明出現(xiàn)了大量的鎖等待，使用末尾文章進(jìn)行排查。
select count(*) from pg_stat_activity where wait_event_type is not null;
演示，打印每秒系統(tǒng)中處于等待中的會(huì)話有多少。
psql
select count(*) from pg_stat_activity where wait_event_type is not null;
\watch 1
11 long waiting
當(dāng)前系統(tǒng)中，等待超過N秒的會(huì)話有多少。
select count(*) from pg_stat_activity where wait_event_type is not null and now()-state_change > interval '? second';
演示，打印每秒系統(tǒng)中等待超過5秒的會(huì)話有多少。
psql
select count(*) from pg_stat_activity where wait_event_type is not null and now()-state_change > interval '5 second';
\watch 1
12 2pc
當(dāng)前系統(tǒng)中，2PC的事務(wù)有多少。如果接近max_prepared_transactions，需要注意。建議調(diào)大max_prepared_transactions，或者排查業(yè)務(wù)是否未及時(shí)提交。
select count(*) from pg_prepared_xacts;
演示，打印每秒系統(tǒng)中未結(jié)束的2PC事務(wù)數(shù)。
psql
select count(*) from pg_prepared_xacts;
\watch 1
13 long 2pc
當(dāng)前系統(tǒng)中，超過N秒未結(jié)束的2PC的事務(wù)有多少。如果很多，需要排查業(yè)務(wù)為什么未及時(shí)提交。
select count(*) from pg_prepared_xacts where now() - prepared > interval '? second';
演示，打印每秒系統(tǒng)中5秒仍未結(jié)束的2PC事務(wù)數(shù)。
psql
select count(*) from pg_prepared_xacts where now() - prepared > interval '5 second';
\watch 1
14 膨脹點(diǎn)監(jiān)測 - 多久以前的垃圾可以被回收
時(shí)間間隔越大，說明越容易導(dǎo)致膨脹。
排查這幾個(gè)方向，長事務(wù)，長SQL，2PC，持有SNAPSHOT的QUERY。必要時(shí)把不合理的老的會(huì)話干掉。
with a as
(select min(xact_start) m from pg_stat_activity where backend_xid is not null or backend_xmin is not null),
b as (select min(prepared) m from pg_prepared_xacts)
select now()-least(a.m,b.m) from a,b;
演示，打印每秒系統(tǒng)中多久以前的垃圾可以被回收
psql
with a as
(select min(xact_start) m from pg_stat_activity where backend_xid is not null or backend_xmin is not null),
b as (select min(prepared) m from pg_prepared_xacts)
select now()-least(a.m,b.m) from a,b;
\watch 1
15 空間
看當(dāng)前占用情況，打快照，看時(shí)間維度空間變化情況。
按庫劃分
postgres=# \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
-----------+----------+-----------+------------+------------+-----------------------+---------+------------+--------------------------------------------
postgres | postgres | SQL_ASCII | en_US.UTF8 | en_US.UTF8 | | 54 GB | pg_default | default administrative connection database
template0 | postgres | SQL_ASCII | en_US.UTF8 | en_US.UTF8 | =c/postgres +| 7489 kB | pg_default | unmodifiable empty database
| | | | | postgres=CTc/postgres | | |
template1 | postgres | SQL_ASCII | en_US.UTF8 | en_US.UTF8 | =c/postgres +| 578 MB | pg_default | default template for new databases
| | | | | postgres=CTc/postgres | | |
test | test | SQL_ASCII | en_US.UTF8 | en_US.UTF8 | | 7489 kB | pg_default |
(4 rows)
按表空間劃分
postgres=# \db+
List of tablespaces
Name | Owner | Location | Access privileges | Options | Size | Description
--------------------+----------+--------------------------------------+-------------------+---------+---------+-------------
dbt2_index1 | postgres | /data02/pg/tbs_tpcc/index1/ts | | | 452 MB |
dbt2_index2 | postgres | /data02/pg/tbs_tpcc/index2/ts | | | 869 MB |
dbt2_pk_customer | postgres | /data02/pg/tbs_tpcc/pk_customer/ts | | | 451 MB |
dbt2_pk_district | postgres | /data02/pg/tbs_tpcc/pk_district/ts | | | 236 kB |
dbt2_pk_item | postgres | /data02/pg/tbs_tpcc/pk_item/ts | | | 2212 kB |
dbt2_pk_new_order | postgres | /data02/pg/tbs_tpcc/pk_new_order/ts | | | 149 MB |
dbt2_pk_order_line | postgres | /data02/pg/tbs_tpcc/pk_order_line/ts | | | 4701 MB |
dbt2_pk_orders | postgres | /data02/pg/tbs_tpcc/pk_orders/ts | | | 490 MB |
dbt2_pk_stock | postgres | /data02/pg/tbs_tpcc/pk_stock/ts | | | 1768 MB |
dbt2_pk_warehouse | postgres | /data02/pg/tbs_tpcc/pk_warehouse/ts | | | 44 kB |
pg_default | postgres | | | | 46 GB |
pg_global | postgres | | | | 573 kB |
(12 rows)
16 數(shù)據(jù)空間
數(shù)據(jù)占用的空間。
17 日志空間
WAL日志占用的空間。
18 備庫發(fā)送延遲
select application_name,client_addr,client_hostname,client_port,state,sync_priority,sync_state,pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn)) from pg_stat_replication;
19 備庫APPLY延遲
select application_name,client_addr,client_hostname,client_port,state,sync_priority,sync_state,pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lag)) from pg_stat_replication;
20 SLOT 延遲
select slot_name, plugin, slot_type, temporary, active, active_pid, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) from pg_replication_slots;
21 歸檔延遲
最后一次歸檔失敗時(shí)間減去最后一次歸檔成功的時(shí)間，求時(shí)間差。
select last_failed_time - last_archived_time from pg_stat_archiver;
22 數(shù)據(jù)庫活動(dòng)信息
以下都可以針對單個(gè)數(shù)據(jù)庫輸出，也可以輸出整個(gè)實(shí)例的統(tǒng)計(jì)。
postgres=# \d pg_stat_database
View "pg_catalog.pg_stat_database"
Column | Type | Collation | Nullable | Default
----------------+--------------------------+-----------+----------+---------
datid | oid | | |
datname | name | | |
numbackends | integer | | |
xact_commit | bigint | | |
xact_rollback | bigint | | |
blks_read | bigint | | |
blks_hit | bigint | | |
tup_returned | bigint | | |
tup_fetched | bigint | | |
tup_inserted | bigint | | |
tup_updated | bigint | | |
tup_deleted | bigint | | |
conflicts | bigint | | |
temp_files | bigint | | |
temp_bytes | bigint | | |
deadlocks | bigint | | |
blk_read_time | double precision | | |
blk_write_time | double precision | | |
stats_reset | timestamp with time zone | | |
22.1 每秒事務(wù)提交數(shù)
多次查詢計(jì)算
select sum(xact_commit) from pg_stat_database; -- pg_stat_get_db_xact_commit 為stable函數(shù)，一個(gè)事務(wù)中兩次調(diào)用之間只執(zhí)行一次，所以需要外部多次執(zhí)行。
22.2 每秒事務(wù)回滾數(shù)
select sum(xact_rollback) from pg_stat_database;
22.3 每秒全表掃描記錄數(shù)
select sum(tup_returned) from pg_stat_database;
22.4 每秒索引掃描回表記錄數(shù)
select sum(tup_fetched) from pg_stat_database;
22.5 每秒插入記錄數(shù)
select sum(tup_inserted) from pg_stat_database;
22.6 每秒更新記錄數(shù)
select sum(tup_updated) from pg_stat_database;
22.7 每秒刪除記錄數(shù)
select sum(tup_deleted) from pg_stat_database;
22.8 備庫查詢沖突數(shù)
select sum(conflicts) from pg_stat_database;
22.9 死鎖數(shù)
select sum(deadlocks) from pg_stat_database;
參考
《PostgreSQL 鎖等待監(jiān)控 珍藏級(jí)SQL - 誰堵塞了誰》
《PostgreSQL snapshot too old補(bǔ)丁, 防止數(shù)據(jù)庫膨脹》
《PostgreSQL垃圾回收代碼分析 - why postgresql cann't reclaim tuple is HEAPTUPLE_RECENTLY_DEAD》
《PostgreSQL relcache在長連接應(yīng)用中的內(nèi)存霸占"坑"》
《PostgreSQL pg_stat_ pg_statio_ 統(tǒng)計(jì)信息(scan,read,fetch,hit)源碼解讀》
|
