使用 pg_xlogdump + awk统计xlog里面表修改情况

原创罗海雄 2025-02-13

934

有个客户说，半夜的时候突然xlog暴涨，想知道是哪些表导致的。
尝试看了一下 SQL和table的统计信息，没能得到确切的表信息（过程略）。突然想起来，pg_xlogdump可以dump出xlog里面的信息，不如直接分析看看。

基本语法如下:

pg_xlogdump -p <xlog所在目录> <开始xlog文件名> <结束的xlog文件名>

其中"结束的xlog文件名"可以省略, 省略就是到最新文件。

出来的结果是这样的：

REDO @ 0/1000028; LSN 0/10000A8: prev 0/0; xid 0; term 0; len 88; total 122; crc 3574696529; desc: XLOG - checkpoint:
 redo 0/1000028; len 0; next_csn 0; recent_global_xmin 0; tli 1; fpw false; xid 4; oid 10000; multi 2; offset 0; olde
st xid 3 in DB 1; oldest running xid 0; oldest xid with epoch having undo 0; shutdown at Mon Jan  1 00:00:36 2024; re
move_seg 0/0
REDO @ 0/10000A8; LSN 0/10000D0: prev 0/1000028; xid 1; term 1; len 4; total 38; crc 2563411093; desc: XLOG - nextOid
: 18192
REDO @ 0/10000D0; LSN 0/1000170: prev 0/10000A8; xid 0; term 1; len 120; total 154; crc 2994269820; desc: XLOG - chec
kpoint: redo 0/10000D0; len 120; next_csn 4; recent_global_xmin 0; tli 1; fpw false; xid 432; oid 12448; multi 2; off
set 0; oldest xid 3 in DB 1; oldest running xid 0; oldest xid with epoch having undo 0; shutdown at Mon Jan  1 00:00:
40 2024; remove_seg 0/1
REDO @ 0/1000170; LSN 0/10002F8: prev 0/10000D0; xid 860; term 1; len 5; total 391; crc 1006072316; desc: Heap - XLOG
_HEAP_UPDATE update: off 10 new off 2, blkref #0: rel 1663/1/1259/-1/0, forknum:0 storage HEAP DISK blk 15 lastlsn 0/
28, blkref #1: rel 1663/1/1259/-1/0, forknum:0 storage HEAP DISK blk 1 lastlsn 0/28
REDO @ 0/1000408; LSN 0/1000478: prev 0/10003B0; xid 860; term 1; len 11; total 106; crc 1467419347; desc: Heap - XLO
G_HEAP_INSERT insert(init): off 429, blkref #0: rel 1663/1/9025/-1/0, forknum:0 storage HEAP DISK blk 0 lastlsn 0/0
REDO @ 0/1000478; LSN 0/10004E0: prev 0/1000408; xid 860; term 1; len 8; total 104; crc 756686740; desc: Btree - lev
0, blkref #0: rel 1663/1/3227/-1/0, forknum:0 storage HEAP DISK blk 1 lastlsn 0/0, blkref #2: rel 1663/1/3227/-1/0, f
orknum:0 storage HEAP DISK blk 0 lastlsn 0/28

重要信息

对于dba来说，最关心的信息有3个：

1，类型

xlog条目的类型在desc后面
比如

desc: XLOG - checkpoint:
desc: Heap - XLOG_HEAP_UPDATE
desc: Btree - lev0
desc: Heap - XLOG_HEAP_INSERT

2, 长度

代表xlog条目的长度,在len后面

len 11;

3，对象oid

对象oid在rel后面
如:

rel 1663/1/1259/-1/0
rel 1663/1/3227/-1/0

提取并统计

肉眼看显然太费劲，可以通过awk 进行分析和加工。

第一步：识别类型、长度、对象oid所在的位置

其中类型和长度都很固定
类型是第19列和第21列，中间的第20列都为“-”。

...
type=$19" - "$21
...

长度都是13列,但注意，数字后面有个分号(";"),需要去掉

... 
size=$13
gsub(size,";","")
...

对象oid相对比较特殊，可能出现第31列或者第28列，需要区别对待

...
        if($30=="rel"){
            rel=$31;
            rel_cnt[rel]++
            rel_size[rel]+=size;
        }
        if($27=="rel"){
            rel=$28;
            rel_cnt[rel]++
            rel_size[rel]+=size;
        }

...

第二步，分别保存各种类型/对象出现的次数和长度

类型和对象分开存储，次数和长度分开存储
由于不是每个条目都有对象oid出现，因此，需要进行过滤

...
    type_cnt[type]++;
    type_size[type]+=size;
...
    if($0 ~ " rel ") {
        rel_cnt[rel]++;
        rel_size[rel]+=size;
    }
...

第三步，对数据按照长度进行排序

这里采用了 awk里面的 PROCINFO[“sorted_in”] 方式进行排序

...
PROCINFO["sorted_in"]="@val_num_desc"
...

第四步，遍历类型/对象并输出

注意，为了更好看，使用了printf格式化了一下输出的表格
另外，对象的格式是 “对象类型/对象所在数据库oid/对象oid…”, 可以把它们拆开，更好看一些。

        printf("%12s %12s %-50s \n","RedoSize(k)","RedoEntries", "Type")
        for (type in type_size)printf("%12.1f %12.1f %-50s\n", type_size[type]/1024,type_cnt[type],type);

        FS="/"
        printf("\n\n%12s %12s %-10s %-10s\n","RedoSize(k)","RedoEntries", "DBID","RelID")
        for (rel in rel_size){
            $0=rel
            printf("%12.1f %12.1f %-10s %-10s\n",rel_size[rel]/1024,rel_cnt[rel], $2,$3)
        };

