PostgreSQL PG15 新功能 PG_WALINSPECT

开头还是介绍一下群，如果感兴趣PolarDB ,MongoDB ,MySQL ,PostgreSQL ,Redis ，Oracle ,Oceanbase 等有问题，有需求都可以加群群内有各大数据库行业大咖，CTO，可以解决你的问题。加群请加微信号 liuaustin3 （共1250人左右 1 + 2 + 3 +4）新人会进入3群(即将关闭自由申请) 默认会进入4群

每天感悟

公平很多人一辈子追求，期望，奢望，可惜了，公平只存在于公众可以看到的地方，然而实际上你生活的世界和非洲大草原上的生存的原理本质是相同的。

PostgreSQL 在PG15 版本之前如果想了解wal 日志中的信息，只能使用上期提到的工具去查看，但从PG15这个版本，查看 wal 日志的内容的方式变化了可以在数据库内部进行查看。作者名为 Bharath Rupireddy

pg_walinspect 这个模块提供了SQL 方面的函数允许你可以探究 write-ahead log 里面的内容，在一个正在运行的PostgreSQL数据库中，实际上功能和我们熟知的pg_waldump功能是类似的，但是在数据内部运行比一个外部的命令给DB人员的在一些情况下，可操作性性要更高。


CREATE EXTENSION pg_walinspect;

这里pg_walinspect函数在PG16 有增强，添加了如下的函数

 
function pg_get_wal_block_info() added (commit c31cf1c0, initially as pg_get_wal_fpi_info() but renamed and expanded in commit 9ecb134a)
functions pg_get_wal_records_info(), pg_get_wal_stats() and pg_get_wal_block_info() accept an LSN value higher than the current LSN (commit 5c1b6628)
functions pg_get_wal_records_info_till_end_of_wal() and pg_get_wal_stats_till_end_of_wal() removed (commit 5c1b6628)

我们通过下面的实验来快速了解pg_walinspect的工作，

 
postgres=# select now(),pg_current_wal_lsn();
elect now(),pg_current_wal_lsn();
             now              | pg_current_wal_lsn 
------------------------------+--------------------
 2023-08-11 08:08:16.79274-04 | 0/4552810
(1 row)
 
postgres=# 
postgres=# 
postgres=# create database test;
CREATE DATABASE
postgres=# create table  test (id int primary key, name varchar(200));
CREATE TABLE
postgres=# insert into test (id,name) values (1,'Austin');
INSERT 0 1
postgres=# insert into test (id,name) values (2,'Simon');
INSERT 0 1
postgres=# 
postgres=# 
postgres=# create index idx_test on test (name);
CREATE INDEX
postgres=# 
postgres=# select now(),pg_current_wal_lsn();
              now              | pg_current_wal_lsn 
-------------------------------+--------------------
 2023-08-11 08:08:16.896122-04 | 0/498AE38
(1 row)

这里我们在操作前获得事务的LSN号，同时在任务结束后，获得结束后的事务号，方便后面我们演示。

首先我们先用第一个函数 pg_get_wal_records() 通过这个函数可以查看系统中的一段日志的内容

 
postgres=# select count(*) from pg_get_wal_records_info('0/4552810', '0/498AE38');
 count 
-------
  1318
(1 row)

这一段产生1318 个记录。

 
postgres=# select count(*) 
postgres-# from pg_get_wal_records_info('0/4552810', '0/498AE38') where record_type <> 'FPI';
-[ RECORD 1 ]
count | 394

而我们排除了FPI 的日志信息后，我们剩下的日志信息只有394 ，实际上其他的日志信息只占整体日志信息的29%，

通过这样的方法可以PG_WAL中的日志大部分信息是不是 FULL PAGE

实际上在这段

实际上在这段里面日志里面我们根据resource_manager 来区分记录的类型，这里主要有 storage , database , btree ,heap , Transaction , heap2, Relmap, Standby , xlog 等，同时记录的类型，有以下集中

 
postgres=# select distinct record_type from pg_get_wal_records_info('0/4552810', '0/498AE38');
-[ RECORD 1 ]---------------
record_type | INSERT
-[ RECORD 2 ]---------------
record_type | NEWROOT
-[ RECORD 3 ]---------------
record_type | CREATE_WAL_LOG
-[ RECORD 4 ]---------------
record_type | MULTI_INSERT
-[ RECORD 5 ]---------------
record_type | INPLACE
-[ RECORD 6 ]---------------
record_type | UPDATE
-[ RECORD 7 ]---------------
record_type | FPI
-[ RECORD 8 ]---------------
record_type | LOCK
-[ RECORD 9 ]---------------
record_type | CREATE
-[ RECORD 10 ]--------------
record_type | RUNNING_XACTS
-[ RECORD 11 ]--------------
record_type | COMMIT
-[ RECORD 12 ]--------------
record_type | INSERT+INIT
-[ RECORD 13 ]--------------
record_type | INSERT_LEAF
 
