CNDBA社区
1 统计信息说明
统计信息主要用于查询优化时的代价估算， 对SQL 的性能非常重要。 在PG 数据库中有2个进程会收集统计信息：autovacuum 和 postgres: stats collector。
[dave@www.cndba.cn ~]# ps -ef|grep post
root 1574 1 0 10:40 ? 00:00:00 /usr/libexec/postfix/master -w
postfix 1580 1574 0 10:40 ? 00:00:00 qmgr -l -t unix -u
root 2184 1945 0 10:48 pts/0 00:00:00 su - postgres
postgres 2185 2184 0 10:48 pts/0 00:00:00 -bash
postgres 2352 1 0 10:53 ? 00:00:01 /usr/pgsql-14/bin/postgres -D /var/lib/pgsql/14/data/
postgres 2353 2352 0 10:53 ? 00:00:00 postgres: logger
postgres 2355 2352 0 10:53 ? 00:00:00 postgres: checkpointer
postgres 2356 2352 0 10:53 ? 00:00:00 postgres: background writer
postgres 2357 2352 0 10:53 ? 00:00:00 postgres: walwriter
postgres 2358 2352 0 10:53 ? 00:00:01 postgres: autovacuum launcher
postgres 2359 2352 0 10:53 ? 00:00:00 postgres: archiver
postgres 2360 2352 0 10:53 ? 00:00:04 postgres: stats collector
postgres 2361 2352 0 10:53 ? 00:00:00 postgres: logical replication launcher
postfix 8567 1574 0 17:09 ? 00:00:00 pickup -l -t unix -u
root 9690 9624 0 18:05 pts/1 00:00:00 grep --color=auto post
[dave@www.cndba.cn ~]#
1.1 统计信息相关参数
#------------------------------------------------------------------------------
# STATISTICS
#------------------------------------------------------------------------------
# - Query and Index Statistics Collector -
#track_activities = on --是否允许跟踪每个session正在执行的SQL命令的信息和命令开始的时间。可在pg_stat_activity中看到。
#track_activity_query_size = 1024 # (change requires restart) --在pg_stat_activity中query字段最多显示多少字节。
#track_counts = on --控制是否收集表和索引上访问的统计信息。
#track_io_timing = off --是否允许统计IO调用时间。开启后可在pg_stat_database和pg_stat_statements中看到。9.2之后的新加参数
#track_wal_io_timing = off --是否收集函数调用次数和时间的统计信息。
#track_functions = none # none, pl, all
#stats_temp_directory = 'pg_stat_tmp'
可以使用以下4个boolean类型的参数来控制是否输出SQL执行过程的统计信息到日志中：
# - Monitoring -
#compute_query_id = auto
#log_statement_stats = off
#log_parser_stats = off
#log_planner_stats = off
#log_executor_stats = off
参数log_statement_stats控制是否输出所有SQL语句的统计信息，其他的参数控制每个SQL是否输出不同执行模块中的统计信息。
1.2 负载指标“统计信息”（Monitoring stats）
负载指标由stats collector进程来实时收集更新。PostgreSQL的统计数据收集器是一个支持收集和报告服务器活动信息的子系统。收集器可以计算对磁盘块和单行项中的表和索引的访问次数。它还跟踪每个表中的总行数，以及关于vacuum的信息，并分析每个表的操作。同时还可以记录基于sql语句执行的代价信息。
1.3 数据分布状态描述“统计信息”（Data distribution stats）
描述数据分析状态的信息有两种生成方式：
1．后台进程autovacuum lancher会在特定的情况下触发统计信息的更新。
2．手动执行analyze table。
基于postgresql的MVCC机制生成的“非活动数据”，更新可见性映射，冻结事务处理（保护老旧数据不会由于事务ID回卷或多事务ID回卷而丢失），也是由autovacuum 进程来清理，同时，还负责XID的清理工作。
有两种VACUUM的变体：标准VACUUM和VACUUM FULL。
1． 标准形式的VACUUM可以和生产数据库操作并行运行（SELECT、INSERT、UPDATE和DELETE等命令将继续正常工作，但在清理期间你无法使用ALTER TABLE等命令来更新表的定义）。
2． VACUUM FULL可以收回更多磁盘空间但是运行起来更慢，且vacuum full不会有后台进程主从触发（只能手动执行）。
另外，VACUUM FULL类似于表的重建或者说碎片整理，同时需要一个大小相当于原始表的额外空间。要求在其工作的表上得到一个排他锁，因此无法和对此表的其他使用并行。因此，通常管理员应该努力使用标准VACUUM并且避免VACUUM FULL。
2 统计信息查看
表和索引的行数、块数等统计信息记录在系统表pg_class中，其他的统计信息主要收集在系统表pg_statistic中（只能由超级用户读取）。
pg_statistic中的项由ANALYZE和VACUUM ANALYZE命令更新，并且总是近似值（即使刚刚更新完）。
pg_stats是pg_statistic更易读版的视图，适合手工检查统计信息（所有人都可读取）
其他具体对象的统计信息可以通过如下相关视图查询：
- pg_stat_database
- pg_stat_all_tables 所有表统计信息
- pg_stat_sys_tables 系统表统计信息
- pg_stat_user_tables 用户表统计信息
- pg_stat_all_indexes
- pg_stat_sys_indexes
- pg_stat_user_indexes
2.1 查看数据库级统计信息
-bash-4.2$ psql -h localhost -U postgres
psql (14.6)
Type "help" for help.
postgres=# /l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
cndba | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
dave | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
ustc | cndba | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(6 rows)
postgres=# /x on
Expanded display is on.
postgres=# select * from pg_stat_database where datname='cndba';
-[ RECORD 1 ]------------+------------------------------
datid | 16384
datname | cndba
numbackends | 0
xact_commit | 926
xact_rollback | 0
blks_read | 370
blks_hit | 55959
tup_returned | 644266
tup_fetched | 14933
tup_inserted | 0
tup_updated | 0
tup_deleted | 0
conflicts | 0
temp_files | 0
temp_bytes | 0
deadlocks | 0
checksum_failures |
checksum_last_failure |
blk_read_time | 0
blk_write_time | 0
session_time | 10660559.085
active_time | 202.486
idle_in_transaction_time | 0
sessions | 7
sessions_abandoned | 0
sessions_fatal | 0
sessions_killed | 0
stats_reset | 2022-12-13 10:54:45.448464+08
postgres=#
具体列的说明可以参考官方手册，这里不再描述。
这里查看另外2个指标：
postgres=# select blks_hit::float/(blks_hit+blks_read) from pg_stat_database where datname='cndba';
?column?
--------------------
0.9934890105055695
(1 row)
postgres=# select xact_commit::float/(xact_commit+xact_rollback) from pg_stat_database where datname='cndba';
?column?
----------
1
(1 row)
统计得出单个数据库整体的IO命中率，如果命中率较低（低于99%），则通常需要加大shared_buffers；如果事务提交率低于99.9%，则通常认为应用健康有问题，需要检查哪些异常导致了事务的回滚。
2.2 查看表的统计信息
cndba=# /x on
Expanded display is on.
cndba=# 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='film';
-[ RECORD 1 ]-+-------
relname | film
schemaname | public
n_tup_ins | 0
n_tup_upd | 0
n_tup_del | 0
n_tup_hot_upd | 0
last_vacuum |
last_analyze |
vacuum_count | 0
cndba=# analyze film;
ANALYZE
cndba=# 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='film';
-[ RECORD 1 ]-+------------------------------
relname | film
schemaname | public
n_tup_ins | 0
n_tup_upd | 0
n_tup_del | 0
n_tup_hot_upd | 0
last_vacuum |
last_analyze | 2022-12-13 18:25:19.104838+08
vacuum_count | 0
cndba=#
2.3 语句级统计信息
官网说明如下：
语句级的统计信息一般可通过pg_stat_statements,postgres日志，auto_explain来获取到，开启pg_stat_statements需要先设置参数shared_preload_libraries=’pg_stat_statements’;
在postgres.auto.conf中添加参数：
# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
compute_query_id = on
pg_stat_statements.max = 10000
pg_stat_statements.track = all
重启生效：
-bash-4.2$ ps -ef|grep pg
postgres 2352 1 0 10:53 ? 00:00:01 /usr/pgsql-14/bin/postgres -D /var/lib/pgsql/14/data/
postgres 10615 10503 0 18:47 pts/2 00:00:00 grep --color=auto pg
-bash-4.2$ pg_ctl restart -D /var/lib/pgsql/14/data/
waiting for server to shut down.... done
server stopped
waiting for server to start....2022-12-13 18:47:29.939 CST [10620] LOG: redirecting log output to logging collector process
2022-12-13 18:47:29.939 CST [10620] HINT: Future log output will appear in directory "pg_log".
done
server started
-bash-4.2$
cndba=# select name,setting from pg_settings where name like 'pg_stat_state%';
-[ RECORD 1 ]------------------------------
name | pg_stat_statements.max
setting | 10000
-[ RECORD 2 ]------------------------------
name | pg_stat_statements.save
setting | on
-[ RECORD 3 ]------------------------------
name | pg_stat_statements.track
setting | all
-[ RECORD 4 ]------------------------------
name | pg_stat_statements.track_planning
setting | off
-[ RECORD 5 ]------------------------------
name | pg_stat_statements.track_utility
setting | on
cndba=# create extension pg_stat_statements;
CREATE EXTENSION
查看top 5：
select
query,
calls,
total_exec_time,
rows,
100.0 * shared_blks_hit,
nullif(shared_blks_hit + shared_blks_read, 0) as hit_percent
from
pg_stat_statements
order by
total_exec_time desc
limit 5;
查看具体事务：
cndba=# select queryid,min_plan_time,max_plan_time,mean_plan_time,calls from pg_stat_statements where queryid=9022136177324309016;
queryid | min_plan_time | max_plan_time | mean_plan_time | calls
---------------------+---------------+---------------+----------------+-------
9022136177324309016 | 0 | 0 | 0 | 146
(1 row)
3 统计信息更新
统计信息正常是会自动收集，当异常的情况下，也可以使用ANALYZE手工收集，analyze命令收集表的统计信息，然后将结果保存在系统表pg_statistic中。
autovcuum lancher进程中会定期的执行autovcuum ，Analyze是autovcuum其中的一步，会主动被触发。Vacuum是Analyze的超集，Vacuum包含一系列的清理、表的重建、以及表的统计信息更新，换句话说就是，vacuum包含但不限于analyze table来更新统计信息。
官方命令如下：
ANALYZE [ ( option [, ...] ) ] [ table_and_columns [, ...] ]
ANALYZE [ VERBOSE ] [ table_and_columns [, ...] ]
where option can be one of:
VERBOSE [ boolean ]
SKIP_LOCKED [ boolean ]
and table_and_columns is:
table_name [ ( column_name [, ...] ) ]
相关说明如下：
- verbose：显示处理的进度，以及表的一些统计信息。
- table：要分析的表名，如果不指定，则对整个数据库中的所有表作分析。
- column：要分析的特定字段的名字默认是分析所有字段。
- analyze命令会在表上加读锁。
对于大表，analyze只读取表的部分内容做一个随机抽样，不读取表的所有内容。
统计信息只是近似的结果。为了调整所收集的统计信息的准确度，可以增大随机抽样比例，可通过参数default_statistics_target来实现，这个参数可以在session级别设置，也可以在列级别设置。
set default_statistics_target to xxx;
alter table tab_name alter column col_name set statistics xxx;
这里是一个target 是一个整数值，取值范围是0到1000，默认为100，即表示10%，当设置为-1时表示100%。
cndba=# analyze film;
ANALYZE
cndba=# 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='film';
-[ RECORD 1 ]-+------------------------------
relname | film
schemaname | public
n_tup_ins | 0
n_tup_upd | 0
n_tup_del | 0
n_tup_hot_upd | 0
last_vacuum |
last_analyze | 2022-12-13 18:25:19.104838+08
vacuum_count | 0
cndba=#
cndba=# alter table film alter column film_id set statistics 1000;
ALTER TABLE
cndba=# analyze film;
ANALYZE
cndba=# /x
Expanded display is on.
cndba=# 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='film';
-[ RECORD 1 ]-+------------------------------
relname | film
schemaname | public
n_tup_ins | 0
n_tup_upd | 0
n_tup_del | 0
n_tup_hot_upd | 0
last_vacuum |
last_analyze | 2022-12-13 19:19:56.568084+08
vacuum_count | 0
cndba=#
版权声明：本文为博主原创文章，未经博主允许不得转载。
