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

查询标识符(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;

?

1 2 3 4 5

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;

?

1 2 3 4 5

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的功能。例如：

?

1	log_line_prefix = 'query_id = [%Q] -> '

?

1 2 3 4 5

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;

?

1 2 3 4 5 6 7

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;

?

1 2 3 4 5 6 7

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计时功能。显示了读和写花费了多少时间。

?

1 2 3 4 5 6 7 8 9 10 11 12 13

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

?

1 2 3

# MAPNAME       SYSTEM-USERNAME         PG-USERNAME ​ pg              vagrant                 postgres

　　

pg_hba.conf

?

1 2 3

# TYPE  DATABASE        USER            ADDRESS                 METHOD # "local" is for Unix domain socket connections only local   all             all                                     peer map=pg

　　

在PostgreSQL-14之前

?

1 2 3 4

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中

?

1 2 3 4

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