postgres=# SELECT * FROM pg_get_wal_stats('0/4552810', '0/498AE38');
-[ RECORD 1 ]----------------+----------------------
resource_manager/record_type | XLOG
count                        | 924
count_percentage             | 70.10622154779969
record_size                  | 45276
record_size_percentage       | 67.49552772808586
fpi_size                     | 4216068
fpi_size_percentage          | 97.18706086725605
combined_size                | 4261344
combined_size_percentage     | 96.73493181657214
-[ RECORD 2 ]----------------+----------------------
resource_manager/record_type | Transaction
count                        | 5
count_percentage             | 0.37936267071320184
record_size                  | 1085
record_size_percentage       | 1.6174716756112104
fpi_size                     | 0
fpi_size_percentage          | 0
combined_size                | 1085
combined_size_percentage     | 0.02463011693516899
-[ RECORD 3 ]----------------+----------------------
resource_manager/record_type | Storage
count                        | 299
count_percentage             | 22.685887708649467
record_size                  | 12558
record_size_percentage       | 18.72093023255814
fpi_size                     | 0
fpi_size_percentage          | 0
combined_size                | 12558
combined_size_percentage     | 0.2850737405270527
-[ RECORD 4 ]----------------+----------------------
resource_manager/record_type | CLOG
count                        | 0
count_percentage             | 0
record_size                  | 0
record_size_percentage       | 0
fpi_size                     | 0
fpi_size_percentage          | 0
combined_size                | 0
combined_size_percentage     | 0
-[ RECORD 5 ]----------------+----------------------
resource_manager/record_type | Database
count                        | 1
count_percentage             | 0.07587253414264036
record_size                  | 34
record_size_percentage       | 0.05068574836016696
fpi_size                     | 0
fpi_size_percentage          | 0
combined_size                | 34
combined_size_percentage     | 0.0007718193325306412
-[ RECORD 6 ]----------------+----------------------
resource_manager/record_type | Tablespace
count                        | 0
count_percentage             | 0
record_size                  | 0
record_size_percentage       | 0
fpi_size                     | 0
fpi_size_percentage          | 0
combined_size                | 0
combined_size_percentage     | 0
-[ RECORD 7 ]----------------+----------------------
resource_manager/record_type | MultiXact
count                        | 0
count_percentage             | 0
record_size                  | 0
record_size_percentage       | 0
fpi_size                     | 0
fpi_size_percentage          | 0
combined_size                | 0
combined_size_percentage     | 0
-[ RECORD 8 ]----------------+----------------------
resource_manager/record_type | RelMap
count                        | 1
count_percentage             | 0.07587253414264036
record_size                  | 553
record_size_percentage       | 0.8243887895050686
fpi_size                     | 0
Cancel request sent

通过这个功能的另一个函数 pg_get_wal_stats ，可以通过这个功能完全，了解这一段实际的日志中的日志的占比，我们可以看到FPI ， XLOG 里面FPI 占比70%。

 
 select * from pg_get_wal_stats('0/4552810', '0/498AE38', true) order by count_percentage desc;

最后为什么会产生那么多FPI， full page image，主要有以下的原因，数据库页面记录在wal日志中的原因，FPI 记录包含整个页面的内容，包括数据和元数据信息，每一个被修改的页面均会产生一个FPI记录，这些FPI记录会写到WAL 日志中，当进行事务性操作是，会对事务牵扯的需要操作的多个页面进行操作被修改的页面都需要有对应的FPI 记录，所以WAL日志中占据最大存储量的是FPI 。换言之，你的系统做的数据变动越多，牵扯的页面数量越多，则产生的FPI 会越多，最终就是你的WAL 日志会较大。

当然如果你想获得更多关于数据库操作的一些内部构造知识，可以通过下面的方式来初步获取，比如日志中一段时间，频繁操作OID，你可以把OID 放到下面的SQL中，来查看到底在这段时间，系统操作了什么。

 
SELECT
 relname,
 CASE 
  when relkind = 'r' then 'tab'
  when relkind = 'i' then 'idx'
  when relkind = 'S' then 'seq'
  when relkind = 't' then 'toast'
  when relkind = 'v' then 'view'
  when relkind = 'm' then 'matview'
  when relkind = 'c' then 'composite'
  when relkind = 'f' then 'F tab'
  when relkind = 'p' then 'part tab'
  when relkind = 'I' then 'part idx'
 END as object_type
   FROM
  pg_class
 WHERE
  oid IN ('oid');
 select * from pg_get_wal_stats('0/4552810', '0/498AE38', true) order by count_percentage desc;
