KingbaseES集群运维案例之-- V8R3与V8R6集群wal函数应用

最新推荐文章于 2024-09-05 23:32:04 发布

Kingbase 研究院

最新推荐文章于 2024-09-05 23:32:04 发布

阅读量371

点赞数 5

文章标签：运维 java 数据库服务器 javascript

本文链接：https://blog.csdn.net/lyu1026/article/details/137282562

版权

案例说明：
KingbaseES V8R3和V8R6集群在通过函数获取wal日志的相关信息时，两个版本的函数名称不同，本案例做了函数应用的对比和总结。

适用版本： KingbaseES V8R3/R6

一、KingbaseES V8R3相关函数Tips：
在V8R3的版本，事务日志名称为xlog。

1、查询数据库支持的函数

test=# select proname from sys_proc where proname like '%xlog%';
             proname
----------------------------------
 SYS_CURRENT_XLOG_LOCATION
 SYS_CURRENT_XLOG_INSERT_LOCATION
 SYS_CURRENT_XLOG_FLUSH_LOCATION
 SYS_XLOGFILE_NAME_OFFSET
 SYS_XLOGFILE_NAME
 SYS_XLOG_LOCATION_DIFF
 SYS_LAST_XLOG_RECEIVE_LOCATION
 SYS_LAST_XLOG_REPLAY_LOCATION
 SYS_IS_XLOG_REPLAY_PAUSED
 SYS_XLOG_REPLAY_RESUME
 SYS_SWITCH_XLOG
 SYS_XLOG_REPLAY_PAUSE
(12 rows)

2、xlog切换并查看对应wal日志文件

test=# select sys_switch_xlog();
 sys_switch_xlog
-----------------
 0/E80002A8
(1 row)

test=# select SYS_XLOGFILE_NAME('0/E80002A8');
    SYS_XLOGFILE_NAME
--------------------------
 0000000300000000000000E8
(1 row)

3、获取当前lsn对应的日志文件及offset

# SQL文本
select SYS_CURRENT_XLOG_LOCATION(),SYS_XLOGFILE_NAME(SYS_CURRENT_XLOG_LOCATION()),
     SYS_XLOGFILE_NAME_OFFSET(SYS_CURRENT_XLOG_LOCATION());

test=# select SYS_CURRENT_XLOG_LOCATION(),SYS_XLOGFILE_NAME(SYS_CURRENT_XLOG_LOCATION()),
        SYS_XLOGFILE_NAME_OFFSET(SYS_CURRENT_XLOG_LOCATION());
 SYS_CURRENT_XLOG_LOCATION |    SYS_XLOGFILE_NAME     |    SYS_XLOGFILE_NAME_OFFSET
---------------------------+--------------------------+--------------------------------
 0/E9000220                | 0000000300000000000000E9 | (0000000300000000000000E9,544)
(1 row)

4、获取当前lsn及flush_lsn和insert_lsn位置

# SQL文本
select  SYS_CURRENT_XLOG_FLUSH_LOCATION(),SYS_CURRENT_XLOG_INSERT_LOCATION(),
    SYS_CURRENT_XLOG_LOCATION();

test=# select  SYS_CURRENT_XLOG_FLUSH_LOCATION(),SYS_CURRENT_XLOG_INSERT_LOCATION(),
test-#     SYS_CURRENT_XLOG_LOCATION();
 SYS_CURRENT_XLOG_FLUSH_LOCATION | SYS_CURRENT_XLOG_INSERT_LOCATION | SYS_CURRENT_XLOG_LOCATION
---------------------------------+----------------------------------+---------------------------
 0/E9000300                      | 0/E9000300                       | 0/E9000300
(1 row)

5、对比lsn之间的差异值

test=# select SYS_XLOG_LOCATION_DIFF('0/8BF6C80','0/8BF6C68');
 SYS_XLOG_LOCATION_DIFF
------------------------
                     24
(1 row)

6、获取主备库之间复制延迟

