PostgreSQL 14数据库监控和日志功能的加强

2022-04-04 00:22:39

查询标识符(query identifier)

查询标识符用于标识一个查询，在扩展中可以被交叉引用。在PostgreSQL 14之前，扩展使用一个算法来计算query_id。可以使用相同的算法来计算query_id，但是扩展都使用了自己的算法来计算query_id。在14版本内核提供了query_id，现在的监控扩展和工具，如pg_stat_activity,explain,pg_stat_statments都可以使用这个query_id,而不再自己重新计算。

指定log_line_prefix之后，可以在csvlog中看到query_id。从用户角度来看，有两个好处：

1.所有的工具和扩展都使用相同的query_id,便于交叉使用query_id。在此之前，所有的工具和扩展都要使用自己的算法计算query_id。

2.所有的工具和扩展都使用内核计算出的query_id，不再自己单独计算，有益于提升性能。

通过参数 compute_query_id开启或关闭query_id的计算，默认值是auto。可以在参数文件中配置，也可以通过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的机制。query_id在日志是非常有用的。为此，要配置参数log_line_prefix。“%Q”增加了query_id的功能。例如：

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,explain就会显示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)

autovacuum和auto-analyze日志增强

版本14增强了auto-vacuum和auto-analyze日志功能。现在可以在日志中看到I/O计时功能。显示了读和写花费了多少时间。

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

连接日志

如果开启了log_connections/log_disconnections选项，就会记录建立连接和断开连接到日志。因此，版本14现在也记录实际的用户名。如果使用了外部验证机制，并在pg_ident.conf定义了映射关系，就很难识别真正的用户名。在版本14之前，只能看到映射名。

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

在PostgreSQL-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

在PostgreSQL-14中

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

码农公寓