侧边栏壁纸
博主头像
极客日记 博主等级

行动起来，活在当下

  • 累计撰写 93 篇文章
  • 累计创建 17 个标签
  • 累计收到 1 条评论

目 录CONTENT

文章目录
PostgreSQL

PostgreSQL 学习笔记 - 统计信息说明

Jack.Jia
Jack.Jia
2022-12-08 / 0 评论 / 0 点赞 / 3 阅读 / 0 字

统计信息说明

统计信息主要用于查询优化时的代价估算， 对 SQL 的性能非常重要。 在 PG 数据库中有 2 个进程会收集统计信息：autovacuum 和 postgres: stats collector。

[root@VM-16-4-centos ~]# 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
[root@VM-16-4-centos ~]#

统计信息相关参数

#------------------------------------------------------------------------------
# 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 是否输出不同执行模块中的统计信息。  

负载指标“统计信息”（Monitoring stats）

负载指标由 stats collector 进程来实时收集更新。PostgreSQL 的统计数据收集器是一个支持收集和报告服务器活动信息的子系统。收集器可以计算对磁盘块和单行项中的表和索引的访问次数。它还跟踪每个表中的总行数，以及关于 vacuum 的信息，并分析每个表的操作。同时还可以记录基于 sql 语句执行的代价信息。

数据分布状态描述“统计信息”（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。

统计信息查看

表和索引的行数、块数等统计信息记录在系统表 pg_class 中，其他的统计信息主要收集在系统表 pg_statistic 中（只能由超级用户读取）。

pg_statistic 中的项由 ANALYZE 和 VACUUM ANALYZE 命令更新，并且总是近似值（即使刚刚更新完）。

pg_stats 是 pg_statistic 更易读版的视图，适合手工检查统计信息（所有人都可读取）

其他具体对象的统计信息可以通过如下相关视图查询：

  1. pg_stat_database
  2. pg_stat_all_tables 所有表统计信息
  3. pg_stat_sys_tables 系统表统计信息
  4. pg_stat_user_tables 用户表统计信息
  5. pg_stat_all_indexes
  6. pg_stat_sys_indexes
  7. pg_stat_user_indexes

查看数据库级统计信息

-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 |
 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%，则通常认为应用健康有问题，需要检查哪些异常导致了事务的回滚。

查看表的统计信息

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=#

语句级统计信息

官网说明如下：

https://www.postgresql.org/docs/15/pgstatstatements.html

语句级的统计信息一般可通过 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)

统计信息更新

统计信息正常是会自动收集，当异常的情况下，也可以使用 ANALYZE 手工收集，analyze 命令收集表的统计信息，然后将结果保存在系统表 pg_statistic 中。

autovcuum lancher 进程中会定期的执行 autovcuum ，Analyze 是 autovcuum 其中的一步，会主动被触发。Vacuum 是 Analyze 的超集，Vacuum 包含一系列的清理、表的重建、以及表的统计信息更新，换句话说就是，vacuum 包含但不限于 analyze table 来更新统计信息。

官方命令如下：

https://www.postgresql.org/docs/current/sql-analyze.html

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 [, ...] ) ]

相关说明如下：

  1. verbose：显示处理的进度，以及表的一些统计信息。
  2. table：要分析的表名，如果不指定，则对整个数据库中的所有表作分析。
  3. column：要分析的特定字段的名字默认是分析所有字段。
  4. analyze 命令会在表上加读锁。

对于大表，analyze 只读取表的部分内容做一个随机抽样，不读取表的所有内容。

统计信息只是近似的结果。为了调整所收集的统计信息的准确度，可以增大随机抽样比例，可通过参数 default_statistics_target 来实现，这个参数可以在 session 级别设置，也可以在列级别设置。

https://www.postgresql.org/docs/15/sql-alterstatistics.html

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=#
0
PostgreSQL
版权归属： Jack.Jia
本文链接： https://www.explorexd.com/archives/3cd6196f-1f7b-4fc1-8c92-50fccb872059
许可协议： 本文使用《署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0)》协议授权

评论区