PG14 监控和日志功能提升
PostgreSQL 14与2021年9月发布，其中包括很多性能改进和功能增强，包括监控方面的功能提升。下面是一些关键内容。
查询标识符
查询标识符用于标识查询，可以在各个扩展之中交叉使用。PG14之前，扩展需要使用一个算法计算query_id。通常情况下使用同一个算法计算，但任何扩展都可以使用自己的算法。现在PG14提供在内核中计算query_id。现主PG的监控工具比如pg_stat_activity、explain、pg_stat_statments使用这个query_id，不再使用算法进行计算。指定log_line_prefix后，旧会在csvlog中看到query_id。在用户角度，有2个好处：
1) 所有工具/扩展都使用内核中计算的query_id，为工具/扩展交叉使用提供便利。之前的版本需要使用同样的算法计算才能达到这样的功能。
2) 扩展/工具使用这个query_id，无需再重复计算，这是一个性能提升。
PG引入了一个新的GUC控制参数compute_query_id来启用这个特性，默认是开启状态。可以在postgresql.conf中设置也可以使用SET命令。
pg_stat_activity
SET compute_query_id=off;
SELECT datname, query, query_id FROM pg_stat_activity; datname | query | query_id ----------+-----------------------------------------------------------------------+---------- postgres | select datname, query, query_id from pg_stat_activity; | postgres | UPDATE pgbench_branches SET bbalance = bbalance + 2361 WHERE bid = 1; |
SET compute_query_id=on;
SELECT datname, query, query_id FROM pg_stat_activity; datname | query | query_id ----------+-----------------------------------------------------------------------+--------------------- postgres | select datname, query, query_id from pg_stat_activity; | 846165942585941982 postgres | UPDATE pgbench_tellers SET tbalance = tbalance + 3001 WHERE tid = 44; | 3354982309855590749
Log
之前版本中，内核中没有机制计算query_id。在log file中query_id非常有用。需要配置log_line_prefix启用这项功能。query_id中加了“%Q”：
log_line_prefix = 'query_id = [%Q] -> ' query_id = [0] -> LOG: statement: CREATE PROCEDURE ptestx(OUT a int) LANGUAGE SQL AS $$ INSERT INTO cp_test VALUES (1, 'a') $$; query_id = [-6788509697256188685] -> ERROR: return type mismatch in function declared to return record query_id = [-6788509697256188685] -> DETAIL: Function's final statement must be SELECT or INSERT/UPDATE/DELETE RETURNING. query_id = [-6788509697256188685] -> CONTEXT: SQL function "ptestx" query_id = [-6788509697256188685] -> STATEMENT: CREATE PROCEDURE ptestx(OUT a int) LANGUAGE SQL AS $$ INSERT INTO cp_test VALUES (1, 'a') $$;
Explain
如果compute_query_id为true,则EXPLAIN VERBOSE会显示query_id。
SET compute_query_id=off;
EXPLAIN VERBOSE SELECT * FROM foo; QUERY PLAN -------------------------------------------------------------- Seq Scan on public.foo (cost=0.00..15.01 rows=1001 width=4) Output: a (2 rows)
SET compute_query_id=on;
EXPLAIN VERBOSE SELECT * FROM foo; QUERY PLAN -------------------------------------------------------------- Seq Scan on public.foo (cost=0.00..15.01 rows=1001 width=4) Output: a Query Identifier: 3480779799680626233 (3 rows)
自动vacuum和自动analyze 日志改进
PG14改进了auto-vacuum和auto-analyze的logging功能。现在日志中看下IO时间，显示读写花费了多少时间：
automatic vacuum of table "postgres.pg_catalog.pg_depend": index scans: 1 pages: 0 removed, 67 remain, 0 skipped due to pins, 0 skipped frozen tuples: 89 removed, 8873 remain, 0 are dead but not yet removable, oldest xmin: 210871 index scan needed: 2 pages from table (2.99% of total) had 341 dead item identifiers removed index "pg_depend_depender_index": pages: 39 in total, 0 newly deleted, 0 currently deleted, 0 reusable index "pg_depend_reference_index": pages: 41 in total, 0 newly deleted, 0 currently deleted, 0 reusable I/O timings: read: 44.254 ms, write: 0.531 ms avg read rate: 13.191 MB/s, avg write rate: 8.794 MB/s buffer usage: 167 hits, 126 misses, 84 dirtied WAL usage: 85 records, 15 full page images, 78064 bytes system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.07 s
仅track_io_timing启动时，才能显示这样的日志。
连接日志
log_connections/log_disconnections开启时，会记录connection/disconnection到日志中。因此会记录真实用户名。若使用其他认证插件以及pg_ident.conf中映射，定位真实用户名就会很困难。PG14之前，仅能看到映射用户。
pg_ident.conf
# MAPNAME SYSTEM-USERNAME PG-USERNAME pg vagrant postgres
pg_hba.conf
# TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all peer map=pg
PG 14之前
LOG: database system was shut down at 2021-11-19 11:24:30 UTC LOG: database system is ready to accept connections LOG: connection received: host=[local] LOG: connection authorized: user=postgres database=postgres application_name=psql
PG14
LOG: database system is ready to accept connections LOG: connection received: host=[local] LOG: connection authenticated: identity="vagrant" method=peer (/usr/local/pgsql.14/bin/data/pg_hba.conf:89) LOG: connection authorized: user=postgres database=postgres application_name=psql
总结
PG的每个大版本都会有重大改进，PG14也不例外。监控是数据库管理系统关键特性。PG不断升级其功能以改进日志记录和监控功能。通过这些新增的功能，可以对连接有更多了解，可以轻松跟踪查询和观察性能，并确定vacuum在读写中花费多少时间。可以极大版版主更好配置vacuum参数。
原文
https://www.percona.com/blog/postgresql-14-database-monitoring-and-logging-enhancements/