postgres-# 
oid                  relfilenode          relhassubclass       relkind              reloftype            relpersistence       reltoastrelid
relacl               relforcerowsecurity  relhastriggers       relminmxid           reloptions           relreplident         reltuples
relallvisible        relfrozenxid         relispartition       relname              relowner             relrewrite           reltype
relam                relhasindex          relispopulated       relnamespace         relpages             relrowsecurity       
relchecks            relhasrules          relisshared          relnatts             relpartbound         reltablespace        
postgres-# oid IN ('1663','16394','2619','1247');
-[ RECORD 1 ]-------------
relname     | pg_statistic
object_type | tab
-[ RECORD 2 ]-------------
relname     | pg_type
object_type | tab
postgres-# 
oid                  relfilenode          relhassubclass       relkind              reloftype            relpersistence       reltoastrelid
relacl               relforcerowsecurity  relhastriggers       relminmxid           reloptions           relreplident         reltuples
relallvisible        relfrozenxid         relispartition       relname              relowner             relrewrite           reltype
relam                relhasindex          relispopulated       relnamespace         relpages             relrowsecurity       
relchecks            relhasrules          relisshared          relnatts             relpartbound         reltablespace        
postgres-# oid IN ('1663','16394','2619','1247');
-[ RECORD 1 ]-------------
relname     | pg_statistic
object_type | tab
-[ RECORD 2 ]-------------
relname     | pg_type
object_type | tab

	function pg_get_wal_block_info() added (commit c31cf1c0, initially as pg_get_wal_fpi_info() but renamed and expanded in commit 9ecb134a)
	functions pg_get_wal_records_info(), pg_get_wal_stats() and pg_get_wal_block_info() accept an LSN value higher than the current LSN (commit 5c1b6628)
	functions pg_get_wal_records_info_till_end_of_wal() and pg_get_wal_stats_till_end_of_wal() removed (commit 5c1b6628)

	postgres=# select now(),pg_current_wal_lsn();
	elect now(),pg_current_wal_lsn();
	now \| pg_current_wal_lsn
	------------------------------+--------------------
	2023-08-11 08:08:16.79274-04 \| 0/4552810
	(1 row)

	postgres=#
	postgres=#
	postgres=# create database test;
	CREATE DATABASE
	postgres=# create table test (id int primary key, name varchar(200));
	CREATE TABLE
	postgres=# insert into test (id,name) values (1,'Austin');
	INSERT 0 1
	postgres=# insert into test (id,name) values (2,'Simon');
	INSERT 0 1
	postgres=#
	postgres=#
	postgres=# create index idx_test on test (name);
	CREATE INDEX
	postgres=#
	postgres=# select now(),pg_current_wal_lsn();
	now \| pg_current_wal_lsn
	-------------------------------+--------------------
	2023-08-11 08:08:16.896122-04 \| 0/498AE38
	(1 row)

	postgres=# select count(*) from pg_get_wal_records_info('0/4552810', '0/498AE38');
	count
	-------
	1318
	(1 row)

	postgres=# select count(*)
	postgres-# from pg_get_wal_records_info('0/4552810', '0/498AE38') where record_type <> 'FPI';
	-[ RECORD 1 ]
	count \| 394

	postgres=# select distinct record_type from pg_get_wal_records_info('0/4552810', '0/498AE38');
	-[ RECORD 1 ]---------------
	record_type \| INSERT
	-[ RECORD 2 ]---------------
	record_type \| NEWROOT
	-[ RECORD 3 ]---------------
	record_type \| CREATE_WAL_LOG
	-[ RECORD 4 ]---------------
	record_type \| MULTI_INSERT
	-[ RECORD 5 ]---------------
	record_type \| INPLACE
	-[ RECORD 6 ]---------------
	record_type \| UPDATE
	-[ RECORD 7 ]---------------
	record_type \| FPI
	-[ RECORD 8 ]---------------
	record_type \| LOCK
	-[ RECORD 9 ]---------------
	record_type \| CREATE
	-[ RECORD 10 ]--------------
	record_type \| RUNNING_XACTS
	-[ RECORD 11 ]--------------
	record_type \| COMMIT
	-[ RECORD 12 ]--------------
	record_type \| INSERT+INIT
	-[ RECORD 13 ]--------------
	record_type \| INSERT_LEAF

	postgres=# SELECT * FROM pg_get_wal_stats('0/4552810', '0/498AE38');
	-[ RECORD 1 ]----------------+----------------------
	resource_manager/record_type \| XLOG
	count \| 924
	count_percentage \| 70.10622154779969
	record_size \| 45276
	record_size_percentage \| 67.49552772808586
	fpi_size \| 4216068
	fpi_size_percentage \| 97.18706086725605
	combined_size \| 4261344
	combined_size_percentage \| 96.73493181657214
	-[ RECORD 2 ]----------------+----------------------
	resource_manager/record_type \| Transaction
	count \| 5
	count_percentage \| 0.37936267071320184
	record_size \| 1085
	record_size_percentage \| 1.6174716756112104
	fpi_size \| 0
	fpi_size_percentage \| 0
	combined_size \| 1085
	combined_size_percentage \| 0.02463011693516899
	-[ RECORD 3 ]----------------+----------------------
	resource_manager/record_type \| Storage
	count \| 299
	count_percentage \| 22.685887708649467
	record_size \| 12558
	record_size_percentage \| 18.72093023255814
	fpi_size \| 0
	fpi_size_percentage \| 0
	combined_size \| 12558
	combined_size_percentage \| 0.2850737405270527
	-[ RECORD 4 ]----------------+----------------------
	resource_manager/record_type \| CLOG
	count \| 0
	count_percentage \| 0
	record_size \| 0
	record_size_percentage \| 0
	fpi_size \| 0
	fpi_size_percentage \| 0
	combined_size \| 0
	combined_size_percentage \| 0
	-[ RECORD 5 ]----------------+----------------------
	resource_manager/record_type \| Database
	count \| 1
	count_percentage \| 0.07587253414264036
	record_size \| 34
	record_size_percentage \| 0.05068574836016696
	fpi_size \| 0
	fpi_size_percentage \| 0
	combined_size \| 34
	combined_size_percentage \| 0.0007718193325306412
	-[ RECORD 6 ]----------------+----------------------
	resource_manager/record_type \| Tablespace
	count \| 0
	count_percentage \| 0
	record_size \| 0
	record_size_percentage \| 0
	fpi_size \| 0
	fpi_size_percentage \| 0
	combined_size \| 0
	combined_size_percentage \| 0
	-[ RECORD 7 ]----------------+----------------------
	resource_manager/record_type \| MultiXact
	count \| 0
	count_percentage \| 0
	record_size \| 0
	record_size_percentage \| 0
	fpi_size \| 0
	fpi_size_percentage \| 0
	combined_size \| 0
	combined_size_percentage \| 0
	-[ RECORD 8 ]----------------+----------------------
	resource_manager/record_type \| RelMap
	count \| 1
	count_percentage \| 0.07587253414264036
	record_size \| 553
	record_size_percentage \| 0.8243887895050686
	fpi_size \| 0
	Cancel request sent

	select * from pg_get_wal_stats('0/4552810', '0/498AE38', true) order by count_percentage desc;

	SELECT
	relname,
	CASE
	when relkind = 'r' then 'tab'
	when relkind = 'i' then 'idx'
	when relkind = 'S' then 'seq'
	when relkind = 't' then 'toast'
	when relkind = 'v' then 'view'
	when relkind = 'm' then 'matview'
	when relkind = 'c' then 'composite'
	when relkind = 'f' then 'F tab'
	when relkind = 'p' then 'part tab'
	when relkind = 'I' then 'part idx'
	END as object_type
	FROM
	pg_class
	WHERE
	oid IN ('oid');
	select * from pg_get_wal_stats('0/4552810', '0/498AE38', true) order by count_percentage desc;
	postgres-#
	oid relfilenode relhassubclass relkind reloftype relpersistence reltoastrelid
	relacl relforcerowsecurity relhastriggers relminmxid reloptions relreplident reltuples
	relallvisible relfrozenxid relispartition relname relowner relrewrite reltype
	relam relhasindex relispopulated relnamespace relpages relrowsecurity
	relchecks relhasrules relisshared relnatts relpartbound reltablespace
	postgres-# oid IN ('1663','16394','2619','1247');
	-[ RECORD 1 ]-------------
	relname \| pg_statistic
	object_type \| tab
	-[ RECORD 2 ]-------------
	relname \| pg_type
	object_type \| tab
	postgres-#
	oid relfilenode relhassubclass relkind reloftype relpersistence reltoastrelid
	relacl relforcerowsecurity relhastriggers relminmxid reloptions relreplident reltuples
	relallvisible relfrozenxid relispartition relname relowner relrewrite reltype
	relam relhasindex relispopulated relnamespace relpages relrowsecurity
	relchecks relhasrules relisshared relnatts relpartbound reltablespace
	postgres-# oid IN ('1663','16394','2619','1247');
	-[ RECORD 1 ]-------------
	relname \| pg_statistic
	object_type \| tab
	-[ RECORD 2 ]-------------
	relname \| pg_type
	object_type \| tab