PG统计信息查看
PG运行过程中会自动收集大量的统计信息，统计信息表名以pg_stat和pg_statio开头
-
数据库级统计信息
postgres@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 | | |
checksum_failures | bigint | | |
checksum_last_failure | timestamp with time zone | | |
blk_read_time | double precision | | |
blk_write_time | double precision | | |
stats_reset | timestamp with time zone | | |
postgres@postgres:\x on
Expanded display is on.
postgres@postgres:select * from pg_stat_database where datname='findb';
-[ RECORD 1 ]---------+------------------------------
datid | 16391 --数据库OID/0表示shared
datname | findb --数据库名/null表示shared
numbackends | 2 --连接到该库的进程数量（本视图中唯一表示当前状态的列）
xact_commit | 3572 --事务提交数
xact_rollback | 34 --事务回滚数
blks_read | 891832 --读取磁盘块的次数
blks_hit | 26027267 --读取磁盘命中的次数（已经在cache中了）
tup_returned | 90197839 --读取返回的行数
tup_fetched | 73666 --通过fetch返回的行数
tup_inserted | 20000363 --插入的行数
tup_updated | 42 --更新的行数
tup_deleted | 25 --删除的行数
conflicts | 0 --查询和恢复冲突的次数（只在备库发生）
temp_files | 14 --创建的临时文件总数量（hash/sort）
temp_bytes | 356506368 --临时文件大小
deadlocks | 0 --死锁数
checksum_failures | --page checksum失败的次数
checksum_last_failure | --最后一次检测到checksum失败的时间
blk_read_time | 0 --读取数据文件块消耗的时间（单位毫秒）
blk_write_time | 0 --写数据文件块消耗的时间（单位毫秒）
stats_reset | 2020-12-18 12:14:09.546879+08 --从此时间以来的统计数据
postgres@postgres:\x off
Expanded display is off.
postgres@postgres:select blks_hit::float/(blks_hit+blks_read) from pg_stat_database where datname='findb';
?column?
--------------------
0.9668727131602807 --IO缓存缓存命中率
(1 row)
postgres@postgres:select xact_commit::float/(xact_commit+xact_rollback) from pg_stat_database where datname='findb';
?column?
--------------------
0.9900564364418167 --事务提交率
(1 row)
统计得出单个数据库整体的IO命中率，如果命中率较低（低于99%），则通常需要加大shared_buffers；如果事务提交率低于99.9%，则通常认为应用健康有问题，需要检查哪些异常导致了事务的回滚
-
表级的统计信息
postgres@findb:\d pg_stat_user_tables
View "pg_catalog.pg_stat_user_tables"
Column | Type |
---------------------+--------------------------+
relid | oid --relation id
schemaname | name --schemaname
relname | name --relation name
seq_scan | bigint --该表上seq_scan的次数
seq_tup_read | bigint --该表上seq_scan读取的行数
idx_scan | bigint --该表上index_scan的次数
idx_tup_fetch | bigint --该表上通过index获取的行数
n_tup_ins | bigint --插入的行数
n_tup_upd | bigint --更新的行数（包括HOT）
n_tup_del | bigint --删除的行数
n_tup_hot_upd | bigint --HOT更新的行数
n_live_tup | bigint --评估的当前正常元组数
n_dead_tup | bigint --评估的死元组数
n_mod_since_analyze | bigint --评估的自上次表分析以来发生变化的行数
last_vacuum | timestamp with time zone --最近一次vacuum的时间
last_autovacuum | timestamp with time zone --最近一次autovacuum的时间
last_analyze | timestamp with time zone --最近一次表分析的时间
last_autoanalyze | timestamp with time zone --最近一次自动表分析时间
vacuum_count | bigint --手动执行vacuum的次数（不包括vacuum full）
autovacuum_count | bigint --autovacuum进程自动vacuum该表的次数
analyze_count | bigint --表分析的次数
autoanalyze_count | bigint --自动分析的次数
postgres@findb:select relname,schemaname,n_tup_ins,n_tup_upd,n_tup_del,n_tup_hot_upd,last_vacuum,last_analyze,vacuum_count from pg_stat_user_tables where relname='score';
relname | schemaname | n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd | last_vacuum | last_analyze | vacuum_count
---------+------------+-----------+-----------+-----------+---------------+-------------+--------------+--------------
score | public | 0 | 6 | 0 | 6 | | | 0
(1 row)
postgres@findb:analyze score;
ANALYZE
postgres@findb:select relname,schemaname,n_tup_ins,n_tup_upd,n_tup_del,n_tup_hot_upd,last_vacuum,last_analyze,vacuum_count from pg_stat_user_tables where relname='score';
relname | schemaname | n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd | last_vacuum | last_analyze | vacuum_count
---------+------------+-----------+-----------+-----------+---------------+-------------+-------------------------------+--------------
score | public | 0 | 6 | 0 | 6 | | 2020-12-19 16:11:22.613461+08 | 0
(1 row)
postgres@findb:vacuum score;
VACUUM
postgres@findb:select relname,schemaname,n_tup_ins,n_tup_upd,n_tup_del,n_tup_hot_upd,last_vacuum,last_analyze,vacuum_count from pg_stat_user_tables where relname='score';
relname | schemaname | n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd | last_vacuum | last_analyze | vacuum_count
---------+------------+-----------+-----------+-----------+---------------+-------------------------------+-------------------------------+--------------
score | public | 0 | 6 | 0 | 6 | 2020-12-19 16:11:37.721289+08 | 2020-12-19 16:11:22.613461+08 | 1
(1 row)
postgres@findb:select seq_scan,seq_tup_read from pg_stat_user_tables where relname='score';
seq_scan | seq_tup_read
----------+--------------
33 | 297
(1 row)
postgres@findb:select count(*) from score;
count
-------
9
(1 row)
postgres@findb:select seq_scan,seq_tup_read from pg_stat_user_tables where relname='score';
seq_scan | seq_tup_read
----------+--------------
34 | 306
(1 row)
--index_scan rate:
postgres@findb:select idx_scan::float/(idx_scan+seq_scan) from pg_stat_all_tables where relname='score';
?column?
---------------------
0.17073170731707318
(1 row)
-
语句级统计信息
语句级的统计信息一般可通过pg_stat_statements,postgres日志，auto_explain来获取到，开启pg_stat_statements需要先设置参数shared_preload_libraries='pg_stat_statements';
postgres@findb:show shared_preload_libraries;
shared_preload_libraries
--------------------------
(1 row)
在postgres.auto.conf中添加参数：
shared_preload_libraries='pg_stat_statements';
pg_stat_statements.track=all
重启生效：
postgres@postgres:select name,setting from pg_settings where name like 'pg_stat_state%';
name | setting
----------------------------------+---------
pg_stat_statements.max | 5000
pg_stat_statements.save | on
pg_stat_statements.track | all
pg_stat_statements.track_utility | on
(4 rows)
postgres@postgres:create extension pg_stat_statements;
CREATE EXTENSION
postgres@postgres:\d pg_stat_statements
View "public.pg_stat_statements"
Column | Type
---------------------+------------------
userid | oid --user_oid
dbid | oid --database_oid
queryid | bigint --语句的ID(hash_code)
query | text --语句文本
calls | bigint --执行次数
total_time | double precision --总消耗时间, ms
min_time | double precision --最小消耗时间, ms
max_time | double precision --最大消耗时间, ms
mean_time | double precision --平均消耗时间, ms
stddev_time | double precision --标准偏差时间, ms
rows | bigint --获取或影响到的行数
shared_blks_hit | bigint --cache命中块总数
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 --总的读块时间，ms，如果track_io_timing为off则为0
blk_write_time | double precision --总的写块时间，ms，如果track_io_timing为off则为0
--解释:Shared blocks contain data from regular tables and indexes; local blocks contain data from temporary tables and indexes;
-- while temp blocks contain short-term working data used in sorts, hashes, Materialize plan nodes, and similar cases.
postgres@postgres:select queryid,min_time,max_time,mean_time,calls from pg_stat_statements where queryid=3395971566626963923;
queryid | min_time | max_time | mean_time | calls
---------------------+----------+----------+---------------------+-------
3395971566626963923 | 0.006312 | 1.547809 | 0.07749945454545452 | 22
(1 row)