# 主备库之间流复制状态
test=# select * from sys_stat_replication;
  pid  | usesysid | usename | application_name |  client_addr  | client_hostname | client_port |         backend_
start         | backend_xmin |   state   | sent_location | write_location | flush_location | replay_location | sy
nc_priority | sync_state
-------+----------+---------+------------------+---------------+-----------------+-------------+-----------------
--------------+--------------+-----------+---------------+----------------+----------------+-----------------+---
------------+------------
 32377 |       10 | SYSTEM  | node2            | 192.168.1.202 |                 |       35947 | 2023-10-26 15:03
:45.046059+08 |              | streaming | 0/E80001B0    | 0/E80001B0     | 0/E80001B0     | 0/E8000178      |
          2 | sync
(1 row)

# SQL文本
select pid,usename,client_addr,state,
SYS_XLOG_LOCATION_DIFF( SYS_CURRENT_XLOG_LOCATION(),write_location) write_delay,
SYS_XLOG_LOCATION_DIFF( SYS_CURRENT_XLOG_LOCATION(),flush_location) flush_delay,
SYS_XLOG_LOCATION_DIFF( SYS_CURRENT_XLOG_LOCATION(),replay_location) replay_dely
from sys_stat_replication;

test=# select pid,usename,client_addr,state,
test-# SYS_XLOG_LOCATION_DIFF( SYS_CURRENT_XLOG_LOCATION(),write_location) write_delay,
test-# SYS_XLOG_LOCATION_DIFF( SYS_CURRENT_XLOG_LOCATION(),flush_location) flush_delay,
test-# SYS_XLOG_LOCATION_DIFF( SYS_CURRENT_XLOG_LOCATION(),replay_location) replay_dely
test-# from sys_stat_replication;
  pid  | usename |  client_addr  |   state   | write_delay | flush_delay | replay_dely
-------+---------+---------------+-----------+-------------+-------------+-------------
 32377 | SYSTEM  | 192.168.1.202 | streaming |           0 |           0 |           0
(1 row)

7、获取备库lsn信息

test=# select SYS_LAST_XLOG_RECEIVE_LOCATION();
 SYS_LAST_XLOG_RECEIVE_LOCATION
--------------------------------
 0/E90003E0
(1 row)

test=# select SYS_LAST_XLOG_REPLAY_LOCATION();
 SYS_LAST_XLOG_REPLAY_LOCATION
-------------------------------
 0/E90003E0
(1 row)

二、KingbaseES V8R6相关函数

Tips：
在V8R6的版本，事务日志名称为wal。

1、查询数据库支持的函数

test=# select proname from sys_proc where proname like '%wal%';
          proname
----------------------------
 pg_stat_get_wal_senders
 pg_stat_get_wal_receiver
 pg_current_wal_lsn
 pg_current_wal_insert_lsn
 pg_current_wal_flush_lsn
 pg_walfile_name_offset
 pg_walfile_name
 pg_wal_lsn_diff
 pg_last_wal_receive_lsn
 pg_last_wal_replay_lsn
 pg_is_wal_replay_paused
 pg_switch_wal
 pg_wal_replay_pause
 pg_wal_replay_resume
 pg_ls_waldir
 pg_stat_get_wal_buffer
 sys_switch_wal
 sys_current_wal_lsn
 sys_walfile_name_offset
 sys_walfile_name
 sys_current_wal_insert_lsn
 sys_wal_replay_pause
 sys_wal_replay_resume
 sys_is_wal_replay_paused
 sys_stat_get_wal_senders
 sys_wal_lsn_diff
 sys_stat_get_wal_receiver
 sys_current_wal_flush_lsn
 sys_ls_waldir
 sys_stat_get_wal_buffer
 sys_last_wal_receive_lsn
 sys_last_wal_replay_lsn
(32 rows)

2、wal切换并查看对应wal日志文件

test=# select sys_switch_wal();
 sys_switch_wal
----------------
 0/1B0046E8
(1 row)