完整脚本如下：

脚本后面加个more, 分屏输出

pg_xlogdump -p $PGDATA/pg_xlog 000000010000000000000001 000000010000000000000002 |
awk '/REDO/{
    #类型
    type=$19" - "$21;
    #长度
    size=$13;
    gsub($13,";","");
    type_cnt[type]++;
    type_size[type]+=size;
    #对象oid
    if($0 ~ " rel ") {
        if($30=="rel"){
            rel=$31;
        }
        if($27=="rel"){
            rel=$28;
        }
        rel_cnt[rel]++
        rel_size[rel]+=size;
    }
}
END{
        # 排序，数字(num)、倒序(desc)
        PROCINFO["sorted_in"]="@val_num_desc"
        #输出按类型分类结果
        printf("%12s %12s %-50s \n","RedoSize(k)","RedoEntries", "Type")
        for (type in type_size)printf("%12.1f %12.1f %-50s\n", type_size[type]/1024,type_cnt[type],type);

        # 输出按对象分类结果
        FS="/"
        printf("\n\n%12s %12s %-10s %-10s\n","RedoSize(k)","RedoEntries", "DBID","RelID")
        for (rel in rel_size){
            $0=rel
            printf("%12.1f %12.1f %-10s %-10s\n",rel_size[rel]/1024,rel_cnt[rel], $2,$3)
        };
}' | more

其中awk部分

/REDO/{
    #类型
    type=$19" - "$21;
    #长度
    size=$13;
    gsub($13,";","");
    type_cnt[type]++;
    type_size[type]+=size;
    #对象oid
    if($0 ~ " rel ") {
        if($30=="rel"){
            rel=$31;
        }
        if($27=="rel"){
            rel=$28;
        }
        rel_cnt[rel]++
        rel_size[rel]+=size;
    }
}
END{
        # 排序，数字(num)、倒序(desc)
        PROCINFO["sorted_in"]="@val_num_desc"
        #输出按类型分类结果
        printf("%12s %12s %-50s \n","RedoSize(k)","RedoEntries", "Type")
        for (type in type_size)printf("%12.1f %12.1f %-50s\n", type_size[type]/1024,type_cnt[type],type);

        # 输出按对象分类结果
        FS="/"
        printf("\n\n%12s %12s %-10s %-10s\n","RedoSize(k)","RedoEntries", "DBID","RelID")
        for (rel in rel_size){
            $0=rel
            printf("%12.1f %12.1f %-10s %-10s\n",rel_size[rel]/1024,rel_cnt[rel], $2,$3)
        };
}

可以把awk保存为 xlog_dump.awk, 让每次执行时语句更清爽

pg_xlogdump -p $PGDATA/pg_xlog 000000010000000000000001 000000010000000000000002 | awk  -f xlog_dump.awk | more

输出结果如下


 RedoSize(k)  RedoEntries Type
      1118.7        529.0 Transaction - XLOG_XACT_COMMIT
       145.5      74480.0 Btree - insert
       120.5      37387.0 Heap - XLOG_HEAP_INSERT
        28.4        908.0 Standby - AccessExclusive
        28.1          7.0 RelMap - update
        16.3        695.0 Standby - XLOG_STANDBY_CSN_COMMITTING,
         7.0       1436.0 Heap - XLOG_HEAP_HOT_UPDATE
         6.6       1239.0 Heap - XLOG_HEAP_UPDATE
         5.2        166.0 Transaction - XLOG_XACT_COMMIT_COMPACT
         4.3       1473.0 Heap - delete:
         3.5         30.0 XLOG - checkpoint:
         3.0        382.0 Btree - split
         2.4        155.0 Storage - file
         1.6        136.0 Heap2 - clean:
         0.5        256.0 Heap - inplace:
         0.5         62.0 Btree - lev
         0.2          1.0 Sequence - log:
         0.1         14.0 Heap2 - XLOG_HEAP2_MULTI_INSERT
         0.1         25.0 XLOG - nextOid:
         0.0          2.0 Heap2 - cleanup
         0.0          2.0 Btree - delete:
         0.0       1387.0 Heap - new


 RedoSize(k)  RedoEntries DBID       RelID
        42.4      14068.0 1          2608
        40.1      14789.0 1          2674
        39.0      11844.0 1          1249
        34.0      12138.0 1          2659
        28.0      14079.0 1          2673
        23.3      11819.0 1          2658
        11.7       2541.0 1          1259
         9.9       2527.0 1          9025
         8.8       1891.0 1          2838
         7.4       2411.0 1          2609

数据库部分脚本

根据数据库oid和对象oid,可以登录到数据库中获取具体的对象名称。

根据oid获取数据库名称

select datname from pg_database 
where oid = ...

根据oid获取对象信息

select c.oid,nspname,relname,relkind from pg_class c , pg_namespace n
where c.relnamespace = n.oid
and c.oid in (..., ..., ...);

墨力计划 pg xlog

最后修改时间：2025-02-14 09:34:24

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者