test=# select pg_walfile_name('0/1B0046E8');
     pg_walfile_name
--------------------------
 00000003000000000000001B
(1 row)

3、获取当前lsn对应的日志文件及offset

test=# select pg_current_wal_lsn(),pg_walfile_name(pg_current_wal_lsn()),pg_walfile_name_offset(pg_current_wal_lsn());
 pg_current_wal_lsn |     pg_walfile_name      |      pg_walfile_name_offset      
--------------------+--------------------------+----------------------------------
 5/C0006590         | 0000001B00000005000000C0 | (0000001B00000005000000C0,26000)
(1 row)

4、查看wal日志文件存储目录及修改时间

test=# select * from pg_ls_waldir() order by modification asc;
                   name                   |   size   |      modification      
------------------------------------------+----------+------------------------
 0000001B00000005000000BD                 | 16777216 | 2021-03-01 12:43:26+08
 0000001B00000005000000BE                 | 16777216 | 2021-03-01 12:45:30+08
 0000001A00000005000000B3.partial         | 16777216 | 2021-03-01 13:17:33+08
 0000001B.history                         |      834 | 2021-03-01 13:17:45+08
........
 0000001B00000005000000BC                 | 16777216 | 2021-03-01 15:56:16+08
 0000001A.history                         |      790 | 2021-03-01 17:25:02+08
 00000018.history                         |      702 | 2021-03-01 18:31:40+08
(24 rows)

4、获取当前lsn及flush_lsn和insert_lsn位置

test=# select pg_current_wal_flush_lsn(),pg_current_wal_insert_lsn(),
                    pg_current_wal_lsn();
 pg_current_wal_flush_lsn | pg_current_wal_insert_lsn | pg_current_wal_lsn 
--------------------------+---------------------------+--------------------
 5/C0006590               | 5/C0006590                | 5/C0006590
(1 row)

5、对比lsn之间的差异值

test=# select pg_wal_lsn_diff('0/8BF6C80','0/8BF6C68');
 pg_wal_lsn_diff 
-----------------
              24
(1 row)

6、查询主备库之间复制延迟

test=# select pid,usename,client_addr,state,write_lag,flush_lag,replay_lag from pg_stat_replication;
  pid  | usename |  client_addr  |   state   | write_lag | flush_lag | replay_lag 
-------+---------+---------------+-----------+-----------+-----------+------------
 12208 | esrep   | 192.168.7.243 | streaming |           |           | 
 12224 | esrep   | 192.168.7.249 | streaming |           |           | 
(2 rows)

test=# select pid,usename,client_addr,state,
test-# pg_wal_lsn_diff(pg_current_wal_lsn(),write_lsn) write_delay,
test-# pg_wal_lsn_diff(pg_current_wal_lsn(),flush_lsn) flush_delay,
test-# pg_wal_lsn_diff(pg_current_wal_lsn(),replay_lsn) replay_dely
test-# from pg_stat_replication;
  pid  | usename |  client_addr  |   state   | write_delay | flush_delay | replay_dely 
-------+---------+---------------+-----------+-------------+-------------+-------------
 12208 | esrep   | 192.168.7.243 | streaming |           0 |           0 |           0
 12224 | esrep   | 192.168.7.249 | streaming |           0 |           0 |           0
(2 rows)

7、查看备库flush_lsn及replay_lsn

prod=# select pg_last_wal_receive_lsn();
 pg_last_wal_receive_lsn 
-------------------------
 5/C0006590
(1 row)

prod=# select pg_last_wal_replay_lsn();
 pg_last_wal_replay_lsn 
------------------------
 5/C0006590
(1 row)

prod=# select pg_last_xact_replay_timestamp();
 pg_last_xact_replay_timestamp 
-------------------------------
 2021-03-01 15:19:28.682064+08
(1 row)

三、总结
由于版本差异，KingbaseES V8R3和R6在wal日志相关查询上，使用的函数名称不同，需要根据版本具体应用。